ora 锁查询及处理

--查询有没有锁及产生锁的用户

SELECT USERNAME, LOCKWAIT, STATUS, MACHINE, PROGRAM
  FROM V$SESSION
 WHERE SID IN (SELECT SESSION_ID FROM V$LOCKED_OBJECT);

 --查询产生锁的语句

SELECT SQL_TEXT
  FROM V$SQL
 WHERE HASH_VALUE IN
       (SELECT SQL_HASH_VALUE
          FROM V$SESSION
         WHERE SID IN (SELECT SESSION_ID FROM V$LOCKED_OBJECT));

--查询session_id和serial等产生锁的进程，用于杀死锁     

SELECT DISTINCT S.USERNAME,
       L.SESSION_ID,
       S.SERIAL#,
       L.ORACLE_USERNAME,
       L.OS_USER_NAME,
       L.PROCESS
  FROM V$LOCKED_OBJECT L, V$SESSION S
 WHERE L.SESSION_ID = S.SID;

--杀死锁

alter system kill session '3410,54127'; --(sesseion_id,serial#)其中sid=l.session_id