August 21, 2013

ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) - dependencies exist

ISSUE:
ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) - dependencies exist

Restore TABLE backup

Schema Status
-------------
select username from dba_users where username=upper('&username');

Table Status
------------
select count(*) from SCHEMANAME.TABLENAME;

TRUNCATE TABLE
---------------------------
spool TARGET_TABLENAME_TRUNCATE.log
set echo on term on feed on timing on

truncate table SCHEMANAME.TABLENAME;
truncate table SCHEMANAME.TABLENAME
                         *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints where TABLE_NAME='&TABLE_NAME';

CONSTRAINT_NAME                C TABLE_NAME                     STATUS
------------------------------ - ------------------------------ -------
SYS_CONSTRAINT1                C TABLENAME                      ENABLED
PK_TABLENAME                   P TABLENAME                      ENABLED

alter table SCHEMANAME.TABLENAME DISABLE constraint SYS_CONSTRAINT1;

alter table SCHEMANAME.TABLENAME DISABLE constraint PK_TABLENAME;
ERROR at line 1:
ORA-02297: cannot disable constraint (SCHEMANAME.PK_TABLENAME) -
dependencies exist

SOLUTION:

alter table SCHEMANAME.TABLENAME DISABLE constraint PK_TABLENAME cascade;

Table altered.

select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,status from dba_constraints where TABLE_NAME='TABLE_NAME';


No comments:

Post a Comment