How to recover or re-create temporary tablespace in 10g

In database you may discover that your temporary tablespace is deleted from OS or it might get corrupted. In order to get it back you might think about recover it. The recovery process is simply recover the temporary file from backup and roll the file forward using archived log files if you are in archivelog mode.

Another solution is simply drop the temporary tablespace and then re-create a new one and assign new one as a default tablespace to the database users.
SQL> Select File_Name, File_id, Tablespace_name from DBA_Temp_Files;

FILE_NAME                     FILE_ID TABLESPACE_NAME

----------------------------- ------- ----------------

D:\ORACLE\ORADATA\SADHAN\TEMP02.DBF 1     TEMPMake the affected temporary files offline and create new TEMP tablespace and assign it default temporary tablespace:SQL> Alter database tempfile 1 offline;
SQL> Create temporary tablespace TEMP1 tempfile 'D:\ORACLE\ORADATA\SADHAN\TEMP02.DBF' size 1500M;
SQL> alter database default temporary tablespace TEMP1;Check the users who are not pointed to default temp tablespace and assign them externally then finally drop the old tablespace.

SQL> Select temporary_tablespace, username from dba_users where temporary_tablespace<>'TEMP';

TEMPORARY_TABLESPACE       USERNAME

--------------------       ---------

TEMP                       SH1

TEMP                       SH2

SQL>alter user SH1 temporary tablespace TEMP1;
SQL>alter user SH2 temporary tablespace TEMP1;
SQL>Drop tablespace temp;