Oracle 11G Shrinking Temp Tablespace

There are a few methods to shrink the temporary tablespace in Oracle 11G. In some cases you can directly resize or shrink the tablespace. If that is not possible then create a new temporary tablespace, reassign the default temp space and drop the old tablespace.


SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name like 'TEMP%';


alter database tempfile '/the/full/path/to/temp01.dbf' resize 256M;
alter database tempfile '/the/full/path/to/temp01.dbf' resize 256M

-- Can give -
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

-- If that fails, on 11G do -
alter tablespace TEMP shrink space keep 256M;

-- If that fails do

CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/datapath/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

CREATE TEMPORARY TABLESPACE temp
2  TEMPFILE '/datapath/temp01.dbf' SIZE 256M REUSE
3  AUTOEXTEND ON NEXT 128M MAXSIZE unlimited
4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

From http://stackoverflow.com/questions/1824572/how-to-shrink-temp-tablespace-in-oracle