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

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

How to Find Out Default Permanent Tablespace

To find the default permanent tablespace in Oracle, you can run the following SELECT statement:

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 Find Out Default Temporary Tablespace

To find the default temporary tablespace in Oracle, you can run the following query:

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.

How to Find Out Both Default Tablespaces (Permanent and Temporary)

To find both the default permanent tablespace as well as the default temporary tablespace in Oracle, you can run the following command:

SELECT PROPERTY_NAME, PROPERTY_VALUE
FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME IN ('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');

This will query the Oracle system tables and return the both the default permanent and default temporary tablespaces.