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

This article is written about how to discover default permanent and transient 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 locate the default transient 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 gadget tables and return the value of the default transient tablespace.

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

To find each the default everlasting 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 question the Oracle system tables and return the both the default permanent and default transient tablespaces.