Query to find the session consuming high Temp TableSpace
Check the Size of the current Temp usage
select tablespace_name, tablespace_size/1024/1024 "Total Space MB",allocated_space/1024/1024 "Alloc Space MB",free_space/1024/1024 "Free Space MB"
from dba_temp_free_space;
Based on the above output run below. I have run for a temp tablespace TEMP2.
SELECT s.sid, s.username, u.tablespace, s.sql_hash_value||' or '||u.sqlhash hash_value, u.segtype, u.contents, u.blocks
FROM v$session s, v$tempseg_usage u
WHERE s.saddr=u.session_addr
and u.tablespace='TEMP2'
order by u.blocks desc;
The Top row given will have the sid for the session holding maximum space. Based on that fine more info of the session. Click Here for the Session query
Find based on parsing schema name when the open cursor is used.
select hash_value, sorts, rows_processed/executions
from v$sql
where hash_value in (select hash_value from v$open_cursor where sid=1121)
and sorts > 0
and PARSING_SCHEMA_NAME='APPS'
order by rows_processed/executions;
Post a Comment
Post a Comment