If the tablespace is a default temporary tablespace when you are greeted with the following exception:

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

Steps to clear the temporary tablespace:

The first step you need to perform is creating another temporary tablespace (let’s call it TEMP2). The next step would be to remove the temporary tablespace you want to resize from being the default temporary tablespace (in our example, this will be a tablespace named TEMP) by making TEMP2 the default. Drop / recreate the TEMP tablespace to the size you want. Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace.

1) SQL>CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u02/oracle/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

2) SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

3) SQL>DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

4) SQL>CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oracle/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

5) SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.

6) SQL>DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

posted by Jaswinder Singh @ 1:36 AM

2 Comments:

At Wednesday, November 05, 2008, Unknown said…

Will Autoextend on can cause same problems of temp tablespace exhausted in near future?
At Tuesday, December 22, 2009, ceylon tee said…

Create a temporary tablespace.
Syntax:
CREATE TEMPORARY TABLESPACE tablespace_name
TEMPFILE Tempfile_Options
[EXTENT MANAGEMENT LOCAL]
[UNIFORM [SIZE int K | M] ];
Tempfile_Options:
'filespec' [AUTOEXTEND OFF]
'filespec' [AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]]