Temporary Tablespace Management

-----------------Total/Used/Free space by TEMP TABLESPACE----------------------

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;

---------------- TEMP SEGMENTS UTLIZATION///Sort Space Usage by Session in case of TEMP TABLESPACE--------

col SID_SERIAL for a20
col PROGRAM for a20
set lin 200
col OSUSER for a10
col MODULE for a10
col MODULE for a20
col TABLESPACE for a10
col USERNAME for a10
col SID_SERIAL 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 sid_serial;

----------------- SOP TEMP UTILIZATION -------------------

set lines 300
set pagesize 20000
col USERNAME format a12 word_wrap
col OSUSER format a10 word_wrap
col TABLESPACE format a10 word_wrap
col sql_text format a50 word_wrap
break on tablespace on report
compute Sum LABEL SUM of temp_size_kb on tablespace
SELECT ses.username, ses.sid, ses.serial#, ses.osuser, tu.tablespace, (tu.blocks*temp_tspace.block_size)/1024 temp_size_kb, sql.sql_text
FROM v$session ses, v$tempseg_usage tu, v$sqlarea sql,
(
SELECT tablespace_name,
block_size
FROM dba_tablespaces
WHERE contents = 'TEMPORARY'
) temp_tspace
WHERE ses.saddr = tu.session_addr
AND sql.address= ses.sql_address
AND sql.hash_value = ses.sql_hash_value
AND temp_tspace.tablespace_name = tu.tablespace
ORDER BY tu.tablespace, tu.blocks
/

------------ TEMP FILE-------------

set lin 200
col file_name for a60
select file_name , file_id, tablespace_name , status from dba_temp_files;

-----------TEMP TABLESPACE UTILIZATION--------
SELECT tablespace_name, SUM(bytes_used/1024/1024) "USED MB", SUM(bytes_free/1024/1024) "FREE MB"
FROM V$temp_space_header
GROUP BY tablespace_name;

alter database tempfile '/data/oracle/LNDBIBP1/temp01/temp/LNDBIBP1_TEMP_01.dbf' resize 6900M;