Dropping your default oracle temporary tablespace can
be tricky. You cannot simply issue a drop tablespace command because oracle
does not allow it. This article will cover the steps needed to drop and recreate
your default temporary tablespace.
If you've tried dropping your default temporary tablespace by just issuing a
DROP TABLESPACE command you've probably noticed that Oracle won't let you.
SQL> DROP TABLESPACE temp;
DROP TABLESPACE temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
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
/
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.
And that is all that is needed to recreate your default
temporary tablespace. It would be nice if Oracle supplied a command to do this
for us, but if you want to go really crazy I'm sure you could script up your own
procedure to perform the same functionality.
~ Troy Campano ~
|