Shrinking temporary tablespace in Oracle 10g and 11g

Useful links

• Reclaiming Unused Space in Datafiles
• Temporary Tablespace Enhancements in Oracle Database 11g Release 1

Shrinking temporary tablespace in Oracle 10g

CREATE TEMPORARY TABLESPACE TEMP02 TEMPFILE 
  '/oracle/oradata/db/TEMP_02.dbf' SIZE 50M AUTOEXTEND ON;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;

Switch all existing users from old TEMP to new TEMP02 tablespace:

set serveroutput on
-- Switch all existing users to new temp tablespace.
BEGIN
  FOR cur_user IN (SELECT username FROM dba_users WHERE temporary_tablespace = 'TEMP') LOOP
    dbms_output.put_line('ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE TEMP02');    
    EXECUTE IMMEDIATE 'ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE TEMP02';
  END LOOP;
END;
/

Waiting for release old TEMP tablespace usage by applications/sessions:

lsof | grep temp01.dbf | wc -l
124

select count(*) from V$TEMPSEG_USAGE
where tablespace = 'TEMP';

  COUNT(*)
----------
        91

select tablespace, username, count(*)
  from V$TEMPSEG_USAGE
 where 1 = 1
   --and tablespace in ('TEMP', 'TEMP02')
   and tablespace = 'TEMP'
 group by tablespace, username;

when count become zero then drop unused old TEMP tablespace and files:

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Recreate old TEMP tablespace and if you want switch back from TEMP02

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 
  '/oracle/oradata/db/TEMP_01.dbf' SIZE 50M AUTOEXTEND ON;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Switch all existing users back to TEMP tablespace:

set serveroutput on
-- Switch all existing users to new temp tablespace.
BEGIN
  FOR cur_user IN (SELECT username FROM dba_users WHERE temporary_tablespace = 'TEMP02') LOOP
    dbms_output.put_line('ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE TEMP');    
    EXECUTE IMMEDIATE 'ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE TEMP';
  END LOOP;
END;
/

Waiting for release TEMP02 tablespace usage by applications/sessions:

lsof | grep temp02.dbf | wc -l
124

select count(*) from V$TEMPSEG_USAGE
where tablespace = 'TEMP02';

  COUNT(*)
----------
        91

select tablespace, username, count(*)
  from V$TEMPSEG_USAGE
 where 1 = 1
   --and tablespace in ('TEMP', 'TEMP02')
   and tablespace = 'TEMP02'
 group by tablespace, username;

when count become zero then drop unused TEMP02 tablespace and files:

DROP TABLESPACE TEMP02 INCLUDING CONTENTS AND DATAFILES;

Temporary Tablespace Enhancements in Oracle Database 11g

alter tablespace temp shrink space [keep 100m];

The shrink can also be directed to a specific tempfile using the TEMPFILE clause.

ALTER TABLESPACE temp SHRINK TEMPFILE '/oracle/oradata/db/temp01.dbf' KEEP 30M;

View V$TEMPSEG_USAGE

lsof | grep temp01.dbf | wc -l
124

select * from V$TEMPSEG_USAGE

select count(*)
  from V$TEMPSEG_USAGE
 where tablespace = 'TEMP';

select tablespace, username, count(*)
  from V$TEMPSEG_USAGE
 where 1 = 1
   --and tablespace in ('TEMP', 'TEMP02')
   --and tablespace = 'TEMP'
 group by tablespace, username;

Issue: Shrinking of Temp Tablespace does not take effect

Run this query to find if there are any sessions using your temp tablespace a long time:

SELECT se.username username,
       se.SID sid, se.serial# serial#,
       se.status status,
       'alter system kill session ''' || se.sid || ',' || se.serial# || ''' immediate;' kill_session_cmd,
       se.sql_hash_value, se.sql_id,
       se.prev_hash_value,se.machine machine,
       su.TABLESPACE tablespace,su.segtype,
       su.CONTENTS CONTENTS
  FROM v$session se,
       v$tempseg_usage su /* v$sort_usage */
 WHERE se.saddr=su.session_addr
   AND status = 'ACTIVE'
;

Check if you can drop those sessions and then try shrink. Or you can create a new tablespace move every user to that temp tablespace and than drop this old one.