How to recover or recreate temporary tablespace in 10g

How to recover or recreate temporary tablespace in 10g

In database you may discover that your temporary tablespace is deleted from OS or it might got 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 create a new one and assign new one to the database users.

In order to do that follow the steps here.
1)Find out the temporary datafiles.

SQL> col file_name format a50
SQL> set linesize 200
SQL> select file_name,file_id, tablespace_name from dba_temp_files;
FILE_NAME                                             FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ------------------------------
/oradata2/temp2.dbf                                         1 TEMP2
/oradata2/temp.dbf                                          2 TEMP
/oradata2/temp3.dbf                                         4 TEMP3

2)Make the affected temporary files offline.
SQL> Alter database tempfile 1,2,4 offline;
Database altered.

3)Create a new temporary tablespace and make it database default tablespace.
SQL> create temporary tablespace temp01 tempfile '/oradata2/temp.dbf' size 10M;
Tablespace created.

SQL> alter database default temporary tablespace temp01;
Database altered.

3)Check for users who are not pointed to this default temporary tablespaces. Make this default for those users also.

SQL> select temporary_tablespace , username from dba_users where temporary_tablespace<>'TEMP01';

TEMPORARY_TABLESPACE           USERNAME
------------------------------ ------------------------------
TEMP                           TEST2
TEMP2                          ARJU

4)Explicitly assign temporary tablespace for users TEST2 and ARJU.
SQL> alter user arju temporary tablespace temp01;
User altered.

SQL> alter user test2 temporary tablespace temp01;
User altered.

3)Drop the old temporary tablespace.

SQL> drop tablespace temp;
Tablespace dropped.

SQL> drop tablespace temp2;
Tablespace dropped.

SQL> drop tablespace temp3;
Tablespace dropped.