List only Oracle Temp Table Space

Question

Is there way to list only temp tablespaces in Oracle? I found following query which is listing all the tablespaces, I just need only temp tablespaces.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

Solution 1

You can filter the list by the contents column:

SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY'

As described in the Oracle Database Online Documentation for dba_tablespaces.

Solution 2

You can the dba_temp_files, that has tablespace_name column

select distinct (TABLESPACE_NAME) from dba_temp_files;