---------------------
--락 걸린 객체 조회--
---------------------
col object_name format a30
col subobject_name format a10
col oracle_username format a20
select a.object_name, a.subobject_name, b.process, b.session_id, b.oracle_username, b.locked_mode
from dba_objects a, v$locked_object b
where a.object_id = b.object_id;

------------------------
--블록킹 세션 모니터링--
------------------------

column username for a10
column sid for 999
column lock_type for a25
column mode_held for a11
column mod_requested for a10
column lock_id1 for a8
column lock_id2 format a8
select a.sid,
decode(a.type, 'MR', 'Media Recovery',
               'RT', 'Redo Thread',
               'UN', 'User Name',
               'TX', 'Transaction',
               'TM', 'DML',
               'UL', 'PL/SQL User Lock',
               'DX', 'Distributed Xaction',
               'CF', 'Control File',
               'IS', 'Instance State',
               'FS', 'File Set',
               'IR', 'Instance Recovery',
               'ST', 'Disk Space Transaction',
               'TS', 'Temp Segment',
               'IV', 'Library Cache Invaildation',
               'LS', 'Log Start or Switch',
               'RW', 'Row Wait',
               'SQ', 'Sequence Number',
               'TE', 'Extend Table',
               'TT', 'Temp Table', a.type) lock_type,
decode(a.lmode,
				0, 'None', 			/*Non Lock equivalent*/
				1, 'Null', 			/*N*/
				2, 'Row-S(SS)', 	/*L*/
				3, 'Row-X(SX)', 	/*R*/
				4, 'Share', 		/*S*/
				5, 'S/Row-X(SSX)',/*C*/
				6, 'Exclusive',	/*X*/
				to_char(a.lmode)) mode_held,
decode(a.request,
				0, 'None', 			/*Non Lock equivalent*/
				1, 'Null', 			/*N*/
				2, 'Row-S(SS)', 	/*L*/
				3, 'Row-X(SX)', 	/*R*/
				4, 'Share', 		/*S*/
				5, 'S/Row-X(SSX)',/*C*/
				6, 'Exclusive',	/*X*/
				to_char(a.request)) mode_requested,
				to_char(a.id1) lock_id1,
				to_char(a.id2) lock_id2				             
from v$lock a
where a.type not in ('MR', 'DM', 'RT')
and (id1, id2) in (select b.id1, b.id2
                   from v$lock b 
                   where b.id1 = a.id1
                   and b.id2=a.id2
                   and b.request > 0)
order by 5,6;

-------------------------
--락으로 인한 wait 조회--
-------------------------
select s.sid, s.username, s.machine, 
decode(s.status, 'INACTIVE', '락을 걸고 있는 세션(INACTIVE)', s.status) status, e.event
from v$session s, v$session_wait e
where s.sid = e.sid