title: Oracle DDL in autonomous transaction
body: <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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:</p>
  
  <p>ORA-00054: resource busy and acquire with NOWAIT specified</p>
  
  <p>I am guessing this is because the main transaction still has DDL lock on the tables.</p>
  
  <p>Am I doing something wrong here, or is there any other way to make this scenario work?</p>