Managing Temp Tablespace

Here you will see how to manage temporary tablespace.

Temporary tablespace is used for Global temporary tables and sorting operations with the query that uses group by, order by, distinct etc.. which needed intermediate storage to produce the results. Global temporary tables  will release the storage either on commit or on disconnect, depending on how it was created.

In any case Temp tablespace will not shrink by itself, we have to do manually cleanup the temp tablespace.

Below are step by steps to cleanup Temperary tablespace:

1) Using below queries, find default temporary tablespace and size

   SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

   PROPERTY_NAME                        PROPERTY_VALUE     DESCRIPTION
   -------------------------------           ---------------               ----------------------------------------
   DEFAULT_TEMP_TABLESPACE   TEMP002                     Name of default temporary tablespace

   SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name like 'TEMP%';

   TABLESPACE_NAME       FILE_NAME                                               BYTES
   ------------------------        -----------------                                           ---------
   TEMP002                           /Oracle/oradata/Prod/TEMP0002.DBF          10737418240

   SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

   TABLESPACE_NAME                BYTES_USED     BYTES_FREE
   ------------------------------        ----------------      ----------
   TEMP002                                    10737418240          0

2) Create new temporary tablespace and make it as a default 

   SQL> CREATE TEMPORARY TABLESPACE TEMP01 TEMPFILE '/Oracle/oradata/Prod/TEMP01.DBF' SIZE 100M autoextend on;

   Tablespace created.

   SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;

   Database altered.

3) Crosscheck whether default temp tablespace changed or not

   SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

   PROPERTY_NAME                         PROPERTY_VALUE     DESCRIPTION
   --------------------------                    ---------------               --------------------------------------
   DEFAULT_TEMP_TABLESPACE     TEMP01                         Name of default temporary tablespace

4) Now drop OLD temp tablespace.

   SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name like 'TEMP%';

   TABLESPACE_NAME    FILE_NAME                                                  BYTES
   ------------------------     -----------                                                       ------------
   TEMP002                         /Oracle/oradata/Prod/TEMP0002.DBF           10737418240         
   TEMP01                          /Oracle/oradata/Prod/TEMP01.DBF                104857600

   SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

   TABLESPACE_NAME                BYTES_USED        BYTES_FREE
   ------------------------------           ----------                 ----------
   TEMP01                                       27262976               77594624
   TEMP002                                     10737418240          0

   SQL> DROP TABLESPACE TEMP002  INCLUDING CONTENTS AND DATAFILES;

Thats it.

Hope this will help you... :)

Best Regards,