Temporary tablespace :Drop and Recreate

SQL> drop tablespace temp;


drop tablespace temp

*
ERROR at line 1:

ORA-12906: cannot drop default temporary tablespace



– Create a new temp tablespace and assign it as default

SQL> CREATE TEMPORARY TABLESPACE TEMP_NEW

TEMPFILE ‘/u01/oradata/TESTDB/temp_new_01.dbf’ SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 5000M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K

SEGMENT SPACE MANAGEMENT MANUAL;
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;



– Drop old temp tablespace and recreate it and then assign it as default

SQL> drop tablespace temp including contents and datafiles;
SQL> CREATE TEMPORARY TABLESPACE TEMP

TEMPFILE ‘/u03/oradata/TESTDB/temp_01.dbf’ SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 5000M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K

SEGMENT SPACE MANAGEMENT MANUAL;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

– Drop TEMP_NEW tablespace

SQL> drop tablespace TEMP_NEW including contents and datafiles;