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';
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