List only Oracle Temp Table Space

database oracle oracle11g oracle10g tablespace
Malatesh picture Malatesh · Aug 3, 2015 · Viewed 10.1k times · Source

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

Answer

Mureinik picture Mureinik · Aug 3, 2015

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.

Related questions

How to use DBMS_CRYPTO.encrypt function in oracle

I want to encrypt password column in the database and I am trying to use encrypt function present inside DBMS_CRYPTO package (have given execute access from sys account to the current user) but I am getting the following error. …

Read More
Oracle SQL query for Date format

I always get confused with date format in ORACLE SQL query and spend minutes together to google, Can someone explain me the simplest way to tackle when we have different format of date in database table ? for instance i have …

Read More
Oracle - How to create a materialized view with FAST REFRESH and JOINS

So I'm pretty sure Oracle supports this, so I have no idea what I'm doing wrong. This code works: CREATE MATERIALIZED VIEW MV_Test NOLOGGING CACHE BUILD IMMEDIATE REFRESH FAST ON COMMIT AS SELECT V.* FROM TPM_PROJECTVERSION V; If …

Read More