How to reclaim temp tablespace size?

Post by **jimb** » Sun Aug 12, 2012 12:01 pm

Below are the steps to reclaim temp tablespace size.

PART 1: Pre-Reclamation:
A. Ensure you're on the correct database.
SQL>

select name from v$database;

NAME
---------
DB1

B. Check the current default temp tablespace.

col PROPERTY_VALUE for a20
select PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'
/

PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------
DEFAULT_TEMP_TABLESPACE TEMP

C. Verify who is currently using the temp tablespace

set lines 200
col sid_serial for a10
col username for a12
col osuser for a15
col spid for a6
col module for a20
col program for a20
col tablespace for a10

SELECT S.sid || ',' || S.serial# sid_serial,
S.username,
S.osuser,
P.spid,
S.module,
S.program,
SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace,
COUNT (*) sort_ops
FROM v$sort_usage T,
v$session S,
dba_tablespaces TBS,
v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid,
S.serial#,
S.username,
S.osuser,
P.spid,
S.module,
S.program,
TBS.block_size,
T.tablespace
ORDER BY mb_used DESC;

Code: Select all

SID_SERIAL USERNAME     OSUSER          SPID   MODULE               PROGRAM                 MB_USED TABLESPACE   SORT_OPS
---------- ------------ --------------- ------ -------------------- -------------------- ---------- ---------- ----------
58,61470   SYSTEM       user00         25521  SQL Developer        SQL Developer                 1 TEMP                1

D. Check all tablespace (temp/permanent) tablespace allocation.

set pagesize 200
col tablespace_name for a20 heading "Tablespace Name"
col file_name format a50 heading "Datafile"
col total_bytes format 999,999,999,999.9 heading "Total|Bytes|(MB)"
col bytes_free format 999,999,999.9 heading "Bytes|Free|(MB)"
col bytes_used format 999,999,999.9 heading "Bytes|Used|(MB)"
col percent_free format 9999 heading "% Free"
col percent_used format 9999 heading "% Used"
rem break on report
break on tablespace_name skip 1 on report
compute sum of total_bytes bytes_free bytes_used on tablespace_name
compute sum label "Grand Total" of total_bytes bytes_free bytes_used on report
-- compute avg of percent_free percent_used on tablespace_name

set linesize 160
TTitle left skip 2 "****** Tablespace Space Utilization *******" skip 2

select df.tablespace_name
,df.file_name
,df.bytes/1024/1024 total_bytes
,NVL(sum(fs.bytes/1024/1024),0) bytes_free
,NVL((100*((sum(fs.bytes))/df.bytes)),0) percent_free
,(df.bytes-nvl(sum(fs.bytes),0))/1024/1024 bytes_used
,(100*((df.bytes-nvl(sum(fs.bytes),0))/df.bytes)) percent_used
from sys.dba_data_files df
,sys.dba_free_space fs
where df.file_id = fs.file_id(+)
and df.tablespace_name = fs.tablespace_name(+)
group by df.file_name
,df.tablespace_name
,df.bytes
union all
select tf.tablespace_name
,tf.file_name
,tf.bytes/1024/1024 total_bytes
,sum(sh.bytes_free/1024/1024) bytes_free
,(100*((sum(sh.bytes_used))/tf.bytes)) percent_free
,(tf.bytes-sum(sh.bytes_used))/1024/1024 bytes_used
,(100*((tf.bytes-sum(sh.bytes_used))/tf.bytes)) percent_used
from sys.dba_temp_files tf
,v$temp_space_header sh
where tf.file_id = sh.file_id(+)
and tf.tablespace_name = sh.tablespace_name(+)
group by tf.file_name
,tf.tablespace_name
,tf.bytes
order by 1,2;

TTitle off

------------------------------------------------------------------------------------------------

PART 2: Reclamation

1. If you are reclaiming the default temp tablespace, create a new temp tablespace. In this case, the new temp tbsp is TEMP2.
SQL>

CREATE TEMPORARY TABLESPACE "TEMP2" TEMPFILE '/appl/oracle/oradata/DB1/data/temp02.dbf' SIZE 1073741824 AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

2. Set the new temp tablespace created as the default.
SQL>

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

Database altered.

3. Ensure that nobody is using your current default temp tablespace. Coordinate with other teams if those sessions can be killed.

4. DO NOT PROCEED ON THIS STEP IF THERE ARE STILL USERS USING THE DEFAULT TEMP TABLESPACE. Drop the current default temp tablespace.
SQL>

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

NOTE: At this point, your new default temp tablespace is now "TEMP2". The previous "TEMP" has been dropped. Filesystem size is now reclaimed.

5. Ask Unix team to extend the impacted FS if necessary.

6. Re-create the original default temp tablespace.
SQL>

CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '/appl/oracle/oradata/DB1/temp/temp01.dbf' SIZE 10240M AUTOEXTEND ON NEXT 655360 MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

7. Switch back to original default temp tablespace.
SQL>

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Database altered.

8. Drop the previouly created temp tablespace named "TEMP2"
SQL>

DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;

9. Verify that default temp tablespace is switched back to the original one.

col PROPERTY_VALUE for a20
select PROPERTY_NAME, PROPERTY_VALUE from database_properties where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'
/

PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------
DEFAULT_TEMP_TABLESPACE TEMP

10. Verify FS utilization.

df -h /appl/oracle/oradata/DB1/temp/

Filesystem size used avail capacity Mounted on
/appl/oracle/oradata/DB1/temp
39G 33G 5.8G 86% /appl/oracle/oradata/DB1/temp

10. You may also verify if the file is AUTOEXTENSIBLE.

select substr(file_name,1,60), AUTOEXTENSIBLE, increment_by from dba_temp_files;
select substr(file_name,1,60), AUTOEXTENSIBLE, increment_by from dba_data_files;

-- DONE --