ORA-02297: cannot disable constraint -dependencies exist

Problem Description
Whenever you try to disable a constraint of a table it fails with error message ORA-02297: cannot disable constraint -dependencies exist as below.

SQL> alter table transaction disable constraint TRANSACTION_PK;
alter table transaction disable constraint TRANSACTION_PK
*
ERROR at line 1:
ORA-02297: cannot disable constraint (OMS.TRANSACTION_PK) - dependencies exist

Cause of the Problem
Disable constraint command fails as the table is parent table and it has foreign key that are dependent on this constraint.

Solution of the Problem
Two solutions exist for this problem.
1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint.

Following query will check dependent table and the dependent constraint name. After that disable child first and then parent constraint.

SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table",
       p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
       FROM user_constraints p
       JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
       WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
       AND c.constraint_type = 'R'
       AND p.table_name = UPPER('&table_name');
Enter value for table_name: transaction
old   7:      AND p.table_name = UPPER('&table_name')
new   7:      AND p.table_name = UPPER('transaction')

Parent Table                   Child Table                    Parent Constraint              Child Constraint
------------------------------ ------------------------------ ------------------------------ ------------------------------
TRANSACTION                    USER_SALARY_RECORD             TRANSACTION_PK                 SYS_C005564
TRANSACTION                    TRANSACTION_DETAIL             TRANSACTION_PK                 TRNSCTN_DTL_TRNSCTN_FK

SQL> alter table USER_SALARY_RECORD disable constraint SYS_C005564;
Table altered.

SQL> alter table TRANSACTION_DETAIL  disable constraint TRNSCTN_DTL_TRNSCTN_FK;
Table altered.

SQL> alter table transaction disable constraint TRANSACTION_PK;
Table altered.

2)Disable the constraint with cascade option.
SQL> alter table transaction disable constraint TRANSACTION_PK cascade;
Table altered.

 

High Executions Of Statement "delete from smon_scn_time..."

FOR:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.4 - Release: 10.1 to 10.2

Information in this document applies to any platform.

ISSUE:

A delete from smon_scn_time is performing excessive gets and executions as viewed from AWR report:

Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s)

205,501,888 30,508 6,736.00 54.05 9,733.97 61,180.96 delete from smon_scn_time where...

The AWR report shows the following SQL with an excessive amount of executions:

delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where

thread=0);


CAUSE:

There are inconsistencies between the indexes and table smon_scn_time.

The delete statement deletes the oldest rows from smon_scn_time to clear space for new rows. SMON wakes

up every 5 minutes and checks how many on-disk mappings we have--the max is 144000.

The new mappings are then added for the last period (since SMON last updated), and if this is over 144000,

SMON will then issue the delete statement:

delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where

thread=0)

There will be an execution of this each time SMON wakes to update smon_scn_time, and if one deletion does

not free enough mappings, then there will be multiple executions.

What happens is due to the inconsistency between the table and indexes the delete returns zero rows; so the

delete statement is executed continuously to reduce the smon_scn_time below the maximum 14400 mappings.

When table smon_scn_time is analyzed we see the inconsistency:

SQL> analyze table smon_scn_time validate structure cascade;

analyze table smon_scn_time validate structure cascade

*

ERROR at line 1 :

ORA-01499: table/Index Cross Reference Failure - see trace file

SOLUTION:

To implement the solution, please execute the following steps:

1. Ensure you have a usable backup in case of failures

2. Drop and recreate the indexes on table smon_scn_time

connect / as sysdba

drop index smon_scn_time_scn_idx;

drop index smon_scn_time_tim_idx;

create unique index smon_scn_time_scn_idx on smon_scn_time(scn);

create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp);

analyze table smon_scn_time validate structure cascade;

ERROR at line 1: ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], []

To Resolve This Error:
Solution 1: Try to rebuild Indexes and 
ANALYZE TABLE Table_Name VALIDATE STRUCTURE;

Solution 2:  Still You are getting the same Error...
Try this..   
BEGIN
            DBMS_REPAIR.SKIP_CORRUPT_BLOCKS
                 ( SCHEMA_NAME => 'SCOTT', OBJECT_NAME => 'DEPT',
                   OBJECT_TYPE => dbms_repair.table_object, 
                    FLAGS => dbms_repair.skip_flag);
 END;
/

Check this Query:
              SELECT OWNER, TABLE_NAME, SKIP_CORRUPT FROM DBA_TABLES WHERE OWNER = 'SCOTT'; 

OWNER	TABLE_NAME	SKIP_COR
SCOTT	ACCOUNT	DISABLED
SCOTT	BONUS	DISABLED
SCOTT	DEPT	ENABLED

 Create a Backup table same as this table.

• Then take the backup into backup table and drop the original.
• Rename backup to original.
• If the table is very big use Bulk collect to take backup.
• Run statistics for New Table.