TEMP Tablespace Usages and Monitoring

<<Back to DB Administration Main Page

What is TEMP Tablespace?

A temporary tablespace contains transient data that persists only for the duration of a
session. No permanent schema objects can reside in a temporary tablespace. A temp
file stores temporary tablespace data.

Types of Temp Tablespace

Starting from 12.2 Temporary tablespaces can be shared or local.

How to Find if a Tablespace is shared or local

SQL>select TABLESPACE_NAME,SHARED from DBA_TEMP_FILES where upper (TABLESPACE_NAME)=upper('&TABLESPACE_NAME');

What is Default Temporary Tablespaces

Every database user account is assigned a default shared temporary tablespace. If the
database contains local temporary tablespaces, then every user account is also
assigned default local temporary tablespace. A user query can access either shared or local temporary tablespace.
After the database accesses a temporary tablespace for a query, it does not switch to a different one.

How to find the default global temporary tablespace for the database

SQL>select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAME like '%TEMP%';

How to find the temporary tablespaces assigned to an User

SQL> select USERNAME,TEMPORARY_TABLESPACE,LOCAL_TEMP_TABLESPACE from dba_users where upper(username) = upper('&Username');

How to create a Temporary tablespace

By default shared Temporary tablespace is created
SQL>  create temporary tablespace TEMP_TEST  tempfile '+DATA' size 10M;
Tablespace created.

How to Change Default Temporary Tablespace of the database

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_TEST;

How to Increase TEMP tablespace size

SQL> alter tablespace TEMP_TEST add tempfile '+DATA' size 200M;
Tablespace altered.

Changing the default behavior of TABLESPACE

You can change the default behavior of temp tablespace by changing the following hidden parameters.

Parameter Name   Default Value    Description
_force_local_temp  FALSE     for testing only   Forces temporary tablespaces to be LOCAL
_prefer_local_temp  FALSE     Use Local Temp as preferred default tablespace

NOTE: oracle does not recommend to change any hidden parameter until it recommends to do so.

How to drop the tempfile from temp tablespace

Find the Tempfile name to be dropped
SQL>  select TABLESPACE_NAME,FILE_NAME,STATUS from dba_temp_files where TABLESPACE_NAME='TEMP_TEST';
TABLESPACE_NAME                FILE_NAME                                                    STATUS
------------------------------ ------------------------------------------------------------ -------
TEMP_TEST                      +DATA/TST1TP/TEMPFILE/temp_test.600.1004622233               ONLINE
TEMP_TEST                      +DATA/TST1TP/TEMPFILE/temp_test.607.1004616665               ONLINE

drop the tempfile
SQL> alter database tempfile '+DATA/TST1TP/TEMPFILE/temp_test.607.1004616665' drop including datafiles;
Database altered.

How to Drop the temp tablespace

SQL> drop tablespace TEMP_TEST;
or
SQL> drop tablespace TEMP_TEST including contents and datafiles;
Tablespace dropped.

How to Monitor the Utilization of TEMP Tablespace

Check Current Utilization of TEMP Tablespace
SQL>   SELECT TABLESPACE_NAME,
         CON_ID,
         ROUND (SUM (BYTES) / 1048576, 1)                 SIZE_MB,
         ROUND (SUM (MAXBYTES) / 1048576, 1)              MAXSIZE_MB,
         ROUND (SUM (USER_BYTES) / 1048576, 0)            USED_MB,
         ROUND (SUM (USER_BYTES) / SUM (MAXBYTES) * 100, 1) USED_PCT
    FROM CDB_TEMP_FILES
GROUP BY TABLESPACE_NAME, CON_ID
ORDER BY 1;

Check Which User is Consuming Space of TEMP Tablespace

SQL>   SELECT USERNAME,
         SQL_ID,
         SEGTYPE,
         EXTENTS,
         BLOCKS
    FROM V$TEMPSEG_USAGE
ORDER BY USERNAME

Top 5 TEMP TABLESPACE Consumer Over Laster 7 Days 

SQL>   SELECT T.SAMPLE_TIME,
         T.SQL_ID,
         T.TEMP_MB,
         T.TEMP_DIFF,
         S.SQL_TEXT
    FROM (  SELECT
                   TRUNC (SAMPLE_TIME) SAMPLE_TIME,
                   SQL_ID,
                   SUM (TEMP_MB) TEMP_MB,
                   SUM (TEMP_DIFF) TEMP_DIFF,
                   ROW_NUMBER ()
                   OVER (PARTITION BY TRUNC (SAMPLE_TIME)
                         ORDER BY SUM (TEMP_MB) DESC NULLS LAST)
                       AS RN
              FROM (SELECT SAMPLE_TIME,
                           SESSION_ID,
                           SESSION_SERIAL#,
                           SQL_ID,
                           TEMP_SPACE_ALLOCATED / 1024 / 1024 TEMP_MB,
                             TEMP_SPACE_ALLOCATED / 1024 / 1024
                           - LAG (TEMP_SPACE_ALLOCATED / 1024 / 1024, 1, 0)
                                 OVER (ORDER BY SAMPLE_TIME)
                               AS TEMP_DIFF
                      FROM DBA_HIST_ACTIVE_SESS_HISTORY
                     WHERE 1 = 1
                                )
          GROUP BY
                  TRUNC (SAMPLE_TIME), SQL_ID) T
         LEFT JOIN V$SQLAREA S ON S.SQL_ID = T.SQL_ID
   WHERE 1 = 1 AND RN <= 1 AND SAMPLE_TIME >= TRUNC (SYSDATE) - 7
ORDER BY SAMPLE_TIME DESC, TEMP_MB DESC

How to Shrink TEMP tablespace

SQL> alter tablespace LOCAL_TEMP_TEST shrink space keep 100m;

Tablespace altered.

How to Shrink Tempfile

SQL> ALTER TABLESPACE LOCAL_TEMP_TEST SHRINK TEMPFILE '+DATA/TST1TP/822B1C132B1804A6E0530601A8C00CF1/TEMPFILE/local_temp_test.603.1004621635' KEEP 30M;

Tablespace altered.