TEMP tablespace droping and recreating

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

SQL> select * from database_properties
2 where property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME
------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
TEMP

Because this is the default temporary tablespace Oracle won't let you drop it. Here are the steps needed to drop your default temporary tablespace.

1) Create a New Temporary Tablespace
First, you will need to create a new temporary tablespace...in this example I've called the tablespace "TEMPX". Note that I purposefully made the tablespace small in size (10mb). This is because we will drop this tablespace at the end of this process.

SQL> CREATE TEMPORARY TABLESPACE TEMPX
2 TEMPFILE '/data/tempx.dbf' SIZE 10M AUTOEXTEND OFF
3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
4 SEGMENT SPACE MANAGEMENT MANUAL
/

Tablespace created.


2) Make Your New Temporary Tablespace the Default Temporary Tablespace
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempx;

Database altered.


3) Drop Your Original Temporary Tablespace
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.


4) Recreate Your 'TEMP' Tablespace
SQL> CREATE TEMPORARY TABLESPACE TEMP
2 TEMPFILE '/data/temp.dbf' SIZE 1000M AUTOEXTEND OFF
3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
4 SEGMENT SPACE MANAGEMENT MANUAL
/
or
"SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/d23/oraptch/ptchdata/temp01.dbf' SIZE 50M
2 AUTOEXTEND ON NEXT 50M MAXSIZE 2048M,
3 '/d23/oravis/visdata/temp02.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 2048M,
4 '/d23/oravis/visdata/temp03.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 2048M,
5 '/d23/oravis/visdata/temp04.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 2048M
6 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M BLOCKSIZE 8K;


5) Make Your 'TEMP' Tablespace the Default Temporary Tablespace Again
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.


6) Drop Your 'TEMPX' Tablespace Now That It Is Not Needed
SQL> DROP TABLESPACE tempx INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.