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