如何查询锁表的相关对象

1.查询锁的信息

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,
      e.SQL_TEXT,  
      C.SERIAL#,   
      C.PROGRAM 连接方式,   
      C.LOGON_TIME   
 FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C, v$lock d,v$sqltext E 
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.SQL_ID=e.sql_id (+)
ORDER BY 1, 2; 

select t2.username,
       t2.sid,
       t2.serial#,
       t3.object_name,
       t2.OSUSER,
       t2.MACHINE,
       t2.PROGRAM,
       t2.LOGON_TIME,
       t2.COMMAND,
       t2.LOCKWAIT,
       t2.SADDR,
       t2.PADDR,
       t2.TADDR,
       t2.SQL_ADDRESS,
       t1.LOCKED_MODE
  from v$locked_object t1, v$session t2, dba_objects t3,v$sql t4
 where t1.session_id = t2.sid
   and t1.object_id = t3.object_id
  --- and t2.SADDR=t4.ADDRESS(+)

   and t2.sql_id=t4.sql_id
 order by t2.logon_time

 

alter system kill session '139, 182'
139 : sid  182 : sertal#

2.查询堵塞者与被堵塞者

SELECT
    s1.username  "WAITING USER"
  , s1.osuser    "OS User"

  , s1.LOGON_TIME "logon time"
  , w.session_id "Sid"
  , p1.spid      "PID"
  , q1.SQL_TEXT  "SQLTEXT"
  , s2.username         "HOLDING User"
  , s2.osuser           "OS User"

  , s2.LOGON_TIME "logon time"
  , h.session_id        "Sid"
  , p2.spid             "PID"
  , q2.SQL_TEXT         "SQLTEXT"
FROM
    sys.v_$process p1
  , sys.v_$process p2
  , sys.v_$session s1
  , sys.v_$session s2
  , dba_locks  w
  , dba_locks  h
  , v$sql q1
  , v$sql q2
WHERE
      h.mode_held      != 'None'
  AND h.mode_held      != 'Null'
  AND w.mode_requested != 'None'
  AND w.lock_type  (+)  = h.lock_type
  AND w.lock_id1   (+)  = h.lock_id1
  AND w.lock_id2   (+)  = h.lock_id2
  AND w.session_id      = s1.sid   (+)
  AND h.session_id      = s2.sid   (+)
  AND s1.paddr          = p1.addr  (+)
  AND s2.paddr        = p2.addr  (+)
  AND s1.SQL_ID=q1.SQL_ID(+)
  AND s2.SQL_ID=q2.SQL_ID(+)
  order by h.session_id

  ;

SELECT WSN.USERNAME,
       WSN.MACHINE,
       WSN.SID,
       WSN.SERIAL#,
       WSL.SQL_TEXT,
       HSN.USERNAME,
       HSN.MACHINE,
       HSN.SID,
       HSN.SERIAL#,
       HSL.SQL_TEXT
  FROM DBA_WAITERS W,
       V$SESSION   WSN,
       V$SQLAREA   WSL,
       V$SESSION   HSN,
       V$SQLAREA   HSL
 WHERE W.WAITING_SESSION = WSN.SID
   AND WSN.SQL_ID = WSL.SQL_ID
   AND W.HOLDING_SESSION = HSN.SID
   AND HSN.SQL_ID = HSL.SQL_ID(+)
;

-------------------------------------------------------------------------------------------------------

$ cat sw.sql

col event    for a30
col username for a15
select sid,
       serial#,
       username,
       event,
       sql_hash_value,
       sql_id,
       last_call_et as "times(s)",
       p1,
       p2,
       p3
  from v$session
 where WAIT_CLASS <> 'Idle'
 order by event, last_call_et desc
/

$ cat lock_tree2.sql

col user_name for a15
col object_name for a40
col sids for a15

SELECT /*+ NO_MERGE(O) NO_MERGE(S) NO_MERGE(V) NO_MERGE(B) */
 LPAD('--', DECODE(B.BLOCK, 1, 0, 4)) || S.USERNAME USER_NAME,
 B.TYPE,
 O.OWNER || '.' || O.OBJECT_NAME OBJECT_NAME,
 S.SID || ',' || S.SERIAL# as sids,
 sw.event,
 S.sql_id,
 s.status,
 s.last_call_et,
 DECODE(B.REQUEST, 0, 'BLOCKED', 'WAITING') STATUS,
 b.lmode,
 b.request
  FROM DBA_OBJECTS O, V$SESSION S, V$LOCK V, V$LOCK B, v$session_wait sw
 WHERE V.ID1 = O.OBJECT_ID
   AND s.sid = sw.sid
   AND V.SID = S.SID
   AND V.SID = B.SID
   AND (B.BLOCK = 1 OR B.REQUEST > 0)
   AND V.TYPE = 'TM'
 ORDER BY B.ID2, V.ID1, USER_NAME DESC 
 /

$ cat sess_info.sql

col "Session Info" for a80  
select ' OS ID(SPID): ' || p.spid || chr(10) ||
       ' Sid , Serial#: ' || s.sid || ' , ' || s.serial# || chr(10) ||
       ' DB user / OS user: ' || s.username || ' / ' || s.osuser ||
       chr(10) || ' Machine - Terminal: ' || s.machine || ' - ' ||
       s.terminal || chr(10) || 'Client Program Name: ' || s.program ||
       chr(10) || '             Status: ' || s.status || chr(10) ||
       '              Event: ' || s.event || chr(10) ||
       '             SQL ID: ' || s.sql_id || chr(10) ||
       '        Prev SQL ID: ' || s.prev_sql_id "Session Info"
  from v$process p, v$session s
 where p.addr = s.paddr
   and s.sid = &sid;

$ cat sqltext.sql

set line 1000 pages 999
set trims on
select sql_text from v$sqltext where sql_id='&sql_id' order by piece
/