When attempting to drop the interim table after a successful online redefinition, you may get:
ORA-02449: unique/primary keys in table referenced by foreign keys
This is easy to overlook - the online redef procedure does not disable the foreign key relationship from other tables to your (now) obsolete interim table.
To find these tables and their constraints:
SELECT TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS FROM DBA_CONSTRAINTS WHERE OWNER = 'SH' AND CONSTRAINT_TYPE='R' AND R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='SALES_INTERIM')
| TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE | R_CONSTRAINT_NAME | STATUS |
|---|---|---|---|---|
| INVOICES | TMP$$_INVOICES_FK0 | R | TMP$$_SALES_PK0 | DISABLED |
| REFUNDS | TMP$$_REFUNDS_FK0 | R | TMP$$_SALES_PK0 | DISABLED |
| REPLENISHMENT | TMP$$_REPLENISHMENT_FK0 | R | TMP$$_SALES_PK0 | DISABLED |
As can be seen from the table above, there are certainly constraints from other tables, pointing to the primary key on the interim table. They are disabled, but nevertheless preventing us from dropping the table.
Let's make sure that each of these "funny named" constraints have a sibling constraint, one that is named correctly after the redefintion:
SELECT TABLE_NAME,CONSTRAINT_NAME,R_CONSTRAINT_NAME,STATUS
FROM DBA_CONSTRAINTS
WHERE TABLE_NAME in (
SELECT TABLE_NAME
FROM DBA_CONSTRAINTS
WHERE OWNER = 'Sh'
AND CONSTRAINT_TYPE='R'
AND R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='SALES_INTERIM')
)
AND CONSTRAINT_TYPE='R'
AND CONSTRAINT_NAME LIKE '%REPLENISHMENT%' OR CONSTRAINT_NAME LIKE '%INVOICES%' OR CONSTRAINT_NAME LIKE '%REFUNDS%'
ORDER BY TABLE_NAME,CONSTRAINT_NAME;
Result:
| TABLE_NAME | CONSTRAINT_NAME | R_CONSTRAINT_NAME | STATUS |
|---|---|---|---|
| INVOICES | INVOICES_FK | SALES_PK | ENABLED |
| INVOICES | TMP$$_INVOICES_FK0 | TMP$$_SALES_PK0 | DISABLED |
| REFUNDS | REFUNDS_FK | SALES_PK | ENABLED |
| REFUNDS | TMP$$_REFUNDS_FK0 | TMP$$_SALES_PK0 | DISABLED |
| REPLENISHMENT | REPLENISHMENT_FK | SALES_PK | ENABLED |
| REPLENISHMENT | TMP$$_REPLENISHMENT_FK0 | TMP$$_SALES_PK0 | DISABLED |
As you can see, the constraints come in pairs and the ones with names starting with TMP$$ are leftovers from the redefinition.
You can safely drop these constraints. Put the following in a .sql script and run it as sysdba:
alter session set nls_language='american';
set lines 200
set pages 0
set heading off
set feedback off
set trimspool on
set verify off
set echo off
spool 7.alter_table.sql
select 'spool 7.alter_table.log' from dual;
SELECT 'alter table '
|| B.OWNER
|| '.'
|| B.TABLE_NAME
|| ' drop constraint '
|| B.CONSTRAINT_NAME
|| ';'
FROM DBA_CONSTRAINTS A
FULL OUTER JOIN DBA_CONSTRAINTS B
ON A.CONSTRAINT_NAME = B.R_CONSTRAINT_NAME
WHERE A.OWNER = 'SH'
AND A.TABLE_NAME='SALES_INTERIM'
AND B.R_CONSTRAINT_NAME IS NOT NULL;
select 'exit' from dual;
exit
Run the script, and you will have a new script containing the drop-clauses:
alter table SH.INVOICES drop constraint TMP$$_INVOICES_FK0; alter table SH.REFUNDS drop constraint TMP$$_REFUNDS_FK0; alter table SH.REPLENISHMENT drop constraint TMP$$_REPLENISHMENT_FK0;
After these statements have been successfully executed, you can go ahead and drop the interim table.
