temporary tablespace in oracle

When work load is low, we might reclaim space from temporary tablespace and files.

1. check database default temporary tablespace. If a temporary tablespace is default one, you can not drop it.

select property_name, value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

2. Default temporary tablespace might be a temporary tablespace group. check if there is temporary tablespace group.if default temporary tablespace is a  temporary tablespace group, any  member tablespace of the group can not be drop

select * from dba_tablespace_groups;

3. create a new temporary tablespace with a small size file

select file_name from dba_temp_files;

create temporary tablespace newtemp tempfile '/u02/app/oradata/newtemp_01.dbf' size 10m autoextent on maxsize 100m
extent management local;

4. change default tablespace name
alter database default temporary tablespace newtemp;

5. if any user use a temporary tablespace as his/her default temporary tablespace, you can not drop the temporary tablespace

select distinct temporary_tablespace from dba_users;

select ' alter user ' || username || ' temporary tablespace newtemp ; ' from dba_users where temporary_tablespace = 'TEMP';

run generated sql statement

6. drop temporary tablespace with file
drop tablespace temp including contents and datafiles;

7. resize a tempfile
alter database tempfile '/u02/app/oradata/newtemp_01.dbf' resize 50m;


8. if you don't assign a user temporary tablespace explicitly, its temp tbs will be default one.  when you change default temp on database level, its temp tbs will be reset to new default one.

9 assing a temp tbs to temp group
alter tablespace newtemp tablespace group TEMPGROUP;

10, remove a temp tbs from temp group;
alter tablespace newtemp tablespace group '';

11. check temp tbs usage
 v$sort_segment
v$sort_usage

V$SORT_SEGMENT
V$TEMP_CACHE_TRANSFER
V$TEMP_EXTENT_MAP
V$TEMP_EXTENT_POOL
V$TEMP_SPACE_HEADER
V$TEMPFILE
V$TEMPORARY_LOBS
V$TEMPSEG_USAGE -- V$sort_usage
V$TEMPSTAT

EXAMPLE:
check temp usage:

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

Check session level temp usage:

SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, s.sql_id
FROM v$sort_usage T, v$session S, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.tablespace = TBS.tablespace_name
ORDER BY mb_used;




Reference:
http://www.dba-oracle.com/t_temporary_tablespace_groups.htm
http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml
http://www.orafaq.com/forum/t/81733/0/
http://gavinsoorma.com/2009/06/temp-tablespace-usage/