--以下几个为相关表
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