Search for default Oracle tablespaces.

Preamble

To find the default permanent tablespace in Oracle, you can run the following SELECT operator.

Default persistent tablespace

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

This Oracle query will return the default permanent tabular space value.

Default time 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 Oracle query will return the time table space value by default.

Default table spaces (permanent and temporary)

To find both the default permanent table space and the default temporary table space 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 example will query Oracle system tables and return both permanent and temporary table spaces by default.

Query to find Tablespaces and Data files in Oracle 11g

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.