Alter Database Default Temporary Tablespace Examples

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

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

1. Check Current Default Temporary 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_TEMP_TABLESPACE';

PROPERTY_VALUE
----------------------------------------
TEMP

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

2. 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 = 'TEMP1' and contents = 'TEMPORARY';

STATUS
---------
ONLINE

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

3. Alter Database to Set Default Temporary Tablespace

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

SQL> alter database default temporary tablespace temp1;

Database altered.

4. Check Current Default Temporary Tablespace Again

We should make sure the result.

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

PROPERTY_VALUE
----------------------------------------
TEMP1

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