| 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;
TABLESPACE_NAME CONTENTS
------------------------------ ---------
SYSTEM PERMANENT
USERS PERMANENT
TOOLS PERMANENT
TEMP PERMANENT
RBS PERMANENT
COIN PERMANENT
SMALL_OBJECTS 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:
ALTER DATABASE MOUNT;
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:
ALTER DATABASE OPEN;
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