ORACLE: Error on Drop an interim table after dbms_redefinition

Question

I'm working on Oracle11g, and trying to redefine a table with dbms_redefinition. It works ok but when trying to drop the interim table it throws an ORA-02449: unique/primary keys in table referenced by foreign keys error.

I found a query to look for references right here in SO,

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

which gives

table_name  |constraint_name           |status   |owner
---------------------------------------------------------
anotherTable|TMP$$_anotherTable_JOB_ID0|DISABLED|MYSCHEMA

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?

It's safe to just drop the constraint with

alter table anotherTable
   drop constraint TMP$$_anotherTable_JOB_ID0

without a loss of data?

Thanks in advance.

-- EDIT -- After thinking about this, I've decided just to delete the constraint to let the interim table be droped.

I've modified the query to drop the constraints of other tables that point to the tables which I want to drop, almost automatically.

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;

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.

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).

score 7 · Accepted Answer · answered Oct 17 '11 at 19:42

7

It's easy enough to force this:

drop table INTERIM_TABLE cascade constraints;

answered Oct 17 '11 at 19:42

APC

144,005
19
170
281

This will delete rows in those tables who points to my temporary interim table, no? is not what I'm looking for, because those rows' foreign keys are now also pointing to my redefined table. – Keber Oct 17 '11 at 20:01
1

No. The `CASCADE` means it will drop any constraints referencing the interim_table – Oct 17 '11 at 21:01

score 2 · Answer 2 · edited Nov 14 '13 at 22:19

It's because the original and new constraints STATUS changes while running the SYNC and FINISH redefinition procedure as below. You create the fkeys to the interim table from the child tables in a disabled mode. When we finish the redef, we disable the old fkeys and enable the news (without having to validate). Consider:

create table t NOLOGGING
as
select * from all_objects;


alter table t add constraint t_pk primary key(object_id);

create table t1( x references t );
create table t2( y references t );


insert into t1 select object_id from t where rownum <= 100;

100 rows created.


insert into t2 select object_id from t where rownum <= 100;
100 rows created.



create table t_interim similar to t table.

alter table t1 add constraint t1_new_fk foreign key(x) references t_interim disable;

alter table t2 add constraint t2_new_fk foreign key(y) references t_interim disable;

select constraint_name, status from user_constraints where constraint_type = 'R';

CONSTRAINT_NAME                STATUS
------------------------------ --------
SYS_C004733                    ENABLED     <<<== original constraint
T1_NEW_FK                      DISABLED
SYS_C004734                    ENABLED
T2_NEW_FK                      DISABLED


begin
dbms_redefinition.sync_interim_table( user, 'T', 'T_INTERIM' );
end;
/

PL/SQL procedure successfully completed.

begin
dbms_redefinition.finish_redef_table( user, 'T', 'T_INTERIM' );
end;
/

PL/SQL procedure successfully completed.


select constraint_name, status from user_constraints where constraint_type = 'R';

CONSTRAINT_NAME                STATUS
------------------------------ --------
SYS_C004733                    DISABLED       <<< flip flopped the status
T1_NEW_FK                      ENABLED
SYS_C004734                    DISABLED
T2_NEW_FK                      ENABLED

drop table t_interim cascade constraints;

select constraint_name, status from user_constraints where 
constraint_type = 'R';

CONSTRAINT_NAME                STATUS
------------------------------ --------
T1_NEW_FK                      ENABLED
T2_NEW_FK                      ENABLED

ORACLE: Error on Drop an interim table after dbms_redefinition

2 Answers2