[Oracle 12c] Find Default and TemporaryTablespaces and size of Database Schemas

This is an example on how to find the default and temporary table space details of a particular schema. This comes in handy when debugging tablespace exceeded errors like ORA-01536 and ORA-00059. This types of errors are often common in data warehouses full base load scenarios.

To find the Default Tablespace and Temporary Tablespace of a particular database schema, you need to have an user with DBA/SYS privileges. After you have logged in with this particular user, run the following SQL query,

SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME IN('Schema Name Here')

Temporary Tablespace Size

Next to find the size of the temporary tablespace, To find the size of the temporary tablespace we need to query the system table DBA_TEMP_FILES, Find a sample query below

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TABLESPACE_SIZE_MB
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME IN ('Schema Name Here')
GROUP BY TABLESPACE_NAME

Temporary Tablespace Size.

Default Tablespace Size

To find Default Tablespace size you need to query the system table DBA_DATA_FILES. See a sample query syntax below, 

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 TABLESPACE_SIZE_MB
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME IN ('EXAMPLE')
GROUP BY TABLESPACE_NAME

Default Tablespace Size.