How to Drop and Recreate Temp Tablespace

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

Ohhhh.. I tried to drop the temp tablespace but it failed… But Why ??

You always have a temp tablespace associated with the objects for sorting. Temporary tablespace is used for storing the temporary data. So to resize the temp tablespace (using the drop and recreate method), we will have to perform a few more steps.

1. We need to create another temp tablespace (let’s call it Temp2). Then we need to make this tablespace a default tablespace.

SQL> CREATE TEMPORARY TABLESPACE temp2
  2  TEMPFILE '+DATA' SIZE 11M REUSE
  3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

2. Drop the temporary tablespace you want to resize from being the default temporary tablespace (in our example, this will be a tablespace named TEMP). After dropping you can recreate the temp file and make it as default again.

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE temp
  TEMPFILE '+DATA' SIZE 11m 
 AUTOEXTEND ON  MAXSIZE 32g;


Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

3. Finally, make the newly created TEMP tablespace you default temporary tablespace for the database and drop the TEMP2 tablespace.

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.