SRDC - Temp Usage: Checklist of Evidence to Supply (Doc ID 1910608.1)

 

Enter the Main Content

Alert log file
60-min AWR report at the time of the error

The script must be run at the time where temp usage is high / ORA-1652 is reported
It's highly recommended to run the script at least 3-4 times at distinct intervals of the problem to provide a more consistent image of the problem to the support.

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set pagesize 9999
spool /tmp/dba_data_files.html
set markup html on
SELECT A.inst_id,
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 gv$sort_segment A,
(SELECT B.INST_ID,
B.name,
C.block_size,
SUM (C.bytes) / 1024 / 1024 mb_total
FROM gv$tablespace B,
gv$tempfile C
WHERE B.ts# = C.ts#
AND c.inst_id=b.inst_id
GROUP BY B.INST_ID,
B.name,
C.block_size
) D
WHERE A.tablespace_name = D.name
AND A.inst_id =D.inst_id
GROUP BY a.inst_id,
A.tablespace_name,
D.mb_total
ORDER BY 1,2;

SELECT sysdate "TIME_STAMP", vsu.username, vs.sid, vp.spid, vs.sql_id, vst.sql_text,vsu.segtype, vsu.tablespace,vs.service_name,
sum_blocks*dt.block_size/1024/1024 usage_mb
FROM
(
SELECT username, sqladdr, sqlhash, sql_id, tablespace, segtype,session_addr,
sum(blocks) sum_blocks
FROM v$tempseg_usage
group by username, sqladdr, sqlhash, sql_id, tablespace, segtype,session_addr
) "VSU",
v$sqltext vst,
v$session vs,
v$process vp,
dba_tablespaces dt
WHERE vs.sql_id = vst.sql_id
AND vsu.session_addr = vs.saddr
AND vs.paddr = vp.addr
AND vst.piece = 0
--AND vs.status='ACTIVE'
AND dt.tablespace_name = vsu.tablespace
order by usage_mb;

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE b.tablespace = '<temporary tablespace name to monitor>'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.tablespace, b.blocks;
spool off
In case of RAC database please run the following script as well (please run directly after first script completes)

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set pagesize 9999
spool /tmp/dba_data_files.html
set markup html on
select inst_id, tablespace_name, round((total_blocks*8192)/(1024*1024*1024),2) "Space(GB)" from gv$sort_segment where tablespace_name='XY_TEMP' order by 1;
select * from gv$sort_segment
select sum(bytes), owner from gv$temp_extent_map group by owner;
select inst_id, blocks_cached, blocks_used, extents_cached, extents_used from GV$TEMP_EXTENT_POOL;
spool off