пятница, 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.

Move Flash Recovery Area to a new location

For to accomplish moving flash recovery area:
  1. Change the value of the DB_RECOVERY_FILE_DEST initialization parameter to a new value.
  2. Shut down the instance.
  3. Start up the instance and mount the database.
  4. Execute the ALTER DATABASE FLASHBACK OFF command.
  5. Execute the ALTER DATABASE FLASHBACK ON command.
  6. Open the database.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST= '/u01/oradata/flash';

SQL>  SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE FLASHBACK OFF;

SQL> ALTER DATABASE FLASHBACK ON;

SQL> ALTER DATABASE OPEN;

Use Data Recovery Advisor to Perform Recovery

rman target /
LIST FAILURE;
LIST FAILURE 182 DETAIL;
ADVISE FAILURE;
REPAIR FAILURE;


  • LIST FAILURE - to show information about the failures.
  • ADVISE FAILURE - to obtain a report of your repair options.
  • Finally, REPAIR FAILURE will fix the problem.