DROP TEMPORARY TABLESPACE

SQL>
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u02/oradata/HLRMAIN/TEMP2.DBF' SIZE 100M
AUTOEXTEND ON NEXT 1M
MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M ;


Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.


SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

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

Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.


SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

SQL> SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name = 'TEMP';







The normal size of my temporary tablespace is 512MB which has always been more than adequate for this environment.
A bad query, however, increased the size of this tablespace (my tempfile) to over 13GB and I would like to reclaim that space.
In the example below, I simply drop and recreate the tempfile:

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' DROP INCLUDING DATAFILES;

Database altered.


SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 512m
AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

Tablespace altered