oracle 锁

涉及视图
v$session
v$lock
v$session_wait
v$locked_object
v$sql
dba_objects
gv$active_session_history
手动锁表

SELECT employee_id, salary, commission_pct, job_id
FROM employees
WHERE job_id = 'SA_REP'
FOR UPDATE WAIT 5
ORDER BY employee_id;

1.查看被锁住的对象

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;

2.查看被阻塞的会话

SELECT  SID,SERIAL#,USERNAME  FROM V$SESSION  WHERE SID IN (SELECT BLOCKING_SESSION FROM V$SESSION );

3.结束会话

alter   system kill session '267,763' immediate;
alter   system kill session '267,763,@1' immediate;  集群kill方式

4.查看具体阻塞sql会话信息

SELECT b.sid,
       a.sql_id,
       a.sql_text,
       a.hash_value,
       b.username,
       b.machine,
       a.module,
       c.block,
       c.request,
       d.seconds_in_wait
  FROM v$sql a, v$session b, v$lock c, v$session_wait d
 WHERE c.type = 'TX'
       AND a.sql_id = b.sql_id
       AND b.sid = c.sid
       AND d.sid = b.sid
UNION ALL
SELECT b.sid,
       a.sql_id,
       a.sql_text,
       a.hash_value,
       b.username,
       b.machine,
       a.module,
       c.block,
       c.request,
       d.seconds_in_wait
  FROM v$sql a, v$session b, v$lock c, v$session_wait d
 WHERE c.type = 'TX'
       AND a.sql_id = b.prev_sql_id
       AND b.sid = c.sid
       AND d.sid = b.sid
       AND c.block = 1
----------------------------------集群查询-------------------------------------       
       SELECT b.inst_id,
       b.sid,
       b.serial#,
       a.sql_id,
       a.sql_text,
       a.hash_value,
       b.username,
       b.machine,
       a.module,
       c.block,
       c.request,
       d.seconds_in_wait
  FROM gv$sql a, gv$session b, gv$lock c, gv$session_wait d
 WHERE c.type = 'TX'
       AND a.sql_id = b.sql_id
       AND b.sid = c.sid
       AND d.sid = b.sid
UNION ALL
SELECT 
b.inst_id,b.sid,
        b.serial#,
       a.sql_id,
       a.sql_text,
       a.hash_value,
       b.username,
       b.machine,
       a.module,
       c.block,
       c.request,
       d.seconds_in_wait
  FROM gv$sql a, gv$session b, gv$lock c, gv$session_wait d
 WHERE c.type = 'TX'
       AND a.sql_id = b.prev_sql_id
       AND b.sid = c.sid
       AND d.sid = b.sid
       AND c.block = 1;

5.查询锁住对象及关联sql

SELECT A.OWNER 方案名,
A.OBJECT_NAME 表名,
B.XIDUSN 回滚段号,
B.XIDSLOT 槽号,
B.XIDSQN 序列号,
B.SESSION_ID 锁表SESSION_ID,
B.ORACLE_USERNAME 锁表用户名,
decode(D.type,
'XR',
'NULL',
'RS',
'SS(Row-S)',
'CF',
'SS(Row-S)',
'TM',
'TABLE LOCK',
'PW',
'TABLE LOCK',
'TO',
'TABLE LOCK',
'TS',
'TABLE LOCK',
'RT',
'ROW LOCK',
'TX',
'ROW LOCK',
'MR',
'S(Share)',
NULL) 锁定方式,
C.MACHINE 用户组,
C.TERMINAL 机器名,
B.OS_USER_NAME 系统用户名,
B.PROCESS 系统进程id,
DECODE(C.STATUS, 'INACTIVE', '不活动', 'ACTIVE', '活动') 活动情况,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM 连接方式,
C.LOGON_TIME,
s.sql_text
FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C, v$lock d,v$sql s
WHERE( (A.OBJECT_ID = B.OBJECT_ID)
AND (B.PROCESS = C.PROCESS)
and C.sid = d.sid
and B.LOCKED_MODE = D.LMODE)
and c.prev_sql_id=s.sql_id
ORDER BY 1, 2;
-------------------------------------集群回话查询--------------------------------
SELECT A.OWNER 方案名,
A.OBJECT_NAME 表名,
B.XIDUSN 回滚段号,
B.XIDSLOT 槽号,
B.XIDSQN 序列号,
B.SESSION_ID 锁表SESSION_ID,
C.inst_id,
C.serial#,
C.sid,
B.ORACLE_USERNAME 锁表用户名,
decode(D.type,
'XR',
'NULL',
'RS',
'SS(Row-S)',
'CF',
'SS(Row-S)',
'TM',
'TABLE LOCK',
'PW',
'TABLE LOCK',
'TO',
'TABLE LOCK',
'TS',
'TABLE LOCK',
'RT',
'ROW LOCK',
'TX',
'ROW LOCK',
'MR',
'S(Share)',
NULL) 锁定方式,
C.MACHINE 用户组,
C.TERMINAL 机器名,
B.OS_USER_NAME 系统用户名,
B.PROCESS 系统进程id,
DECODE(C.STATUS, 'INACTIVE', '不活动', 'ACTIVE', '活动') 活动情况,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM 连接方式,
C.LOGON_TIME,
s.sql_text
FROM ALL_OBJECTS A, gV$LOCKED_OBJECT B, SYS.GV_$SESSION C, gv$lock d,gv$sql s
WHERE( (A.OBJECT_ID = B.OBJECT_ID)
AND (B.PROCESS = C.PROCESS)
and C.sid = d.sid
and B.LOCKED_MODE = D.LMODE)
and c.prev_sql_id=s.sql_id
ORDER BY 1, 2;

6.查看历史锁

select SAMPLE_TIME,session_id,sql_id,event,p1,BLOCKING_SESSION from gv$active_session_history where event = 'enq: TX - row lock contention'
    and sample_time >to_timestamp('2021-11-02 14:00:00','yyyy-mm-dd hh24:mi:ss')
   and sample_time <to_timestamp('2021-11-02 16:00:00','yyyy-mm-dd hh24:mi:ss')