Oracle：查找锁定的表和解锁

查找锁定的表

1.

SELECT S.SID SESSION_ID,       S.USERNAME,       DECODE(LMODE,              0,              'None',              1,              'Null',              2,              'Row-S (SS)',              3,              'Row-X (SX)',              4,              'Share',              5,              'S/Row-X (SSX)',              6,              'Exclusive',              TO_CHAR(LMODE)) MODE_HELD,       DECODE(REQUEST,              0,              'None',              1,              'Null',              2,              'Row-S (SS)',              3,              'Row-X (SX)',              4,              'Share',              5,              'S/Row-X (SSX)',              6,              'Exclusive',              TO_CHAR(REQUEST)) MODE_REQUESTED,       O.OWNER || '.' || O.OBJECT_NAME || ' (' || O.OBJECT_TYPE || ')',       S.TYPE LOCK_TYPE,       L.ID1 LOCK_ID1,       L.ID2 LOCK_ID2  FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S WHERE L.SID = S.SID   AND L.ID1 = O.OBJECT_ID

说明：SESSION_ID, USERNAME,  MODE_HELD,  MODE_REQUESTED, OBJECT_NAME, LOCK_TYPE, LOCK_ID

分别是 拥有锁的SESSION_ID,拥有锁的USERNAME,锁的执行模式MODE_HELD,锁的请求MODE_REQUESTED,锁所在的数据库对象名
(是你要看的 ),锁的类型,锁的ID

2.

SELECT A.OWNER,
       A.OBJECT_NAME,
       B.XIDUSN,
       B.XIDSLOT,
       B.XIDSQN,
       B.SESSION_ID,
       B.ORACLE_USERNAME,
       B.OS_USER_NAME,
       B.PROCESS,
       B.LOCKED_MODE,
       C.MACHINE,
       C.STATUS,
       C.SERVER,
       C.SID,
       C.SERIAL#,
       C.PROGRAM
  FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C
WHERE (A.OBJECT_ID = B.OBJECT_ID)
   AND (B.PROCESS = C.PROCESS)     
ORDER BY 1, 2;

删除死锁的方法： 在上面第二个SQL的基础上得到sid和serial#，然后执行 alter   system   kill   session   'sid,serial#'