temp tablespace and 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

--create temp tablespace
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE 15G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- change default temporary tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

-- add more tempfile
alter tablespace temp add tempfile '/u03/oradata/datafile/temp03.dbf' size 100m autoextend on maxsize 15G ;

to find what is going on in a database, check v$sort_usage(v$tempseg_usage) and v$sort_segment

-- list sessoin temporary tablespace usage.

SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
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,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;



-- simplized sessoin temporary tablespace usage.

SELECT S.sid || ',' || S.serial# sid_serial, S.username, T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace, T.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 sid_serial;




-- list temporary tablespace 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


reference:
http://www.idevelopment.info/data/Oracle/DBA_tips/Tablespaces/TBS_3.shtml
http://gavinsoorma.com/2009/06/temp-tablespace-usage/