To Change Temp Table Space Location

1) Find the temp tablespace location

        SELECT * FROM dba_temp_files;

2) Find the Default Temporary Tablespace

SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

3) Find the Temp Tablespace size

         SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
         FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
        WHERE    A.tablespace_name = D.name
        GROUP by A.tablespace_name, D.mb_total;

4) Create New Temp Tablespace

create temporary tablespace temp1 tempfile '/data/cepm_data_files/TEMP01.TEMP' SIZE 1G;

5) Change Default location to temp1

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

6) Drop Old Temporary Tablespace

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;