How To Drop and Recreate Temporary Tablespace In Oracle?

Oracle will not allow to drop the default temporary tablespace from a database. We have to assign a new default temp tablespace for the database to drop the current  tablespace.
Suppose we need to drop and recreate the default temporary tablespace TEMP.
Step 1: Create a new temporary tablespace temp1
SQL> CREATE TEMPORARY TABLESPACE temp1
2 TEMPFILE ‘/u01/oradata/TESTDB/temp1_01.dbf’ SIZE 100M REUSE
3 AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Tablespace created.

Step 2: Switch the default temporary tablespace to temp1
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;
Database altered.

Step 3: Drop the temporary tablespace TEMP
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
Step 4 : Recreate the temporary tablespace TEMP
SQL> CREATE TEMPORARY TABLESPACE temp
2 TEMPFILE ‘/u02/oradata/TESTDB/temp_01.dbf’ SIZE 500M REUSE
3 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Tablespace created.

Step 5: Switch the default temporary tablespace to TEMP
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
Database altered.

Step 6: Drop the temporary tablespace TEMP1
SQL> DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

Article by : Anju