TEMP scripts

Sort space usage by session:

col sid_serial for a20
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; (or) set lines 1500 pages 9999
column sid format 9999
column username format a15
column SQL_EXEC_START for a21
column sql_text format a50
column module format a35
column sql_text format a50 SELECT a.inst_id,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_USED,a.sql_id,a.sql_child_number child,c.plan_hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,c.rows_processed,a.status,
-- c.sql_text
substr(c.sql_text,1,50) sql_text
FROM gv$session a, gv$tempseg_usage b, gv$sqlarea c
,(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
-- AND c.hash_value = a.sql_hash_value
AND a.inst_id=b.inst_id
ORDER BY 6 desc; (or) set lines 1500 pages 9999
column sid format 9999
column username format a15
column SQL_EXEC_START for a21
column sql_text format a50
column module format a35
column sql_text format a50
break on report
compute SUM of MB_USED on report
SELECT a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.block_size)/1048576 MB_USED,a.sql_id,a.sql_child_number child,c.plan_hash_value,to_char (a.sql_exec_start, 'dd-Mon-yyyy hh24:mi:ss') sql_exec_start,c.rows_processed,a.status,
-- c.sql_text
substr(c.sql_text,1,50) sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, 5 desc; (or) select ROUND(SUM(tempseg_size)/1048576) temp_mb from gv$sql_workarea_active WHERE sid=&sid;

History of Temp tablespace usage:

select sql_id,SQL_PLAN_HASH_VALUE,max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024) gig
from DBA_HIST_ACTIVE_SESS_HISTORY where sample_time > sysdate-10 and TEMP_SPACE_ALLOCATED > (10*1024*1024*1024) group by sql_id,SQL_PLAN_HASH_VALUE order by sql_id;

Shrink Temp file:

ALTER TABLESPACE temp SHRINK TEMPFILE '+ORA_DATA/JAPAN/tempfile/temp.313.868020865' KEEP 500M; Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8