title: ORACLE: Error on Drop an interim table after dbms_redefinition
body: <p>I'm working on Oracle11g, and trying to redefine a table with <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/tables007.htm#i1006859" rel="nofollow">dbms_redefinition</a>. It works ok but when trying to drop the interim table it throws an <code>ORA-02449: unique/primary keys in table referenced by foreign keys</code> error.</p>
  
  <p>I found a query to look for references right here in SO, </p>
  
  <pre><code>select table_name, constraint_name, status, owner
  from all_constraints
  where r_owner = 'MYSCHEMA'
  and constraint_type = 'R'
  and r_constraint_name in
   (
     select constraint_name from all_constraints
     where constraint_type in ('P', 'U')
     and table_name = 'INTERIM_TABLE'
     and owner = 'MYSCHEMA'
   )
  order by table_name, constraint_name
  </code></pre>
  
  <p>which gives</p>
  
  <pre><code>table_name  |constraint_name           |status   |owner
  ---------------------------------------------------------
  anotherTable|TMP$$_anotherTable_JOB_ID0|DISABLED|MYSCHEMA
  </code></pre>
  
  <p>I suppose that this constraint was created during the redefinition process, that's ok, but I also expected that it must be deleted by the same process. Is this wrong? I say, is part of normal behavior that this constraint were not went deleted? </p>
  
  <p>It's safe to just drop the constraint with </p>
  
  <pre><code>alter table anotherTable
     drop constraint TMP$$_anotherTable_JOB_ID0
  </code></pre>
  
  <p>without a loss of data?</p>
  
  <p>Thanks in advance.</p>
  
  <p>-- EDIT --
  After thinking about this, I've decided just to delete the constraint to let the interim table be droped.</p>
  
  <p>I've modified the query to drop the constraints of other tables that point to the tables which I want to drop, almost automatically.</p>
  
  <pre><code>DECLARE 
    my_table varchar2(100);
    my_constraint varchar2(100);
  BEGIN
  select table_name , constraint_name into my_table,my_constraint
  from all_constraints
  where r_owner = 'MYSCHEMA'
  and constraint_type = 'R'
  and r_constraint_name in
   (
     select constraint_name from all_constraints
     where constraint_type in ('P', 'U')
     and table_name = 'INTERIM_TABLE'
     and owner = 'MYSCHEMA'
   )
  order by table_name, constraint_name;
  execute immediate 'ALTER TABLE '||my_table||' DROP CONSTRAINT '|| my_constraint;
  END;
  /
  DROP TABLE MYSCHEMA.INTERIM_TABLE; 
  </code></pre>
  
  <p>This worked for me, but I must note that in my case that query throws just one row (only one  dependant table), so this must be modified to drop many constraints by a loop or another method if you know someone.</p>
  
  <p>It would be good if someone can figure out and explain why that constraint was not deleted by the process itself (or if this is a normal behavior).</p>