Oracle DDL in autonomous transaction

Question

question

suoloordi asked Mar 1, '10

Oracle DDL in autonomous transaction

I need to execute a bunch of (up to ~1000000) sql statements on an Oracle database. These statements should result in a referentially consistent state at the end, and all the statements should be rolled back if an error occurs. These statements do not come in a referential order. So if foreign key constraints are enabled, one of the statements may cause a foreign key violation even though, this violation would be fixed with a statement that would be executed later on.

I tried disabling foreign keys first and enabling them after all statements were executed. I thought I would be able to roll back when there was an actual foreign key violation. I was wrong though, I found out that every DDL statement in Oracle started with a commit, so there was no way to rollback the statements this way. Here is my script for disabling foreign keys:

begin 
  for i in (select constraint_name, table_name from user_constraints
            where constraint_type ='R' and status = 'ENABLED') 
    LOOP execute immediate 'alter table '||i.table_name||' disable constraint 
                           '||i.constraint_name||''; 
  end loop;
end;

After some research, I found out that it was recommended to execute DDL statements, like in this case, in an autonomous transaction. So I tried to run DDL statements in an autonomous transaction. This resulted in the following error:

ORA-00054: resource busy and acquire with NOWAIT specified

I am guessing this is because the main transaction still has DDL lock on the tables.

Am I doing something wrong here, or is there any other way to make this scenario work?

oracle transaction ddl constraint

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-01T01:41:16Z

KillerDBA answered Mar 1, '10

I'm looking forward to any suggestions on how to get an all-or-none on DDL updates... Most likely, you're going to have to be prepared to flashback your database (if you're on a recent enough version of Oracle). All DDL is automatically commited, so each DDL change you do should result in an irrevocable commit. This is one of the reasons I like SQL Server - you can control transactions that involved DDL on your objects, just like transactions to data in tables.

And I'm not sure if your immediate issue is related to the lock conflict you sugges, but I think you're right. Here's a workaround for that... use a temporary table to store the constraint and table names and then loop off a cursor in that table.

Update:

It appears that the only DDL wish to use is the management of the FK relationships and you want to do this so that INSERT statements won't fail. Is there some reason you can't rearrange your INSERTs so that the FKs can be left in place? I've done database transforms and ordinarily we just make sure we create the parent records first; we leave the FK definitions alone.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-03-01T02:45:13Z

suoloordi answered Mar 1, '10

I had asked the same question on stackoverflow.com, and here is my accepted answer on there:

There's several potential approaches.

The first thing to consider is that whatever you do at the table level will apply to all sessions using that table. If you haven't got exclusive access to that table, you probably don't want to drop/recreate constraints, or disable/enable them.

The second thing to consider is that you probably don't want to be in a position of rolling back a million inserts/updates. Rolling back can be SLOW.

Generally I would load into a temporary table. Then do a single INSERT from the temporary table into the destination table. As a single statement, Oracle will apply all the check constraints at the end.

If you can't go through a temporary table (eg updates to existing data), before starting make the constraints deferrable initially immediate. Then, within your session,

SET CONSTRAINTS emp_job_nn, emp_salary_min DEFERRED;

You can then apply the changes and, when you commit, the constraints will be validated.

You should aquaint yourself with DML error logging as it can help identify any rows causing violations.

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KillerDBA commented · Mar 01, 2010 at 04:23 PM

Do you want to be able to atomically do a large number of any kind of DDL or just a number of FK disables/enables with data inserts?

0 ·

Answer 3 · 2010-03-02T13:02:13Z

Leigh Riffel answered Mar 2, '10

As the answer on StackOverflow said, disabling the constraints disables them for every user. This could cause problems for updates, but could even effect select statements as the optimizer no longer has as much information about the data. You should defer your constraints or use the temporary table as suggested.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KillerDBA commented · Mar 13, 2010 at 01:17 AM

I disagree. On SQL Server, modifying the FK DDL as part of the transaction should prevent other users accessing the object, preventing attempted updates when the FK protections are missing. It's very straightforward.

0 ·

Leigh Riffel commented · Mar 15, 2010 at 10:30 AM

I'll edit my answer to remove the transactional DDL debate best left for a different venue.

0 ·

question