收集的一些查询阻塞和锁的SQL，非常好用

1、查询某个连接会话阻塞
col program  format a40
col username  format a10
select b.sid     block_sess,
       bp.SPID   block_spid,
       b.program,
       b.event,
       s.sid,
       sp.SPID,
       s.username,
       s.program,
       s.event
  from v$session s, v$session b, v$process sp, v$process bp
 where s.blocking_session = b.sid
   and s.PADDR = sp.ADDR
   and b.PADDR = bp.ADDR

补充：
（1）Finding Blocked OBJECT#, FILE#, BLOCK# and ROW#  
        select sid, event, row_wait_obj# obj, row_wait_file# file,
        row_wait_block# block, row_wait_row# row
        from v$session s, v$session w
        where w.blocking_session = s.sid;
        SID  EVENT                          OBJ         FILE      BLOCK   ROW
        ---- ------------------------------ ---------- ------  ---------  ----
         9   enq: TX - row lock contention     41456       1      46818     0
（2）查询某个SESSION对应的进程号
select p.spid,s.username,s.sid,s.serial#,s.server,s.paddr,s.status
from v$session s,v$process p
where s.sid='144' and s.PADDR=p.addr   ----这里SID=144
（3）查询谁锁了谁
   select c.terminal||' ('''||a.sid||','||c.serial#||''') is blocking '||b.sid block_msg
from v$lock a,v$lock b,v$session c
where a.id1=b.id1
  and a.id2=b.id2
  and a.block>0
  and a.sid<>b.sid
  and a.sid=c.sid

2、查找library cache lock的源头
SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
 WHERE SADDR in 
  (SELECT KGLLKSES FROM X$KGLLK LOCK_A 
   WHERE KGLLKREQ = 0
     AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
                 WHERE KGLLKSES = 'saddr_from_v$session' /* BLOCKED SESSION */
                 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
                 AND KGLLKREQ > 0)
  );

3、查询Oracle中的阻塞锁(以及阻塞在哪个数据上)的SQL
语句1：----好用，可以涵盖绝大多数信息
Select '节点 ' || a.INST_ID || ' session ' || a.sid || ',' || a_s.SERIAL# ||
' 阻塞了 节点 ' || b.INST_ID || ' session ' || b.SID || ',' || b_s.SERIAL# blockinfo,
a.INST_ID,
a_s.SID,
a_s.SCHEMANAME,
a_s.MODULE,
a_s.STATUS,
a.type lock_type,
a.id1,
a.id2,
decode(a.lmode,0,'none',1,null,2,'row-S (SS)',3,'row-X (SX)',4,'share (S)',5,'S/Row-X (SSX)',6,'exclusive (X)') lock_mode,
'后为被阻塞信息' ,
b.INST_ID blocked_inst_id,
b_s.SID blocked_sid,
b.TYPE blocked_lock_type,
decode(b.request,0,'none',1,null,2,'row-S (SS)',3,'row-X (SX)',4,'share (S)',5,'S/Row-X (SSX)',6,'exclusive (X)') blocked_lock_request,
b_s.SCHEMANAME blocked_SCHEMANAME,
b_s.MODULE blocked_module,
b_s.STATUS blocked_status,
b_s.SQL_ID blocked_sql_id,
obj.owner blocked_owner,
obj.object_name blocked_object_name,
obj.OBJECT_TYPE blocked_OBJECT_TYPE,
case
when b_s.ROW_WAIT_OBJ# <> -1 then
dbms_rowid.rowid_create(1,
obj.DATA_OBJECT_ID,
b_s.ROW_WAIT_FILE#,
b_s.ROW_WAIT_BLOCK#,
b_s.ROW_WAIT_ROW#)
else
'-1'
end blocked_rowid, --被阻塞数据的rowid
decode(obj.object_type,
'TABLE',
'select * from ' || obj.owner || '.' || obj.object_name ||
' where rowid=''' ||
dbms_rowid.rowid_create(1,
obj.DATA_OBJECT_ID,
b_s.ROW_WAIT_FILE#,
b_s.ROW_WAIT_BLOCK#,
b_s.ROW_WAIT_ROW#) || '''',
NULL) blocked_data_querysql
from gv$lock a,
gv$lock b,
gv$session a_s,
gv$session b_s,
dba_objects obj
where a.id1 = b.id1
and a.id2 = b.id2
and a.BLOCK > 0 --阻塞了其他人
and b.request > 0
and ((a.INST_ID = b.INST_ID and a.sid <> b.sid) or
(a.INST_ID <> b.INST_ID))
and a.sid = a_s.sid
and a.INST_ID = a_s.INST_ID
and b.sid = b_s.sid
and b.INST_ID = b_s.INST_ID
and b_s.ROW_WAIT_OBJ# = obj.object_id(+)
order by a.inst_id,a.sid;

语句2：根据用户查询阻塞，偏重于用户。
select /*+ ordered */
  --b.kaddr,
  --b.ID2,b.ID1,
  c.sid,
  lock_waiter.waiting_session,
  lock_blocker.holding_session,
  c.program,
  c.osuser,
  c.machine,
  c.process,
  decode(u.name,
    null,'',
    u.name||'.'||o.name
  ) object,
  c.username,
  decode
  (
    b.type,
    'BL', 'Buffer hash table instance lock',
    'CF', 'Control file schema global enqueue lock',
    'CI', 'Cross-instance function invocation instance lock',
    'CU', 'Cursor bind lock',
    'DF', 'Data file instance lock',
    'DL', 'direct loader parallel index create lock',
    'DM', 'Mount/startup db primary/secondary instance lock',
    'DR', 'Distributed recovery process lock',
    'DX', 'Distributed transaction entry lock',
    'FS', 'File set lock',
    'IN', 'Instance number lock',
    'IR', 'Instance recovery serialization global enqueue lock',
    'IS', 'Instance state lock',
    'IV', 'Library cache invalidation instance lock',
    'JQ', 'Job queue lock',
    'KK', 'Thread kick lock',
    'LA','Library cache lock instance lock (A..P=namespace);',
    'LB','Library cache lock instance lock (A..P=namespace);',
    'LC','Library cache lock instance lock (A..P=namespace);',
    'LD','Library cache lock instance lock (A..P=namespace);',
    'LE','Library cache lock instance lock (A..P=namespace);',
    'LF','Library cache lock instance lock (A..P=namespace);',
    'LG','Library cache lock instance lock (A..P=namespace);',
    'LH','Library cache lock instance lock (A..P=namespace);',
    'LI','Library cache lock instance lock (A..P=namespace);',
    'LJ','Library cache lock instance lock (A..P=namespace);',
    'LK','Library cache lock instance lock (A..P=namespace);',
    'LL','Library cache lock instance lock (A..P=namespace);',
    'LM','Library cache lock instance lock (A..P=namespace);',
    'LN','Library cache lock instance lock (A..P=namespace);',
    'LO','Library cache lock instance lock (A..P=namespace);',
    'LP','Library cache lock instance lock (A..P=namespace);',
    'MM', 'Mount definition global enqueue lock',
    'MR', 'Media recovery lock',
    'NA', 'Library cache pin instance lock (A..Z=namespace)',
    'NB', 'Library cache pin instance lock (A..Z=namespace)',
    'NC', 'Library cache pin instance lock (A..Z=namespace)',
    'ND', 'Library cache pin instance lock (A..Z=namespace)',
    'NE', 'Library cache pin instance lock (A..Z=namespace)',
    'NF', 'Library cache pin instance lock (A..Z=namespace)',
    'NG', 'Library cache pin instance lock (A..Z=namespace)',
    'NH', 'Library cache pin instance lock (A..Z=namespace)',
    'NI', 'Library cache pin instance lock (A..Z=namespace)',
    'NJ', 'Library cache pin instance lock (A..Z=namespace)',
    'NK', 'Library cache pin instance lock (A..Z=namespace)',
    'NL', 'Library cache pin instance lock (A..Z=namespace)',
    'NM', 'Library cache pin instance lock (A..Z=namespace)',
    'NN', 'Library cache pin instance lock (A..Z=namespace)',
    'NO', 'Library cache pin instance lock (A..Z=namespace)',
    'NP', 'Library cache pin instance lock (A..Z=namespace)',
    'NQ', 'Library cache pin instance lock (A..Z=namespace)',
    'NR', 'Library cache pin instance lock (A..Z=namespace)',
    'NS', 'Library cache pin instance lock (A..Z=namespace)',
    'NT', 'Library cache pin instance lock (A..Z=namespace)',
    'NU', 'Library cache pin instance lock (A..Z=namespace)',
    'NV', 'Library cache pin instance lock (A..Z=namespace)',
    'NW', 'Library cache pin instance lock (A..Z=namespace)',
    'NX', 'Library cache pin instance lock (A..Z=namespace)',
    'NY', 'Library cache pin instance lock (A..Z=namespace)',
    'NZ', 'Library cache pin instance lock (A..Z=namespace)',
    'PF', 'Password File lock',
    'PI', 'Parallel operation locks',
    'PS', 'Parallel operation locks',
    'PR', 'Process startup lock',
    'QA','Row cache instance lock (A..Z=cache)',
    'QB','Row cache instance lock (A..Z=cache)',
    'QC','Row cache instance lock (A..Z=cache)',
    'QD','Row cache instance lock (A..Z=cache)',
    'QE','Row cache instance lock (A..Z=cache)',
    'QF','Row cache instance lock (A..Z=cache)',
    'QG','Row cache instance lock (A..Z=cache)',
    'QH','Row cache instance lock (A..Z=cache)',
    'QI','Row cache instance lock (A..Z=cache)',
    'QJ','Row cache instance lock (A..Z=cache)',
    'QK','Row cache instance lock (A..Z=cache)',
    'QL','Row cache instance lock (A..Z=cache)',
    'QM','Row cache instance lock (A..Z=cache)',
    'QN','Row cache instance lock (A..Z=cache)',
    'QP','Row cache instance lock (A..Z=cache)',
    'QQ','Row cache instance lock (A..Z=cache)',
    'QR','Row cache instance lock (A..Z=cache)',
    'QS','Row cache instance lock (A..Z=cache)',
    'QT','Row cache instance lock (A..Z=cache)',
    'QU','Row cache instance lock (A..Z=cache)',
    'QV','Row cache instance lock (A..Z=cache)',
    'QW','Row cache instance lock (A..Z=cache)',
    'QX','Row cache instance lock (A..Z=cache)',
    'QY','Row cache instance lock (A..Z=cache)',
    'QZ','Row cache instance lock (A..Z=cache)',
    'RT', 'Redo thread global enqueue lock',
    'SC', 'System commit number instance lock',
    'SM', 'SMON lock',
    'SN', 'Sequence number instance lock',
    'SQ', 'Sequence number enqueue lock',
    'SS', 'Sort segment locks',
    'ST', 'Space transaction enqueue lock',
    'SV', 'Sequence number value lock',
    'TA', 'Generic enqueue lock',
    'TS', 'Temporary segment enqueue lock (ID2=0)',
    'TS', 'New block allocation enqueue lock (ID2=1)',
    'TT', 'Temporary table enqueue lock',
    'UN', 'User name lock',
    'US', 'Undo segment DDL lock',
    'WL', 'Being-written redo log instance lock',
    b.type
  ) lock_type,
  decode
  (
    b.lmode,
    0, 'None',           /* Mon Lock equivalent */
    1, 'Null',           /* N */
    2, 'Row-S (SS)',     /* L */
    3, 'Row-X (SX)',     /* R */
    4, 'Share',          /* S */
    5, 'S/Row-X (SRX)',  /* C */
    6, 'Exclusive',      /* X */
    to_char(b.lmode)
  ) mode_held,
  decode
  (
    b.request,
    0, 'None',           /* Mon Lock equivalent */
    1, 'Null',           /* N */
    2, 'Row-S (SS)',     /* L */
    3, 'Row-X (SX)',     /* R */
    4, 'Share',          /* S */
    5, 'S/Row-X (SSX)',  /* C */
    6, 'Exclusive',      /* X */
    to_char(b.request)
  ) mode_requested
from
  v$lock b
  ,v$session c
  ,sys.user$ u
  ,sys.obj$ o
  ,( select * from sys.dba_waiters) lock_blocker
  ,( select * from sys.dba_waiters) lock_waiter
where
b.sid = c.sid
and u.user# = c.user#
and o.obj#(+) = b.id1
and lock_blocker.waiting_session(+) = c.sid
and lock_waiter.holding_session(+) = c.sid
and c.username = 'DBMGR'               ----   指定用户
order by b.ID2,b.ID1,kaddr, lockwait