пятница, 9 марта 2012 г.

Shrinking temporary tablespace in Oracle 10g and 11g

Useful links

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 to new temp tablespace.
BEGIN
  FOR cur_user IN (SELECT username FROM dba_users WHERE temporary_tablespace = 'TEMP') LOOP
    EXECUTE IMMEDIATE 'ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE TEMP02';
  END LOOP;
END;
/

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

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 to new temp tablespace.
BEGIN
  FOR cur_user IN (SELECT username FROM dba_users WHERE temporary_tablespace = 'TEMP02') LOOP
    EXECUTE IMMEDIATE 'ALTER USER ' || cur_user.username || ' TEMPORARY TABLESPACE TEMP';
  END LOOP;
END;
/

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

select * from V$TEMPSEG_USAGE

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.

Комментариев нет:

Отправить комментарий