This simple script fixed my problem:
alter session set nls_language='american'; set lines 200 col table_name format a30 col constraint_name format a30 col r_constraint_name format a30 col status format a20 SELECT TABLE_NAME, CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE OWNER = '&&owner' AND CONSTRAINT_TYPE='R' AND R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='&&interim_table'); prompt copy and paste output above as input to owner and table_name: prompt ALTER TABLE &&owner..&&table_name DROP CONSTRAINT &constraint_name; DROP TABLE &&owner..&&interim_table PURGE; exit
Result:
Enter value for owner: SCOTT old 3: WHERE OWNER = '&&owner' new 3: WHERE OWNER = 'SCOTT' Enter value for interim_table: ORDERS_INTERIM old 5: AND R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='&&interim_table') new 5: AND R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='ORDERS_INTERIM') TABLE_NAME CONSTRAINT_NAME ------------------------------ ------------------------------ ORDERROWS TMP$$_FK__ORD__ENT0 copy and paste output above as input to owner and table_name: Enter value for table_name: ORDERROWS Enter value for constraint_name: TMP$$_FK__ORD__ENT0 old 1: ALTER TABLE &&owner..&table_name DROP CONSTRAINT &constraint_name new 1: ALTER TABLE SCOTT.ORDERROWS DROP CONSTRAINT TMP$$_FK__ORD__ENT0 Table altered. old 1: DROP TABLE &&owner..ORDERS_INTERIM PURGE new 1: DROP TABLE SCOTT.ORDERS_INTERIM PURGE Table dropped.