Oracle DDL in autonomous transaction

Question

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?

Answer 1

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.