oracle 监听表,[DB]ORACLE监控锁表（锁表与解表）

--以下几个为相关表

SELECT * FROM V$LOCK;

SELECT * FROM V$SQLAREA;

SELECT * FROM V$SESSION;

SELECT * FROM V$PROCESS;

SELECT * FROM V$LOCKED_OBJECT;

SELECT * FROM ALL_OBJECTS;

SELECT * FROM V$SESSION_WAIT;

--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和执行的语句

--比上面那段多出sql_text和action

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

--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode

SELECT S.SID,

S.SERIAL#,

S.USERNAME,

S.SCHEMANAME,

S.OSUSER,

S.PROCESS,

S.MACHINE,

S.TERMINAL,

S.LOGON_TIME,

L.TYPE

FROM V$SESSION S, V$LOCK L

WHERE S.SID = L.SID

AND S.USERNAME IS NOT NULL

ORDER BY SID;

--如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待

--以下的语句可以查询到谁锁了表，而谁在等待。

--以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。

--如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN

SELECT LPAD(' ', DECODE(L.XIDUSN, 0, 3, 0)) || L.ORACLE_USERNAME USER_NAME,

O.OWNER,

O.OBJECT_NAME,

O.OBJECT_TYPE,

S.SID,

S.SERIAL#

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

WHERE L.OBJECT_ID = O.OBJECT_ID

AND L.SESSION_ID = S.SID

ORDER BY O.OBJECT_ID, XIDUSN DESC