Temporary Tablespace Issue

Recreation of Temporary Tablespace

Solution:

Drop and Re Create the Temporary Tablespace

Steps to implement

1. alter database backup controlfile to trace;
2. select file_name,tablespace_name from dba_temp_files;
3. create temporary tablespace tempbkp tempfile '/y01/orcl/dat/tempbkp.dbf' size 10m;
4. alter database default temporary tablespace tempbkp;Database altered.
5. select temporary_tablespace from dba_users;
6. drop tablespace temp including contents and datafiles;
If above statement doesn't work. Delete each datafile of the particular temporary tablespace as follows
alter database tempfile '/temp/orcle/dat/temp05.dbf' drop including datafiles;
7. alter tablespace temp add tempfile'/temp/orcl/dat/temp01.dbf' SIZE 118489088 reuse autoextend on 1048576 maxsize 8500m;
8. alter database default temporary tablespace temp;
9. drop tablespace tempbkp including contenets and datafiles