ORA-02449: unique/primary keys in table referenced by foreign keys During Drop Tablespace

I got “ORA-02449: unique/primary keys in table referenced by foreign keys During Drop Tablespace ” error in Oracle database.

 

ORA-02449: unique/primary keys in table referenced by foreign keys During Drop Tablespace

Details of error are as follows.

SQL> DROP TABLESPACE TBS_USERS INCLUDING CONTENTS AND DATAFILES
DROP TABLESPACE TBS_USERS INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

SQL>

 

 

 

The tablespace still contain dependencies objects (constraints , indexes ) in the different tablespaces.

 

To check the dependencies objectsi run the following query.

SQL>select owner, constraint_name,table_name,index_owner,index_name
       from dba_constraints
       where (index_owner,index_name) in (select owner,index_name from dba_indexes
       where tablespace_name='YOUR_TABLESPACE_NAME');

 

Then run the following command.

 SQl>ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;

And then you can drop the tablespace as follows.

SQL>Drop tablespace <tablespace_name> including contents and datafiles cascade constraints;

 

Please note that if this Tablespace contain empty table it will not be viewed in dba_segments because of the new feature deffered segment creation and you can you can user alter table move to move the table to a new TS

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

See more