1) Find the session id (SID), serial# and instance (inst_id) of your session that is hanging. 2) Is your session being blocked? SELECT s.blocking_status, s.blocking_session, s.event FROM gv$session s WHERE s.sid = [789] AND s.serial# = [98543] AND s.inst_id = [1] If any one of these three is true: 1) The blocking_status is VALID or […]
A useful query for getting temp usage by session: SELECT s.process, s.machine, s.username, s.inst_id, s.sid, s.serial#, s.sql_id, s.event,s.seconds_in_wait, s.module, s.action, temp.temp_gb, s.blocking_session, s.blocking_instance, s.program, s.logon_time FROM ( SELECT inst_id, session_adr, session_num, ts#, username, sql_id, sum(blocks*8192)/(1024*1024*1024) temp_gb FROM gv$sort_usage GROUP BY inst_id, session_addr, session_num, ts#, username, sql_id ) temp, gv$session s WHERE s.inst_id = temp.inst_id AND […]