DB 전체의 TX, TM LOCK 현상 확인 SCRIPT
=====================================

database 전체에 대해서 user table에 관계된 lock만을 확인하는 script를 기존
catblock.sql을 변형하여 작성하였다.

기존의 catblock.sql이 해당 lock을 waiting하는 session이 있을 때에만 결과가
나타나, 실제 waiting이 없는 경우 현재 lock 상태를 보지 못하였던 점과,
각 lock 상태에 대해서 바로 해당 object를 함께 display하도록 수정하였다.

[NOTE 1] sys user에서 수행하여야 하며 system 등 다른 dba 권한을 가진
user에서 수행하고자 한다면 다음과 같이 sys user에서 grant를 주
어야 한다.

SQL>grant select on v_$lock to system;

[NOTE 2] 전체 lock현황이 아니고 waiting이 발생하는 경우만을 보여주고자
한다면 첫번째 문장의 where절에 request !=0 를 첨가한다.
다음과 같이 된다.

where request !=0
and type = 'TX'
or type = 'TM';

----------------------------------------------------------------------

create or replace view EYKIM_LOCK as
select
sid session_id,
decode(type,
'TX', 'Transaction',
'TM', 'DML',
'DX', 'Distributed Xaction',
type) lock_type,
decode(lmode,
0, 'None', /* Mon 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(lmode)) mode_held,
decode(request,
0, 'None', /* Mon 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(request)) mode_requested,
to_char(id1) lock_id1, to_char(id2) lock_id2,
decode(block,
0, 'Not Blocking', /* Not blocking any other processes */
1, 'Blocking', /* This lock blocks other processes */
2, 'Global', /* This lock is global, so we can't tell */
to_char(block)) blocking_others
from v$lock
where type = 'TX'
or type = 'TM';


drop table eykim_holders;

create table EYKIM_HOLDERS /* temporary table */
(
waiting_session number,
holding_session number,
lock_type varchar2(26),
mode_held varchar2(14),
mode_requested varchar2(14),
lock_table varchar2(22),
lock_owner varchar2(30)
);


insert into eykim_holders
select w.session_id,
h.session_id,
w.lock_type,
nvl(h.mode_held, w.mode_held),
w.mode_requested,
o.object_name,
o.owner
from (select session_id, lock_type, mode_held, lock_id1, lock_id2
from eykim_lock
where blocking_others = 'Blocking'
and mode_held != 'None'
and mode_held != 'Null' ) h,
(select session_id, lock_type, mode_held, mode_requested,
lock_id1, lock_id2
from eykim_lock
where lock_type = 'Transaction') w,
eykim_lock a, dba_objects o
where w.lock_type = h.lock_type(+)
and w.lock_id1 = h.lock_id1(+)
and w.lock_id2 = h.lock_id2(+)
and w.session_id = a.session_id
and a.lock_type = 'DML'
and a.lock_id1 = o.object_id;
commit;

insert into eykim_holders
select holding_session, null, 'None', null, null, null, null
from eykim_holders
minus
select waiting_session, null, 'None', null, null, null, null
from eykim_holders;
commit;

col lock_type format a12
col lock_owner format a8
col lock_table format a10

select lpad(' ',3*(level-1)) || waiting_session waiting_session,
lock_type,
mode_requested,
mode_held,
lock_table,
lock_owner
from eykim_holders
connect by prior waiting_session = holding_session
start with holding_session is null;

select * from eykim_holders;