Oracle-锁的查询和处理

2024-09-01 09:38:34

1.查出锁定object的session的信息以及被锁定的object名

SELECT L.SESSION_ID SID,

       S.SERIAL#,

       L.LOCKED_MODE,

       L.ORACLE_USERNAME,

       L.OS_USER_NAME,

       S.MACHINE,

       S.TERMINAL,

       O.OBJECT_NAME,

       S.LOGON_TIME

  FROM V$LOCKED_OBJECT L, ALL_OBJECTS O, V$SESSION S

 WHERE L.OBJECT_ID = O.OBJECT_ID

   AND L.SESSION_ID = S.SID

 ORDER BY SID, S.SERIAL#;

2.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句

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.ACTION

  FROM 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#;

2、解锁表

--kill session语句

--'151,3027'格式：'SID,SERIAL#'

ALTER SYSTEM KILL SESSION '61,563';

最新文章

热门文章