文章目录

• 前言
• 一、步骤

前言

今天就整理了一下简单的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 ORDER BY SID, S.SERIAL#;

三、杀掉锁表进程

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

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

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