ORA-12906, DROPPING DEFAULT TEMPORARY TABLESPACE

First find the Default temporary tablespace in your database

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

SQL>

SQL > select  property_value  from  database_properties  Where  property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_VALUE

--------------------------------------------------------------------------------

TEMP

Check the size of TEMP tablespace:

SQL > SELECT tablespace_name, file_name, bytes/1024/1024/1024 GB FROM dba_temp_files WHERE tablespace_name like 'TEMP%';

TABLESPACE_NAME         FILE_NAME                                                   GB

--------------------------     --------------------------------                        ------------

  TEMP                        /opt/oracle/oradata/temp01.dbf                        6

For Oracle 8 and above, the following query will return all users and their SIDs which are doing a sort:

 SELECT   b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#, a.username, a.osuser, a.status  FROM     v$session a,v$sort_usage b  WHERE    a.saddr = b.session_addr  ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;

To see USED and FREE space of it:

SQL> select SUM(bytes_used)/1024/1024/1024 GBused, SUM(bytes_free)/1024/1024/1024 GBfree from  v$temp_space_header;

  GBUSED     GBFREE

------------    ---------

   1.25                 4.75

Now, try to drop the Default temp tablespace

SQL> DROP TABLESPACE temp;

DROP TABLESPACE temp

*

ERROR at line 1:

ORA-12906: cannot drop default temporary tablespace

In order to drop and recreate the default temp tablepsace first thing is to create another temporary tablespace and make that tablespace as default tablespace for the database and then drop the old default tablespace(TEMP)

SQL>   CREATE TEMPORARY TABLESPACE temp_new TEMPFILE  '/opt/oracle/oradata/temp_new01.dbf' size 5G reuse  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new; 

Database altered.

Now drop the TEMP tablespace

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.