#查看被锁对象
select b.owner, b.object_name, a.session_id, a.locked_mode
from v$locked_object a, dba_objects b
where b.object_id = a.OBJECT_ID;
#查看被锁对象和用户信息
SELECT c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.inst_id,
b.status,
b.osuser,
b.machine,
'alter system kill session ''' || b.sid || ',' || b.serial# || ',@' ||b.inst_id || ''';'
FROM gv$locked_object a, gv$session b, dba_objects c
WHERE b.sid = a.session_id
AND a.object_id = c.object_id
and a.inst_id = b.inst_id;
#查看历史阻塞会话和锁信息
select v.sql_text, v.sql_fulltext, sub.*
from v$sql v,
(select sample_time,
s.sql_id sql_id,
session_state,
blocking_session,
owner || '.' || object_name || ':' ||
nvl(subobject_name, '-') obj_name,
s.program,
s.module,
s.machine
from dba_hist_active_sess_history s, dba_objects o
where sample_time between
to_date('27/02/2019 07:30:02', 'DD/MM/YYYY HH24:MI:SS') and
to_date('28/02/2019 15:10:02', 'DD/MM/YYYY HH24:MI:SS')
and event = 'enq: TX - row lock contention'
and o.data_object_id = s.current_obj#
order by 1 desc) sub
where sub.sql_id = v.sql_id;
#找到暂时阻塞的会话和锁定
select s1.username || '@' || s1.machine || ' ( THIS SID=' || s1.sid ||' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid = l1.sid
and s2.sid = l2.sid
and l1.BLOCK = 1
and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2;
#kill掉大于300秒的会话
SELECT 'kill -9 ' || p.spid,
s.username,
'alter system kill session ''' || SID || ',' || s.serial# || ''';'
FROM v$session s, v$process p
WHERE s.paddr = p.addr(+)
AND s.SID IN (select sid
from v$sql_monitor
where status = 'EXECUTING'
and elapsed_time / 1000000 > 300
and username in ('MEHMET', 'SALIH'));
SELECT 'alter system kill session ''' || SID || ',' || s.serial# || ',@' ||
inst_id || ''';',
sid,
username,
serial#,
process,
NVL(sql_id, 0),
sql_address,
blocking_session,
wait_class,
event,
p1,
p2,
p3,
seconds_in_wait
FROM gv$session s
WHERE blocking_session_status = 'VALID'
OR sid IN (SELECT blocking_session
FROM gv$session
WHERE blocking_session_status = 'VALID');
#Oracle–找到被锁住的对象(非死锁)
SELECT a.sid, a.serial#, a.username, c.os_user_name
, a.program, a.logon_time, a.machine, a.terminal
, b.object_id, substr(b.object_name,1,40) object_name
, DECODE(c.locked_mode,1, 'No Lock',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Shared Table',
5, 'Shared Row Exclusive',
6, 'Exclusive') locked_mode
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;
#当前会话中查看引起行锁竞争的语句
select sw.event,
sw.sid,
sw.p1,
sw.p2,
sw.p3,
s.ROW_WAIT_OBJ#,
s.ROW_WAIT_FILE#,
s.ROW_WAIT_BLOCK#,
s.ROW_WAIT_ROW#,
o.OWNER,
o.OBJECT_NAME,
o.OBJECT_ID,
o.DATA_OBJECT_ID,
o.OBJECT_TYPE,
st.sql_id,
st.sql_text
from v$session_wait sw, v$session s, dba_objects o, v$sql st
where sw.sid = s.sid
and o.object_id = s.ROW_WAIT_OBJ#
and (st.sql_id = s.sql_id or st.sql_id = s.prev_sql_id)
and sw.event = 'enq: TX - row lock contention';
#从历史会话中查看引起行锁竞争的语句
select ash.sample_time,
ash.instance_number,
ash.user_id,
u.username,
ash.session_id,
ash.session_serial#,
ash.current_obj#,
o.owner,
o.object_name,
o.object_type,
ash.sql_id,
ash.sql_opname,
ash.wait_class,
ash.program,
ash.module,
ash.blocking_session_status,
ash.blocking_session,
ash.blocking_session_serial#,
ash.blocking_inst_id,
st.inst_id,
st.sql_text
from dba_hist_active_sess_history ash,
dba_users u,
dba_objects o,
gv_$sql st
where to_char(ash.sample_time, 'YYYY-MM-DD hh24:mi:ss') between '2022-03-22 13:30:00' and '2022-03-22 15:30:00'
and ash.time_waited > 0
and ash.session_state = 'WAITING'
and ash.user_id = u.user_id
and ash.current_obj# = o.object_id
and st.sql_id = ash.sql_id
and ash.event = 'enq: TX - row lock contention';
#单实例的会话阻塞
SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid ||
' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' ||
s2.sid || ' ) ' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.sid = l1.sid
AND s2.sid = l2.sid
AND l1.BLOCK = 1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2;
#单实例的会话阻塞,详细信息
SELECT vs.username,
vs.osuser,
vh.sid locking_sid,
vs.status status,
vs.module module,
vs.program program_holding,
jrh.job_name,
vsw.username,
vsw.osuser,
vw.sid waiter_sid,
vsw.program program_waiting,
jrw.job_name,
'alter system kill session ' || '''' || vh.sid || ',' || vs.serial# ||
''';' "Kill_Command"
FROM v$lock vh,
v$lock vw,
v$session vs,
v$session vsw,
dba_scheduler_running_jobs jrh,
dba_scheduler_running_jobs jrw
WHERE (vh.id1, vh.id2) IN (SELECT id1, id2
FROM v$lock
WHERE request = 0
INTERSECT
SELECT id1, id2
FROM v$lock
WHERE lmode = 0)
AND vh.id1 = vw.id1
AND vh.id2 = vw.id2
AND vh.request = 0
AND vw.lmode = 0
AND vh.sid = vs.sid
AND vw.sid = vsw.sid
AND vh.sid = jrh.session_id(+)
AND vw.sid = jrw.session_id(+);
#RAC环境的会话阻塞
SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' ||
S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' ||
S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' ||
S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2
WHERE S1.SID = L1.SID
AND S2.SID = L2.SID
AND S1.INST_ID = L1.INST_ID
AND S2.INST_ID = L2.INST_ID
AND L1.BLOCK > 0
AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1
AND L1.ID2 = L2.ID2;
#RAC环境的会话阻塞和对象信息
SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' ||
S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' ||
S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' ||
S1.INST_ID || ' SID=' || S2.SID || ' ) OBJ_ID:' || L1.ID1 ||
' OBJ_NAME:' || O.OBJECT_NAME AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2, DBA_OBJECTS O
WHERE S1.SID = L1.SID
AND S2.SID = L2.SID
AND S1.INST_ID = L1.INST_ID
AND S2.INST_ID = L2.INST_ID
AND L1.ID1 = OBJECT_ID
AND L1.ID1 = O.OBJECT_ID
AND L1.BLOCK > 0
AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1
AND L1.ID2 = L2.ID2;
#RAC环境的会话阻塞,针对某个具体的对象
SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' ||
S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' ||
S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' ||
S1.INST_ID || ' SID=' || S2.SID || ' ) OBJ_ID:' || L1.ID1 ||
' OBJ_NAME:' || O.OBJECT_NAME AS BLOCKING_STATUS
FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2, DBA_OBJECTS O
WHERE S1.SID = L1.SID
AND S2.SID = L2.SID
AND S1.INST_ID = L1.INST_ID
AND S2.INST_ID = L2.INST_ID
AND L1.ID1 = OBJECT_ID
AND L1.ID1 = O.OBJECT_ID
AND L1.BLOCK > 0
AND L2.REQUEST > 0
AND L1.ID1 = L2.ID1
AND L1.ID2 = L2.ID2
AND object_id in (SELECT OBJECT_ID
FROM DBA_OBJECTS
WHERE OWNER = 'ABC'
AND OBJECT_NAME = 'XYZ');