Oracle / PLSQL: Set Default Tablespaces (both Permanent and Temp)

This Oracle tutorial explains how to set default permanent and temporary tablespaces in Oracle with syntax and examples.

How to SET Default Permanent Tablespace

First, make sure that you have created a permanent tablespace.

Next you will need to change the Oracle database to use your permanent tablespace as the default permanent tablespace.

To set the default permanent tablespace in Oracle, you can run the following ALTER DATABASE statement:

ALTER DATABASE DEFAULT TABLESPACE tbs_perm_01;

This will update the default permanent tablespace to use the tbs_perm_01 tablespace.

You can run the following query to see that the default permanent tablespace has, in fact, been changed:

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

This will query the Oracle system tables and return the value of the default permanent tablespace.

How to Set Default Temporary Tablespace

First, make sure that you have created a temporary tablespace.

Next you will need to change the Oracle database to use your temporary tablespace as the default temporary tablespace.

To set the default temporary tablespace in Oracle, you can run the following ALTER DATABASE statement:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbs_temp_01;

This will update the default temporary tablespace to use the tbs_temp_01 tablespace.

You can run the following query to see that the default temporary tablespace has, in fact, been changed:

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

This will query the Oracle system tables and return the value of the default temporary tablespace.