|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
create or replace view HAISINS_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 HAISINS_holders; create table HAISINS_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 HAISINS_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 HAISINS_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 HAISINS_lock where lock_type = 'Transaction') w, HAISINS_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 HAISINS_holders select holding_session, null, 'None', null, null, null, null from HAISINS_holders minus select waiting_session, null, 'None', null, null, null, null from HAISINS_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 HAISINS_holders connect by prior waiting_session = holding_session start with holding_session is null; select * from HAISINS_holders; |