Managing Tablespaces   «Prev  »Next
Lesson 10 Temporary tablespaces and the data dictionary
Objective Query the data dictionary for information about temporary tablespaces.

Temporary tablespaces | Data dictionary

I am working as an Oracle DBA using Oracle 12c. How do I query the data dictionary for information about temporary tablespaces.
You can find out if you have any temporary tablespaces in a database by querying the dba_tablespaces data dictionary view. The contents column of that view will tell you whether or not a tablespace is temporary:

SQL> select tablespace_name, contents

  2  from dba_tablespaces;


------------------------------ ---------

SYSTEM                         PERMANENT

USERS                          PERMANENT

TOOLS                          PERMANENT

TEMP                           PERMANENT

RBS                            PERMANENT

COIN                           PERMANENT


COIN_SORT                      TEMPORARY

In this example, the lone temporary tablespace is the one named COIN_SORT. Note that the TEMP tablespace is a permanent database just like all the others.

Mounting a Database to an Instance

When you need to perform specific administrative operations, the database must be started and mounted to an instance, but closed. You can achieve this scenario by starting the instance and mounting the database. To mount a database to a previously started, but not opened instance, use the SQL statement ALTER DATABASE with the MOUNT clause as follows:


Opening a Closed Database

You can make a mounted but closed database available for general use by opening the database. To open a mounted database, use the ALTER DATABASE SQL statement with the OPEN clause:

After executing this statement, any valid Oracle Database user with the CREATE SESSION system privilege can connect to the database.

Temp TableSpaces Data Dictionary - Quiz

Take the quiz to test your knowledge of the tablespace options.
Temp TableSpaces Data Dictionary - Quiz
In the next lesson, you will learn how to take tablespaces and data files offline