Managing/shrinking TEMP tablespace

posted Sep 8, 2010, 8:16 AM by Sachchida Ojha
11G Method:

SQL> SELECT * FROM dba_temp_free_space;
SQL> select file_name,bytes,blocks from dba_temp_files;
SQL> select name,bytes,blocks from v$tempfile;
you can perform an online shrink of a temporary tablespace using the 
ALTER TABLESPACE command.
SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 100M;
The shrink can also be directed to a specific tempfile using the TEMPFILE clause.
SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/u01/oracle/oradata/temp01.dbf' KEEP 20M;
The KEEP clause specifies the minimum size of the tablespace or tempfile. 
If this is omitted, the database will shrink the tablespace or tempfile 
to the smallest possible size.
SQL> ALTER TABLESPACE temp SHRINK SPACE;
SQL> ALTER DATABASE TEMPFILE ‘+DB1_DATA01/orcl/tempfile/temp.264.32801683′ 
RESIZE 200M;
Before 11G
1. Create new temporary tablespace with smaller size.
SQL> create temporary tablespace TEMP2
tempfile '/app/oradata/u01/temp01.dbf' size 100M
extent management local uniform size 128K;
SQL> alter database default temporary tablespace temp2;
SQL> drop tablespace temp including contents and datafiles;
 
f any users are using the temporary tablespace currently, 
you would not be able to drop the tablespace. 
So, let us find out wihich users are using the 
temporary tablespace right now using bellow query:
SQL> SELECT b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2) as "SIZE",
a.sid,
a.serial#,
a.username,
a.program
FROM sys.v_$session a,
sys.v_$sort_usage b,
sys.v_$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
ORDER BY b.tablespace, b.blocks;


Then kill or wait until the above users finish their jobs, and then try to
drop the temporary tablespace again.
 
You can also see the usage of temporary tablespace using bellow query:

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE
from V$TEMP_SPACE_HEADER;
Comments