Alter Database Default Tablespace Examples

To globally change the default permanent tablespace of a database, we take the following steps:

1. Check Current Default Permanent Tablespace
2. Make Sure the Target Tablespace Does Exist
3. Alter Database to Set Default Tablespace
4. Check Current Default Permanent Tablespace Again

Check Current Default Permanent Tablespace

To check what default tablespace currently is in the database, we may query the database properties.

SQL> select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE
----------------------------------------
USERS

Currently, the default tablespace is USERS which is the most commonly used tablespace name.

Make Sure the Target Tablespace Does Exist

We should make sure the target tablespace is existing and online.

SQL> select status from dba_tablespaces where tablespace_name = 'ERPTBS' and contents = 'PERMANENT';

STATUS
---------
ONLINE

Otherwise, you may see ORA-00959 if the tablespace does not exist.

Alter Database to Set Default Tablespace

To change the default permanent tablespace globally in the database, we should perform ALTER DATABASE.

SQL> alter database default tablespace erptbs;

Database altered.

Check Current Default Permanent Tablespace Again

We should make sure the result.

SQL> select property_value from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE
----------------------------------------
ERPTBS

We have changed the default permanent tablespace for the database. To change the default tablespace of a user, it can be done at user-level.