Clear the temporary tablespace

If the tablespace is a default temporary tablespace when you are greeted with the following exception:

SQL> DROP TABLESPACE temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

Steps to clear the temporary tablespace:

1) SQL>CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/database/oracle/oradata/KOUSHIK/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

2) SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

3) SQL>DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

4) SQL>CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/database/oracle/oradata/KOUSHIK/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

5) SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

6) SQL>DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped