Simple script for finding a leftover FK constraint to an interim table after a redefinisjon

After a redefinition of a table, you may be stuck with some FK constraints from other tables, to the old interim tables.

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.