Tyo Sites and Notes

Below is step for recreating Oracle temp Tablespace.

 == Step 1 Create Temporary Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
  ‘/u01/app/oradata/temp01′ SIZE 10240M AUTOEXTEND OFF,
  ‘/u01/app/oradata/temp02′ SIZE 10240M AUTOEXTEND OFF,
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

== Step 2 Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

== Step 3 Drop temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

== Step 4 Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE
  ‘/u01/app/temp/temp01′ SIZE 10240M AUTOEXTEND OFF,

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

== Step 5 Move Tablespace Temp, back to new temp tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
== Step 6 Drop temporary for tablespace temp
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

 No need to do shutdown when drop temp tablespace and the recreate it. If something happen with temp tablespaces e.g. : crash, corrupt, etc. Oracle database will ignore the error, but DML (insert,update,delete) and SELECT Query will suffer.