ORA-12919: Can not drop the default permanent tablespace

Hi, Today I am getting the “ORA-12919: Can not drop the default permanent tablespace” error. Actually, I tried to drop users tablespace which is the default tablespace for the database user and I'm getting the above error.

ORA-12919 Can not drop the default permanent tablespace

SQL> drop tablespace datatbs;
drop tablespace datatbs
*
ERROR at line 1:

ORA-12919: Can not drop the default permanent tablespace

Cause - We tried to drop default tablespace.

Action - Make another tablespace as the default tablespace for the database and drop.

You should firstly change the default tablespace of database to new tablespace as follows.

SQL> alter database default tablespace data;

Database altered.

Now tried to drop tablespace.

SQL> drop tablespace datatbs;

Tablespace dropped.

Read - How To Export Tablespaces Using Expdp Data Pump Utility?

How to change default tablespace for a user

Use the below command to check default tablespace for a user.

select username,default_tablespace from dba_users where username = 'SHRIPAL';

USERNAME DEFAULT_TABLESPACE
-------- ------------------
SHRIPAL  USERS

In the above query the shripal user has the tablespace USERS as default.

Change default tablespace

SQL> alter user SHRIPAL default tablespace SHRIPAL_TBS;

User altered.

Check the user details.

select username,default_tablespace from dba_users where username = 'SHRIPAL';

USERNAME DEFAULT_TABLESPACE
-------- ------------------
SHRIPAL  SHRIPAL_TBS

Now your default tablespace has been changed.