ORA-12906 cannot drop default temporary tablespace

In this post I have shown how to solve the error ORA-12906: cannot drop default temporary tablespace.

Let's have a look about temporary tablespace assigned to users ARJU, PROD and SCOTT.

SQL> select username, temporary_tablespace from dba_users where username in ('ARJU','SCOTT','PROD');

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
ARJU                           TEMP
PROD                           TEMP
SCOTT                          TEMP

So These users are assigned to TEMP temporary tablespace.
Now have a look at database default tablespace.
SQL> select PROPERTY_VALUE from database_properties
where property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE
--------------------------------------------------------------------------------
TEMP

Whenever you try to drop database default tablespace it fails with error ORA-12906.
SQL> drop tablespace temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

In order to solve this problem you must assign database default tenporary tablespace to a new one.
To do this create a new tempoary tablespace temp2 and then assign it to database default tablespace.

To create a new one,
SQL> create temporary tablespace temp2 tempfile '/oradata2/temp2.dbf' size 10M;
Tablespace created.

To make this new one to database default temporary tablespace,
SQL> alter database default temporary tablespace temp2;
Database altered.

After chaging database default temporary tablespace you will notice all user's temporary tablespace are also changed.

SQL> select username, temporary_tablespace from dba_users where username in ('ARJU','SCOTT','PROD');

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
ARJU                           TEMP2
PROD                           TEMP2
SCOTT                          TEMP2

You can easily drop by,
SQL> drop tablespace temp;
Tablespace dropped.

Related Documents
Free space in Temporary Tablespace
The operation that require sort area or Temporary Tablespace
Information about Temporary Segments.