Recover or Recreate TEMPORARY Tablespace in Oracle

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> select file_name,file_id, tablespace_name from dba_temp_files;

FILE_NAME                              FILE_ID     TABLESPACE_NAME
--------------------------------------------- ------------ ------------------------------
/oradata2/temp2.dbf                          1    TEMP01
/oradata2/temp.dbf                            2        TEMP02
/oradata2/temp3.dbf                          4        TEMP03

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 temp tempfile '/opt/nit/temp.dbf' size 10M;
Tablespace created.

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

4)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<>'TEMP';

TEMPORARY_TABLESPACE           USERNAME
--------------------------------------------- -------------------------
TEMP                                                     US1
TEMP2                                                   US2

5)Explicitly assign temporary tablespace for users US1 and US2.
SQL> alter user us1 temporary tablespace temp;
User altered.

SQL> alter user us2 temporary tablespace temp;
User altered.
6)Drop the old temporary tablespace.

SQL> drop tablespace temp01;
Tablespace dropped.

SQL> drop tablespace temp02;
Tablespace dropped.

SQL> drop tablespace temp03;
Tablespace dropped.