ORA-12919: Can not drop the default permanent tablespace

Issue:

Not able to drop the USERS tablespace

Error Message: 

ORA-12919: Can not drop the default permanent tablespace

Cause: 

USERS tablespace is default permanent tablespace which can not be dropped without assign this default permanent tablespace to another tablespace

Fix:

Change the default permanent tablespace to another tablespace and drop this USERS tablespace

Error Logs and commands output:

1. trying to drop the default permanent tablespace USERS which is erroring out 

[oracle@oraclelab1 datafile]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 7 09:56:31 2025

Version 19.17.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.17.0.0.0

SQL> select NAME from v$tablespace;

NAME

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

SYSAUX

SYSTEM

UNDOTBS1

USERS

TEMP

SQL> drop tablespace users including contents and datafiles;

drop tablespace users including contents and datafiles

*

ERROR at line 1:

ORA-12919: Can not drop the default permanent tablespace

2. Find or How to Find Default Permanent Tablespace? 

SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE

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

USERS

3. Create new tablespace and make it as Default Permanent Tablespace

SQL> create tablespace USERS1;

Tablespace created.

SQL> alter database default tablespace USERS1;

Database altered.

4. Drop USERS tablespace 

SQL> drop tablespace users including contents and datafiles;

Tablespace dropped.

SQL>

SQL> select NAME from v$tablespace;

NAME

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

SYSAUX

SYSTEM

UNDOTBS1

TEMP

USERS1

SQL>