oracle 查询实锁常用的sql

1  .查询谁锁定了谁--查看某个session被另一个session锁定了，很好用。

SELECT A.SID || ' is blocking ' || B.SID
  FROM GV$LOCK A, GV$LOCK B
  WHERE A.ID1 = B.ID1
   AND A.ID2 = B.ID2
   AND A.BLOCK = 1
   AND B.REQUEST > 0 ;

2 .查询谁被锁了

SELECT P.SPID,
       A.SERIAL#,
       C.OBJECT_NAME,
       B.SESSION_ID,
       B.ORACLE_USERNAME,
       B.OS_USER_NAME,
       B.LOCKED_MODE,
       A.LOCKWAIT,
       D.SQL_TEXT
  FROM GV$PROCESS P, GV$SESSION A, GV$LOCKED_OBJECT B, ALL_OBJECTS C,GV$sqlarea D
  WHERE P.ADDR = A.PADDR
   AND A.PROCESS = B.PROCESS
   AND C.OBJECT_ID = B.OBJECT_ID
   AND a.SID = b.SESSION_ID
   AND A.SQL_ADDRESS = D.ADDRESS;

3  .同样查询锁的信息

SELECT S.USERNAME,
       S.SID,
       S.SERIAL#,
       DECODE (LO.LOCKED_MODE,
              0 ,
              'None' ,
              1 ,
              'Null' ,
              2 ,
              'Row-S(ss)' ,
              3 ,
              'Row-X(sx)' ,
              4 ,
              'share' ,
              5 ,
              's/row-x(ssx)' ,
              6 ,
              'Exclusive' ,
              TO_CHAR(LO.LOCKED_MODE)) MODE_LOCKED,
   OBJECT LO, DBA_OBJECTS DO
  WHERE S.SID = LO.SESSION_ID    LO.OS_USER_NAME,
       DO.OBJECT_NAME,
       DO.OBJECT_TYPE
  FROM V$SESSION S, V$LOCKED_
   AND LO.OBJECT_ID = DO.OBJECT_ID;

5  .查询被锁的存储过程，有时候我们遇到某个存储过程编译不过去了，可以用下面的方法查询杀掉

 
SELECT p.SPID,S.sid
FROM V$DB_OBJECT_CACHE   OC,
       V$OBJECT_DEPENDENCY OD,
       DBA_KGLLOCK         W,
       V$SESSION           S,
       V$PROCESS           P
WHERE OD.TO_OWNER = OC.OWNER
   AND OD.TO_NAME = OC.NAME
   AND OD.TO_ADDRESS = W.KGLLKHDL
   AND W.KGLLKUSE = S.SADDR
   AND P.ADDR = S.PADDR
   AND OC.NAME = 'PKG_K_TEST'

杀掉会话

--根据sid 查询psid
  SELECT PRO.SPID
  FROM V$SESSION SES, V$PROCESS PRO
  WHERE SES.SID = : SID
   AND SES.PADDR = PRO.ADDR;

--oracle 下杀掉相关进程 
alter system kill session ‘sid,serial#’;

linux 下查询相关线程
ps -ef|grep spid
kill -9 spid