Oracle锁表解决方法

文章目录

前言

今天就整理了一下简单的Oracle解锁和查询锁表的方法；

一、步骤

一、首先PLSQL以管理员的账号（system/admin等）登录

二、查看被锁表信息

SELECT L.SESSION_ID SID,
       S.SERIAL#,
       L.LOCKED_MODE,
       L.ORACLE_USERNAME,
       S.USER#,
       L.OS_USER_NAME,
       S.MACHINE,
       S.TERMINAL,
       A.SQL_TEXT,
       A.ACTIONFROM V$SQLAREA A, V$SESSION S, V$LOCKED_OBJECT L
 WHERE L.SESSION_ID = S.SID
   AND S.PREV_SQL_ADDR = A.ADDRESS
 ORDERBY SID, S.SERIAL#;

三、杀掉锁表进程

ALTER SYSTEM KILLSESSION'210,11562';/*KILL SESSION 两个值要换掉 分别为SID和SERIAL#号*/

四、查看数据库引起锁表的语句

SELECT A.USERNAME,
       A.MACHINE,
       A.PROGRAM,
       A.SID,
       A.SERIAL#,
       A.STATUS,
       C.PIECE,
       C.SQL_TEXT
  FROM V$SESSION A, V$SQLTEXT C
 WHERE A.SID IN(SELECTDISTINCT T2.SID
                   FROM V$LOCKED_OBJECT T1, V$SESSION T2
                  WHERE T1.SESSION_ID = T2.SID)AND A.SQL_ADDRESS = C.ADDRESS(+)ORDERBY C.PIECE;