My Oracle Support Banner

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

Last updated on NOVEMBER 22, 2023

Applies to:

Oracle Database - Enterprise Edition - Version and later
Oracle Database - Personal Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.


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

SELECT   constraint_name
      ,bt.owner#  parent_table_owner
      ,db.username ownername
 ,    parent_table
      ,    parent_column
      ,tab.owner# child_table_owner
      ,   child_table
      ,   child_column
  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;





To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.