SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Blocking Locks |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
PROMPT
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | BLOCKING LOCKS (Summary) |
PROMPT +------------------------------------------------------------------------+
PROMPT
SET serveroutput ON FORMAT WRAPPED
SET feedback OFF
DECLARE
CURSOR cur_BlockingLocks IS
SELECT
iw.instance_name AS waiting_instance
, sw.status AS waiting_status
, lw.sid AS waiting_sid
, sw.serial# AS waiting_serial_num
, sw.username AS waiting_oracle_username
, sw.osuser AS waiting_os_username
, sw.machine AS waiting_machine
, pw.spid AS waiting_spid
, SUBSTR(sw.terminal,0, 39) AS waiting_terminal
, SUBSTR(sw.program,0, 39) AS waiting_program
, ROUND(lw.ctime/60) AS waiting_lock_time_min
, DECODE ( lh.type
, 'CF', 'Control File'
, 'DX', 'Distributed Transaction'
, 'FS', 'File Set'
, 'IR', 'Instance Recovery'
, 'IS', 'Instance State'
, 'IV', 'Libcache Invalidation'
, 'LS', 'Log Start or Log Switch'
, 'MR', 'Media Recovery'
, 'RT', 'Redo Thread'
, 'RW', 'Row Wait'
, 'SQ', 'Sequence Number'
, 'ST', 'Diskspace Transaction'
, 'TE', 'Extend Table'
, 'TT', 'Temp Table'
, 'TX', 'Transaction'
, 'TM', 'DML'
, 'UL', 'PLSQL User_lock'
, 'UN', 'User Name'
, 'Nothing-'
) AS waiter_lock_type
, DECODE ( lw.request
, 0, 'None' /* Mon Lock equivalent */
, 1, 'NoLock' /* N */
, 2, 'Row-Share (SS)' /* L */
, 3, 'Row-Exclusive (SX)' /* R */
, 4, 'Share-Table' /* S */
, 5, 'Share-Row-Exclusive (SSX)' /* C */
, 6, 'Exclusive' /* X */
, '[Nothing]'
) AS waiter_mode_request
, ih.instance_name AS locking_instance
, sh.status AS locking_status
, lh.sid AS locking_sid
, sh.serial# AS locking_serial_num
, sh.username AS locking_oracle_username
, sh.osuser AS locking_os_username
, sh.machine AS locking_machine
, ph.spid AS locking_spid
, SUBSTR(sh.terminal,0, 39) AS locking_terminal
, SUBSTR(sh.program,0, 39) AS locking_program
, ROUND(lh.ctime/60) AS locking_lock_time_min
, aw.sql_text AS waiting_sql_text
FROM
gv$lock lw
, gv$lock lh
, gv$instance iw
, gv$instance ih
, gv$session sw
, gv$session sh
, gv$process pw
, gv$process ph
, gv$sqlarea aw
WHERE
iw.inst_id = lw.inst_id
AND ih.inst_id = lh.inst_id
AND sw.inst_id = lw.inst_id
AND sh.inst_id = lh.inst_id
AND pw.inst_id = lw.inst_id
AND ph.inst_id = lh.inst_id
AND aw.inst_id = lw.inst_id
AND sw.sid = lw.sid
AND sh.sid = lh.sid
AND lh.id1 = lw.id1
AND lh.id2 = lw.id2
AND lh.request = 0
AND lw.lmode = 0
AND (lh.id1, lh.id2) IN ( SELECT id1,id2
FROM gv$lock
WHERE request = 0
INTERSECT
SELECT id1,id2
FROM gv$lock
WHERE lmode = 0
)
AND sw.paddr = pw.addr (+)
AND sh.paddr = ph.addr (+)
AND sw.sql_address = aw.address
ORDER BY
iw.instance_name
, lw.sid;
TYPE t_BlockingLockRecord IS RECORD (
WaitingInstanceName VARCHAR2(16)
, WaitingStatus VARCHAR2(8)
, WaitingSid NUMBER
, WaitingSerialNum NUMBER
, WaitingOracleUsername VARCHAR2(30)
, WaitingOSUsername VARCHAR2(30)
, WaitingMachine VARCHAR2(64)
, WaitingSpid VARCHAR2(12)
, WaitingTerminal VARCHAR2(30)
, WaitingProgram VARCHAR2(48)
, WaitingLockTimeMinute NUMBER
, WaiterLockType VARCHAR2(30)
, WaiterModeRequest VARCHAR2(30)
, LockingInstanceName VARCHAR2(16)
, LockingStatus VARCHAR2(8)
, LockingSid NUMBER
, LockingSerialNum NUMBER
, LockingOracleUsername VARCHAR2(30)
, LockingOSUsername VARCHAR2(30)
, LockingMachine VARCHAR2(64)
, LockingSpid VARCHAR2(12)
, LockingTerminal VARCHAR2(30)
, LockingProgram VARCHAR2(48)
, LockingLockTimeMinute NUMBER
, SQLText VARCHAR2(1000)
);
TYPE t_BlockingLockRecordTable IS TABLE OF t_BlockingLockRecord INDEX BY BINARY_INTEGER;
v_BlockingLockArray t_BlockingLockRecordTable;
v_BlockingLockRec cur_BlockingLocks%ROWTYPE;
v_NumBlockingLocksIncidents BINARY_INTEGER := 0;
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
OPEN cur_BlockingLocks;
LOOP
FETCH cur_BlockingLocks INTO v_BlockingLockRec;
EXIT WHEN cur_BlockingLocks%NOTFOUND;
v_NumBlockingLocksIncidents := v_NumBlockingLocksIncidents + 1;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingInstanceName := v_BlockingLockRec.waiting_instance;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingStatus := v_BlockingLockRec.waiting_status;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingSid := v_BlockingLockRec.waiting_sid;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingSerialNum := v_BlockingLockRec.waiting_serial_num;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingOracleUsername := v_BlockingLockRec.waiting_oracle_username;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingOSUsername := v_BlockingLockRec.waiting_os_username;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingMachine := v_BlockingLockRec.waiting_machine;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingSpid := v_BlockingLockRec.waiting_spid;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingTerminal := v_BlockingLockRec.waiting_terminal;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingProgram := v_BlockingLockRec.waiting_program;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaitingLockTimeMinute := v_BlockingLockRec.waiting_lock_time_min;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaiterLockType := v_BlockingLockRec.waiter_lock_type;
v_BlockingLockArray(v_NumBlockingLocksIncidents).WaiterModeRequest := v_BlockingLockRec.waiter_mode_request;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingInstanceName := v_BlockingLockRec.locking_instance;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingStatus := v_BlockingLockRec.locking_status;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingSid := v_BlockingLockRec.locking_sid;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingSerialNum := v_BlockingLockRec.locking_serial_num;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingOracleUsername := v_BlockingLockRec.locking_oracle_username;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingOSUsername := v_BlockingLockRec.locking_os_username;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingMachine := v_BlockingLockRec.locking_machine;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingSpid := v_BlockingLockRec.locking_spid;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingTerminal := v_BlockingLockRec.locking_terminal;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingProgram := v_BlockingLockRec.locking_program;
v_BlockingLockArray(v_NumBlockingLocksIncidents).LockingLockTimeMinute := v_BlockingLockRec.locking_lock_time_min;
v_BlockingLockArray(v_NumBlockingLocksIncidents).SQLText := v_BlockingLockRec.waiting_sql_text;
END LOOP;
CLOSE cur_BlockingLocks;
DBMS_OUTPUT.PUT_LINE('Number of blocking lock incidents: ' || v_BlockingLockArray.COUNT);
DBMS_OUTPUT.PUT(chr(10));
FOR RowIndex IN 1 .. v_BlockingLockArray.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE('Incident ' || RowIndex);
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(' WAITING BLOCKING');
DBMS_OUTPUT.PUT_LINE(' ---------------------------------------- ----------------------------------------');
DBMS_OUTPUT.PUT_LINE('Instance Name : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingInstanceName, 41) || v_BlockingLockArray(RowIndex).LockingInstanceName);
DBMS_OUTPUT.PUT_LINE('Oracle SID : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingSid, 41) || v_BlockingLockArray(RowIndex).LockingSid);
DBMS_OUTPUT.PUT_LINE('Serial# : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingSerialNum, 41) || v_BlockingLockArray(RowIndex).LockingSerialNum);
DBMS_OUTPUT.PUT_LINE('Oracle User : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingOracleUsername, 41) || v_BlockingLockArray(RowIndex).LockingOracleUsername);
DBMS_OUTPUT.PUT_LINE('O/S User : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingOSUsername, 41) || v_BlockingLockArray(RowIndex).LockingOSUsername);
DBMS_OUTPUT.PUT_LINE('Machine : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingMachine, 41) || v_BlockingLockArray(RowIndex).LockingMachine);
DBMS_OUTPUT.PUT_LINE('O/S PID : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingSpid, 41) || v_BlockingLockArray(RowIndex).LockingSpid);
DBMS_OUTPUT.PUT_LINE('Terminal : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingTerminal, 41) || v_BlockingLockArray(RowIndex).LockingTerminal);
DBMS_OUTPUT.PUT_LINE('Lock Time : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingLockTimeMinute || ' minutes', 41) || v_BlockingLockArray(RowIndex).LockingLockTimeMinute ||' minutes');
DBMS_OUTPUT.PUT_LINE('Status : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingStatus, 41) || v_BlockingLockArray(RowIndex).LockingStatus);
DBMS_OUTPUT.PUT_LINE('Program : ' || RPAD(v_BlockingLockArray(RowIndex).WaitingProgram, 41) || v_BlockingLockArray(RowIndex).LockingProgram);
DBMS_OUTPUT.PUT_LINE('Waiter Lock Type : ' || v_BlockingLockArray(RowIndex).WaiterLockType);
DBMS_OUTPUT.PUT_LINE('Waiter Mode Request : ' || v_BlockingLockArray(RowIndex).WaiterModeRequest);
DBMS_OUTPUT.PUT_LINE('Waiting SQL : ' || v_BlockingLockArray(RowIndex).SQLText);
DBMS_OUTPUT.PUT(chr(10));
END LOOP;
END;
/
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 256
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN instance_name FORMAT a9 HEADING 'Instance'
COLUMN sid FORMAT 999999 HEADING 'SID'
COLUMN sid_serial FORMAT a15 HEADING 'SID / Serial#'
COLUMN session_status FORMAT a9 HEADING 'Status'
COLUMN locking_oracle_user FORMAT a20 HEADING 'Locking Oracle User'
COLUMN object_owner FORMAT a15 HEADING 'Object Owner'
COLUMN object_name FORMAT a25 HEADING 'Object Name'
COLUMN object_type FORMAT a15 HEADING 'Object Type'
COLUMN locked_mode HEADING 'Locked Mode'
CLEAR BREAKS
PROMPT
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | LOCKED OBJECTS |
PROMPT +------------------------------------------------------------------------+
SELECT
i.instance_name instance_name
, l.session_id || ' / ' || s.serial# sid_serial
, s.status session_status
, l.oracle_username locking_oracle_user
, o.owner object_owner
, o.object_name object_name
, o.object_type object_type
, DECODE ( l.locked_mode
, 0, 'None' /* Mon Lock equivalent */
, 1, 'NoLock' /* N */
, 2, 'Row-Share (SS)' /* L */
, 3, 'Row-Exclusive (SX)' /* R */
, 4, 'Share-Table' /* S */
, 5, 'Share-Row-Exclusive (SSX)' /* C */
, 6, 'Exclusive' /* X */
, '[Nothing]'
) locked_mode
FROM
dba_objects o
, gv$session s
, gv$locked_object l
, gv$instance i
WHERE
i.inst_id = l.inst_id
AND s.inst_id = l.inst_id
AND s.sid = l.session_id
AND o.object_id = l.object_id
ORDER BY
i.instance_name
, l.session_id;
Nenhum comentário:
Postar um comentário