ORA-02273 Attempting to Drop Index Tablespace (Doc ID 1080398.6)

Last updated on NOVEMBER 22, 2023

Applies to:

Symptoms

Problem Description: 
==================== 

For some reason you need to identify what your foreign key constraints are and 
on which tables they reference.

Typically you are trying to drop a primary key but cannot as there is a foreign
key constraint referencing the constraint. You typically would get the 
following error:

 ORA-02273: dropping a primary key constraint
     Cause: A unique or primary key referenced by foreign keys cannot be 
            dropped.
    Action: Remove all references to the key before dropping it.
 

The following query will generate a view that lists out all the foreign key 
constraints in the database.


CREATE OR REPLACE VIEW ALL_FK_CONSTRAINTS
AS
SELECT con.name   constraint_name
      ,bt.owner#  parent_table_owner
      ,db.username ownername
 ,bt.name    parent_table
      ,bc.name    parent_column
      ,tab.owner# child_table_owner
      ,tab.name   child_table
      ,col.name   child_column
 ,decode(i2.refact,1,'YES','NO')
       cascade_delete
  FROM sys.con$ con
      ,sys.obj$ tab
      ,sys.dba_users db
      ,sys.col$ col
      ,sys.ccol$ i
      ,sys.obj$ bt
      ,sys.col$ bc
      ,sys.cdef$ i2
 WHERE con.con# = i.con#
 AND tab.obj# = i.obj#
   AND col.obj# = i.obj#
   AND db.user_id = tab.owner#
   AND col.col# = i.col#
   AND i2.con#  = i.con#
   AND bt.obj#  = i2.robj#
   AND bc.obj#  = i2.robj#
   AND bc.col#  = i2.cols;
/

Changes

Cause