iason 업데이트 하다가 update문에 where절 안줘서 DB lock을 엄청나게 양상해 버렸다 -_- 완전 캐 난감..
어렵지만 유용하기에 lock 체크하는 쿼리를 적어둡니다.
select
/*+ RULE */
b.sid,b.serial#,b.username,b.osuser,
c.owner||'.'||c.object_name "Object",
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',
'CU', 'Cursor Bind',
'TT', 'Temp Table', a.type) "Lock Type",
DECODE(a.lmode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR(a.lmode)) "Mode Held",
DECODE(a.request,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
to_char(a.request)) "Mode Requested",
b.machine,b.program, d.sql_text
from v$lock a,v$session b,dba_objects c, v$sql d
where a.lmode > 1
and b.username is not null
and a.sid = b.sid
and c.object_id (+)= a.id1
and b.sql_address = d.address
and b.sql_hash_value = d.hash_value
order by sid,"Object"
