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

Issue:

Not able to drop the USERS tablespace

Error Message: 

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

Cause: 

USERS tablespace has some schemas and objects inside the schemas which has unique and primary keys in table referenced by foreign keys

Fix:

Drop those users or objects before dropping the tablespace

Error Logs and commands output:

1. Verify the tablespace and while trying to drop USERS tablespace which is not allowing us due to "ORA-02449: unique/primary keys in table referenced by foreign keys"

SQL> select NAME from v$tablespace;

NAME

------------------------------

SYSAUX

SYSTEM

UNDOTBS1

USERS

USERS1

TEMP

SQL> drop tablespace users including contents and datafiles;

drop tablespace users including contents and datafiles

*

ERROR at line 1:

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

2. Verify the users and default tablespaces 

SQL> set pages 1000 lines 1000

SQL> col USERNAME for a20

SQL> select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE from dba_users where DEFAULT_TABLESPACE='USERS';

USERNAME             ACCOUNT_STATUS                   DEFAULT_TABLESPACE

-------------------- -------------------------------- ------------------------------

HR                   OPEN                             USERS

1 rows selected.

SQL>

3. Drop the HR user including the cascade constraints, after dropping the user including cascade constraints all the unique and primary keys in table referenced by foreign keys will be removed 

SQL> drop user hr cascade;

User dropped.

4. Drop the USERS tablespaces

SQL> drop tablespace users including contents and datafiles;

Tablespace dropped.

SQL>

SQL> select NAME from v$tablespace;

NAME

------------------------------

SYSAUX

SYSTEM

UNDOTBS1

TEMP

USERS1

SQL>