Recreation of 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 ‘/u01/oradb/tempbkp.dbf’ size 10m;
4. alter database default temporary tablespace tempbkp;
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 ‘/u01/oradb/temp05.dbf’ drop including datafiles;
7. alter tablespace temp add tempfile ‘/u01/oradb/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

  • June 27, 2016 | 13 views
  • Comments