List only Oracle Temp Table Space
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
.
You can the dba_temp_files, that has tablespace_name column
select distinct (TABLESPACE_NAME) from dba_temp_files;