How to recreate TEMP Tablespace in Oracle

Problem:

You need to recreate a temporary tablespace in Oracle 9i/10g/11g/12c.

Solution:

--Create Temporary Tablespace Temp2 CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE   '/oradata/testdb/temp02.dbf' SIZE 500M; --Move Default Database temp tablespace ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2; --Drop temp tablespace DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES; --Recreate Tablespace Temp CREATE TEMPORARY TABLESPACE TEMP TEMPFILE   '/oradata/testdb/temp01.dbf' SIZE 500M; --Move Tablespace Temp, back to new temp tablespace ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp; --Drop temporary for tablespace temp DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.