[DB]ORACLE監控鎖表（鎖表與解表）

ORACLE監控鎖表（鎖表與解表）1、查詢已經鎖住的表
SELECT S.USERNAME,<br /> DECODE(L.TYPE, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL,<br /> O.OWNER,<br /> O.OBJECT_NAME,<br /> O.OBJECT_TYPE,<br /> S.SID,<br /> S.SERIAL#,<br /> S.TERMINAL,<br /> S.MACHINE,<br /> S.PROGRAM,<br /> S.OSUSER<br /> FROM V$SESSION S, V$LOCK L, DBA_OBJECTS O<br /> WHERE L.SID = S.SID<br /> AND L.ID1 = O.OBJECT_ID(+)<br /> AND S.USERNAME IS NOT NULL2、解表--kill session語句<br />--'151,3027'格式：'SID,SERIAL#'<br />ALTER SYSTEM KILL SESSION '151,3032';

3、相關表

--以下幾個為相關表<br />SELECT * FROM V$LOCK;<br />SELECT * FROM V$SQLAREA;<br />SELECT * FROM V$SESSION;<br />SELECT * FROM V$PROCESS;<br />SELECT * FROM V$LOCKED_OBJECT;<br />SELECT * FROM ALL_OBJECTS;<br />SELECT * FROM V$SESSION_WAIT;<br />--1.查出鎖定object的session的資訊以及被鎖定的object名<br />SELECT L.SESSION_ID SID,<br /> S.SERIAL#,<br /> L.LOCKED_MODE,<br /> L.ORACLE_USERNAME,<br /> L.OS_USER_NAME,<br /> S.MACHINE,<br /> S.TERMINAL,<br /> O.OBJECT_NAME,<br /> S.LOGON_TIME<br /> FROM V$LOCKED_OBJECT L, ALL_OBJECTS O, V$SESSION S<br /> WHERE L.OBJECT_ID = O.OBJECT_ID<br /> AND L.SESSION_ID = S.SID<br /> ORDER BY SID, S.SERIAL#;<br />--2.查出鎖定表的session的sid, serial#,os_user_name, machine name, terminal和執行的語句<br />--比上面那段多出sql_text和action<br />SELECT L.SESSION_ID SID,<br /> S.SERIAL#,<br /> L.LOCKED_MODE,<br /> L.ORACLE_USERNAME,<br /> S.USER#,<br /> L.OS_USER_NAME,<br /> S.MACHINE,<br /> S.TERMINAL,<br /> A.SQL_TEXT,<br /> A.ACTION<br /> FROM V$SQLAREA A, V$SESSION S, V$LOCKED_OBJECT L<br /> WHERE L.SESSION_ID = S.SID<br /> AND S.PREV_SQL_ADDR = A.ADDRESS<br /> ORDER BY SID, S.SERIAL#;<br />--3.查出鎖定表的sid, serial#,os_user_name, machine_name, terminal，鎖的type,mode<br />SELECT S.SID,<br /> S.SERIAL#,<br /> S.USERNAME,<br /> S.SCHEMANAME,<br /> S.OSUSER,<br /> S.PROCESS,<br /> S.MACHINE,<br /> S.TERMINAL,<br /> S.LOGON_TIME,<br /> L.TYPE<br /> FROM V$SESSION S, V$LOCK L<br /> WHERE S.SID = L.SID<br /> AND S.USERNAME IS NOT NULL<br /> ORDER BY SID;<br />--如果發生了鎖等待，我們可能更想知道是誰鎖了表而引起誰的等待<br />--以下的語句可以查詢到誰鎖了表，而誰在等待。<br />--以上查詢結果是一個樹狀結構，如果有子節點，則表示有等待發生。<br />--如果想知道鎖用了哪個復原段，還可以關聯到V$rollname，其中xidusn就是復原段的USN<br />SELECT LPAD(' ', DECODE(L.XIDUSN, 0, 3, 0)) || L.ORACLE_USERNAME USER_NAME,<br /> O.OWNER,<br /> O.OBJECT_NAME,<br /> O.OBJECT_TYPE,<br /> S.SID,<br /> S.SERIAL#<br /> FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S<br /> WHERE L.OBJECT_ID = O.OBJECT_ID<br /> AND L.SESSION_ID = S.SID<br /> ORDER BY O.OBJECT_ID, XIDUSN DESC