select sample_time,inst_id,session_id,session_serial#,sql_id,sql_opname, event,blocking_inst_id,blocking_session,blocking_session_serial#, level lv, connect_by_isleaf isleaf, sys_connect_by_path(inst_id||'_'||session_id||'_'||session_serial#||':'||sql_id||':'||sql_opname,'->'...
Oracle provide views like, DBA_BLOCKERS and V$LOCK using which we can easily find the blocking locks. Here, we will try to find blocking locks using V$LOCK view which is faster to query and makes it easy to identify the blocking session. SQL> select * from v$lock ; Here we are intere...
--即session 1073,4642是堵塞者,后面2个session是被堵塞者 --Author : Leshami --Blog : http://blog.csdn.net/leshami --以下查询正在堵塞的session id,SQL语句以及被堵塞的时间 scott@CNMMBO> @blocking_session_detail.sql 'SID='||A.SID||'WAITCLASS='||A.WAIT_CLASS||'TIME='||A.SECONDS_IN_WA...
robin@szdb:~/dba_scripts/custom/sql> more blocking_session_detail.sql --to find the query for blocking session --access privileges: select on v$session, v$sqlarea select 'sid=' || a.sid || ' wait class=' || a.wait_class || ' time=' || a.seconds_in_wait || chr (10) || '...
scott@CNMMBO>@blocking_session_detail.sql'SID='||A.SID||'WAITCLASS='||A.WAIT_CLASS||'TIME='||A.SECONDS_IN_WAIT||CHR(10)||'QUERY='||B.SQL_TEXT---sid=1067Wait Class=Application Time=5995Query=update scott.empsetsal=sal+100where empno=7788sid=1065Wait Class=Application Time=225Query...
SELECTsid, serial#, osuser, programFROM v$sessionWHEREsidIN (SELECTblocking_sessionFROMv$sessionWHEREevent='latch: library cache'); 查询高子游标计数和高版本sql SELECTDISTINCTsql_id FROM v$sql_plan WHERE child_number >= 300SELECTsql_id, version_coun...
SESSION中还增加了BLOCKING_SESSION等字段,以前我们需要通dba_waiters等视图才能获得的信息,现在也可以直接从V SESSION视图中每个字段的含义。 首先,请看官方文档上面关于V$SESSION字段的说明: v$session:This view has one row for every session connected to the database instance. The sessions include user sessions...
sid2的表级锁为31891000AE40189885390OD60189655471930TX04189798331TO30SQL>select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING'andFINAL_BLOCKING_SESSION_STATUS='VALID';SIDFINAL_BLOCKING_SESSIONEVENT---189161enq:TX-row lock contention 案例2会话1的sid是161,会话2的sid是189 代码语言...
t2.event,blocking_session as b_sid,final_blocking_session as f_b_sid, t2.p1,t2.p2,t2.p3,round(t2.wait_time_micro/1E6,4) waittime, T2.LOGON_TIME,t1.LOCKED_MODE FROM GV$LOCKED_OBJECT T1, GV$SESSION T2, DBA_OBJECTS T3
1* select sid,blocking_session,username,program,event,to_char(logon_time,'yyyy-mm-dd hh24:mi...