【Oracle 锁表操作】

原创已于 2023-03-01 11:31:18 修改

· 602 阅读

0 ·

版权

文章标签：

#oracle #数据库 #sql

于 2023-03-01 11:30:03 首次发布

一、Oracle 锁表操作

1、查看锁表项目

select sess.sid,sess.serial#, lo.oracle_username,lo.os_user_name, 
ao.object_name,lo.locked_mode  from v$locked_object lo,dba_objects 
ao,v$session sess where ao.object_id=lo.object_id and lo.session_id=sess.sid;

2、杀掉锁表进程 --分别为SID和SERIAL#号

alter system kill session '68,51';

3、查看数据库引起锁表的sql

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 (SELECT DISTINCT T2.SID
                   FROM V$LOCKED_OBJECT T1, V$SESSION T2
                  WHERE T1.SESSION_ID = T2.SID)
AND A.SQL_ADDRESS = C.ADDRESS(+) ORDER BY C.PIECE;