Using Set Default Tablespaces (both Permanent and Temp) in Oracle

This article is written about how to set default permanent and temporary tablespaces in Oracle with syntax and examples.

How to SET Default Permanent Tablespace

First, make certain that you have created a everlasting tablespace.

Next you will want to exchange the Oracle database to use your everlasting tablespace as the default everlasting tablespace.

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

ALTER DATABASE DEFAULT TABLESPACE tbs_perm_01;

This will replace 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 gadget tables and return the cost of the default everlasting tablespace.

How to Set Default Temporary Tablespace

First, make positive that you have created a transient tablespace.

Next you will want to change the Oracle database to use your transient tablespace as the default transient tablespace.

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

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tbs_temp_01;

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

You can run the following question 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 question the Oracle gadget tables and return the price of the default temporary tablespace.