Tags

Step for recreating Oracle temp Tablespace.

 Step 1 Create Temporary Tablespace Temp

 CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE
  '/u01/app/oradata/temp2_01.dbf' SIZE 10240M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M; 

Step 2 Move Default Database temp tablespace

 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2

[optional step check session that is still using tempfile, to avoid hanging session during drop tempfile tablespace]

Step 3 Drop old temp tablespace

 DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Step 4 Recreate Tablespace Temp

 CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/temp/temp01.dbf' SIZE 10240M AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Step 5 Move Tablespace Temp, back to newly created temp tablespace

 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Step 6 Drop temporary for tablespace temp

 DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

usually shutdown is not necessary however if there is persistent users that still hold on temp tablespaces then restart database is mandatory.

this step is also applicable to change undo tablespace in Oracle. but of course with slight modification.