2012. 10. 7.

DBMS/오라클]3489347_오라클_LOCK_확인_프로시저


select *
from v$session s, v$lock l, dba_objects o
where s.sid= l.sid and o.object_id = l.id1 and s.username is not null;



select a.sid, a.serial#, a.username, a.process, b.object_name,
decode(c.lmode, 2, 'RS', 3, 'RX',
4, 'S', 5, 'SRX', 8, 'X', 'NO') TABLE_LOCK,
decode(a.command, 2, 'INSERT',3, 'SELECT',6, 'UPDATE',
7, 'DELETE', 12, 'DROP',6, 'LOCK', 'unknown') SQL,
decode(a.lockwait, NULL, 'No Wait', 'Wait') STATUS
from V$SESSION a, DBA_OBJECTS b, V$LOCK c
where a.sid = c.sid and b.object_id = c.id1
and c.type = 'TM'
and a.username = 'NIOTALKUSER'
;



select substr(c.object_name,1,20),a.sid,a.serial#
from v$session a, v$lock b, dba_objects c
where a.sid = b.sid
and b.id1 = c.object_id
and b.type='TM';



SELECT SUBSTR(S.USERNAME, 1,11) "ORACLE USER", P.PID "PROCESS ID", S.SID "SESSION ID", S.SERIAL#, OSUSER "OS USER", P.SPID "PROC SPID",
    S.PROCESS "SESS SPID", S.LOCKWAIT "LOCK WAIT"
FROM V$PROCESS P, V$SESSION S, V$ACCESS A
WHERE A.SID = S.SID
AND P.ADDR = S.PADDR
AND S.USERNAME != 'SYS'

댓글 없음:

댓글 쓰기