Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 3 July 2013

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;
----------------------------- ------- ----------------
Make 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';
--------------------       ---------
TEMP                       SH1
TEMP                       SH2

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


Post a Comment