How to drop and recreate TEMP Tablespace in Oracle ?

1. Create Temporary Tablespace Temp2.

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  '/u01/dbdata/oradata/temp101.dbf' SIZE 2000M, '/u01/dbdata/oradata/temp102.dbf' SIZE 2000M';

2. Move Default temp tablespace to newly created one.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;


3. Drop temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

4. Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/dbdata/temp/temp01.dbf' SIZE 2000M;

5 Move Tablespace Temp, back to new temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

6. Drop temporary for tablespace temp

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;


Recap
====

1)Create Temporary Tablespace Temp2
2)Move Default temp tablespace to newly created one
3)Drop temp tablespace TEMP
4)Recreate Tablespace Temp
5) Make the default tablepsace to Temp
6)Drop temporary tablespace temp2