Lock 걸린 Table 알아내기

SELECT C.OBJECT_NAME,
       B.LOGON_TIME,

       A.SESSION_ID SID,

       A.PROCESS Parent_PID,

       A.ORACLE_USERNAME,

       A.OS_USER_NAME,

       A.LOCKED_MODE LK,

       B.SQL_HASH_VALUE HASH_VALUE,

       B.MACHINE,

       B.USERNAME,

       B.TERMINAL,

       B.PROGRAM
FROM SYS.V_$LOCKED_OBJECT A,

       SYS.ALL_OBJECTS C,

       SYS.V_$SESSION B
WHERE A.OBJECT_ID = C.OBJECT_ID
AND A.SESSION_ID = B.SID
ORDER BY OBJECT_NAME, SID;

 

Lock이 걸릴때 Lock을 유발하는 쿼리문 찾는 쿼리문

select b.username username,b.osuser,b.machine, c.sid sid, c.owner object_owner,
c.object object, b.lockwait, a.sql_text SQL
from v$sqltext a, v$session b, v$access c
where a.address=b.sql_address and
a.hash_value=b.sql_hash_value and
b.sid = c.sid and c.owner != 'SYS';

 

 

출처 : http://blog.naver.com/pluggers?Redirect=Log&logNo=150014343586