Temp Tablespace recreate to resize / Shrink

In 11g, there is a new SQL command that can be used to shrink temporary tablespaces. This command can be used to shrink only locally managed temporary tablespaces :

ALTER TABLESPACE tablespace SHRINK SPACE | TEMPFILE tempfile_name [KEEP size];

The SHRINK SPACE clause allows a user to shrink a temporary tablespace, whereas SHRINK TEMPFILE allows the shrink of a temporary file. The optional KEEP clause defines the lower bound that a tablespace can be shrunk to. It is the opposite for MAXSIZE for auto extensible tablespace. If it is not specified, the system will try to shrink as much as possible, as long as the other storage attibutes are satisfied. Otherwise, shrink will stop once the tablespace/tempfile already reaches the size specified through the KEEP option.  

Below steps are OK for any Oracle Versions Release: 8.1.5 to 11.2


1. Create another temp tablespace with moderate size on temp tablespace datafile location

CREATE  TEMPORARY TABLESPACE "TEMP2"

TEMPFILE '/mylocation/temp/temp02.dbf' SIZE 3072M REUSE AUTOEXTEND ON NEXT  100M MAXSIZE  32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

2. Make the new TEMP tablespace as default for database.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Drop old big default tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

4. Re create that big TBS with new size

CREATE

TEMPORARY TABLESPACE "TEMP" TEMPFILE '/mylocation/temp/temp01.dbf' SIZE 3072M REUSE AUTOEXTEND ON NEXT  100M MAXSIZE  32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

5.  Make the resized TEMP TBS as default for Database.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

6. Drop second temp tablespace.

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

While trying to drop first temp tbs, if users are still using that tablespace you won't be able to drop it. 


Following is the work around:

1. Find out the all the sessions that are not active and have an entry in V$sort_usage.
You can do it by

SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

2. Kill those session Using
alter system kill session 'SID_NUMBER, SERIAL#NUMBER'; kill those session that are not being used actually. where SID_NUMBER and SERIAL#NUMBER is found in step 


3. Drop old tablespace