Query Data dictionary regarding Temporary tablespaces in Oracle 19c

To query the data dictionary for information about temporary tablespaces in Oracle 19c, you can use the following views:

1. DBA_TABLESPACES
   This view provides general information about all tablespaces, including temporary tablespaces.
   • Query:

     SELECT TABLESPACE_NAME, STATUS, CONTENTS, BIGFILE
     FROM DBA_TABLESPACES
     WHERE CONTENTS = 'TEMPORARY';
             

   • Key Columns:
     • TABLESPACE_NAME: Name of the temporary tablespace.
     • STATUS: Status of the tablespace (e.g., ONLINE, OFFLINE).
     • CONTENTS: Identifies the tablespace type (e.g., TEMPORARY).
     • BIGFILE: Indicates whether it is a Bigfile tablespace.
2. DBA_TEMP_FILES
   This view shows detailed information about the tempfiles in temporary tablespaces.
   • Query:

     SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024 AS SIZE_MB, AUTOEXTENSIBLE, MAXBYTES/1024/1024 AS MAX_SIZE_MB
     FROM DBA_TEMP_FILES;
             

   • Key Columns:
     • TABLESPACE_NAME: Name of the temporary tablespace.
     • FILE_NAME: Physical file name.
     • BYTES: Current size of the tempfile.
     • AUTOEXTENSIBLE: Indicates if the file can auto-extend.
     • MAXBYTES: Maximum size the tempfile can grow.
3. V$TEMP_SPACE_HEADER
   This view provides information about space usage in temporary tablespaces.
   • Query:

     SELECT TABLESPACE_NAME, FILE_NAME, BYTES_FREE/1024/1024 AS FREE_MB, BYTES_USED/1024/1024 AS USED_MB
     FROM V$TEMP_SPACE_HEADER;
             

   • Key Columns:
     • TABLESPACE_NAME: Name of the temporary tablespace.
     • FILE_NAME: Physical file name.
     • BYTES_FREE: Amount of free space.
     • BYTES_USED: Amount of used space.
4. V$SORT_USAGE
   This view shows details about sessions using temporary space.
   • Query:
     

     SELECT SESSION_NUM, SQL_ID, TABLESPACE_NAME, SEGMENT_NAME, SEGMENT_TYPE, BYTES/1024/1024 AS USED_MB
     FROM V$SORT_USAGE;
     

   • Key Columns:
     • SESSION_NUM: Session using temporary space.
     • SQL_ID: SQL query using temporary space.
     • TABLESPACE_NAME: Temporary tablespace in use.
     • BYTES: Amount of temporary space being used.
5. DBA_USERS
   This view maps users to their default temporary tablespaces.
   • Query:

     SELECT USERNAME, TEMPORARY_TABLESPACE
     FROM DBA_USERS;
             

Key Columns:

• USERNAME: Database username.
• TEMPORARY_TABLESPACE: The temporary tablespace assigned to the user.

Additional Notes:

• Use DBA_HIST views for historical data (e.g., DBA_HIST_TEMP_USAGE for historical temporary tablespace usage).
• Temporary tablespaces are crucial for sorting, hashing, and other temporary operations, so regular monitoring is essential for performance.

These queries provide comprehensive details about temporary tablespaces in Oracle 19c.

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.

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.

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