*******************************************************************************
* Description: Procedure of Recreating Oracle Database Default Temp Tablespace
* Compatiablity: RDBMS 10g, 11g, 12c
* Date: 12:06 PM EST, 01/17/2017
*******************************************************************************


<1> Find temp tablespace names:
    |
    |__ SQL> select distinct(tablespace_name) from dba_temp_files;


<2> Creating new temp tablespace:
    |
    |__ SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE 'D:\oradata\PREPROD\TEMP01.DBF' SIZE 2G, 'D:\oradata\PREPROD\TEMP02.DBF' SIZE 7G; 
	

<3> Reset default temp tablespace:
    |
    |__ SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
	

<4> Drop old tablespace - Before dropping, ensure no session is performing any sort operation:
    |
    |__ SQL> SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;
    |   SQL> SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;
    |   SQL> ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;
    |
    |
    |__ SQL> DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;
	
	
<5> Reference - http://dbafix.blogspot.com/2010/08/how-to-drop-and-recreate-temp.html