SET SERVEROUTPUT ON
SET LINES 200
SET PAGES 2000
DECLARE
CURSOR C_BLOQUEIOS IS
SELECT
gvh.inst_id Locking_Inst,
gvh.sid Locking_Sid,
gvs.serial# Locking_Serial,
gvp.spid os_process,
gvs.osuser,
gvs.process P_Locking,
gvs.status Status,
gvs.module Module,
gvw.inst_id Waiting_Inst,
gvw.sid Waiter_Sid,
gvsw.audsid,
gvsw.osuser WAITER_USER,
gvsw.process P_Waiter,
decode(gvh.type, 'MR', 'Media_recovery',
'RT', 'Redo_thread',
'UN', 'User_name',
'TX', 'Transaction',
'TM', 'Dml',
'UL', 'PLSQL User_lock',
'DX', 'Distrted_Transaxion',
'CF', 'Control_file',
'IS', 'Instance_state',
'FS', 'File_set',
'IR', 'Instance_recovery',
'ST', 'Diskspace Transaction',
'IV', 'Libcache_invalidation',
'LS', 'LogStaartORswitch',
'RW', 'Row_wait',
'SQ', 'Sequence_no',
'TE', 'Extend_table',
'TT', 'Temp_table',
'Nothing-') Waiter_Lock_Type,
decode(gvw.request, 0, 'None',
1, 'NoLock',
2, 'Row-Share',
3, 'Row-Exclusive',
4, 'Share-Table',
5, 'Share-Row-Exclusive',
6, 'Exclusive',
'Nothing-') Waiter_Mode_Req ,
'alter system kill session '|| ”” || gvh.sid || ',' || gvs.serial# || ”';' Kill_Command,
gvsw.sid Locked_Sid,
gvsw.serial# Locked_Serial,
gvsw.inst_id Locked_Inst_Id
FROM sys.gv_$lock gvh,
sys.gv_$lock gvw,
sys.gv_$session gvs,
sys.gv_$process gvp,
sys.gv_$session gvsw
WHERE (gvh.id1, gvh.id2) in (
SELECT id1, id2 FROM sys.gv_$lock WHERE request=0
INTERSECT
SELECT id1, id2 FROM sys.gv_$lock WHERE lmode=0)
AND gvh.id1=gvw.id1
AND gvh.id2=gvw.id2
AND gvh.request=0
AND gvw.lmode=0
AND gvh.sid=gvs.sid
AND gvh.inst_id=gvs.inst_id
AND gvs.paddr = gvp.addr(+)
AND gvs.inst_id = gvp.inst_id
AND gvw.inst_id = gvsw.inst_id
AND gvw.sid = gvsw.sid;
C1 C_BLOQUEIOS%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE(' INICIO DO PROGRAMA ');
DBMS_OUTPUT.PUT_LINE('|— LOCKING —-| |———– WAITING ———–|');
DBMS_OUTPUT.PUT_LINE('[SID]-[SERIAL]-[I]-[Lock_Type]-[SID]-[SERIAL]-[I]-[Kill_Command]');
DBMS_OUTPUT.PUT_LINE('|————————————————————–|');
OPEN C_BLOQUEIOS;
LOOP
FETCH C_BLOQUEIOS INTO C1;
EXIT WHEN C_BLOQUEIOS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('['||c1.Locking_Sid||']-['||c1.Locking_Serial||']-['||c1.Locking_Inst||']-['||c1.Waiter_Lock_Type||']-['||c1.Locked_Sid||']-['||c1.Locked_Serial||']-['||c1.Locked_Inst_Id||']-['||c1.Kill_Command||']');
END LOOP;
CLOSE C_BLOQUEIOS;
DBMS_OUTPUT.PUT_LINE(' FIM DO PROGRAMA ');
END;
/
SQL