【oracle】锁相关脚本

#查看哪些表被锁住了

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');