How to Manage Temporary Tablespace

Step-01 ( Check Default tablespace)
-------------------------------------

SQL> select * from database_properties where property_name like 'DEFAULT%TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                 Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                Name of default permanent tablespace

SQL>


Step-02 ( set default temp tablespace)
--------------------------------------
SQL> alter database default temporary tablespace NILLTEMP;


Step-03 (Check free space of temp tablespace)
----------------------------------------------
Monitoring Temporary Tablespaces and Sorting:

Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.

One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE

DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:

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

TABLESPACE_NAME                BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP1                           859963392  188612608

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

TABLESPACE_NAME                BYTES_USED/1024/1024 BYTES_FREE/1024/1024
------------------------------ -------------------- --------------------
TEMP1                                       820.125              179.875


Step-04 (find temp file path)
--------------------------------------
SQL> select * from dba_temp_files where tablespace_name='TEMP3';
or
SQL> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;


Step-05 ( alter temp tablespace)
--------------------------------------
SQL> ALTER TABLESPACE TEMP3 ADD TEMPFILE '/u01/oracle/oradata/PROD/temp03.dbf' size 1024m;