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 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 waiting_instance_sid_serial FORMAT a24 HEADING '[WAITING]|Instance - SID / Serial#'
COLUMN waiting_oracle_username FORMAT a20 HEADING '[WAITING]|Oracle User'
COLUMN waiting_pid FORMAT a11 HEADING '[WAITING]|PID'
COLUMN waiting_machine FORMAT a15 HEADING '[WAITING]|Machine' TRUNC
COLUMN waiting_os_username FORMAT a15 HEADING '[WAITING]|O/S User'
COLUMN waiter_lock_type_mode_req FORMAT a35 HEADING 'Waiter Lock Type / Mode Requested'
COLUMN waiting_lock_time_min FORMAT a10 HEADING '[WAITING]|Lock Time'
COLUMN waiting_instance_sid FORMAT a15 HEADING '[WAITING]|Instance - SID'
COLUMN waiting_sql_text FORMAT a105 HEADING '[WAITING]|SQL Text' WRAP
COLUMN locking_instance_sid_serial FORMAT a24 HEADING '[LOCKING]|Instance - SID / Serial#'
COLUMN locking_oracle_username FORMAT a20 HEADING '[LOCKING]|Oracle User'
COLUMN locking_pid FORMAT a11 HEADING '[LOCKING]|PID'
COLUMN locking_machine FORMAT a15 HEADING '[LOCKING]|Machine' TRUNC
COLUMN locking_os_username FORMAT a15 HEADING '[LOCKING]|O/S User'
COLUMN locking_lock_time_min FORMAT a10 HEADING '[LOCKING]|Lock Time'
COLUMN instance_name FORMAT a8 HEADING 'Instance'
COLUMN sid FORMAT 999999 HEADING 'SID'
COLUMN session_status FORMAT a9 HEADING 'Status'
COLUMN locking_oracle_user FORMAT a20 HEADING 'Locking Oracle User'
COLUMN locking_os_user FORMAT a20 HEADING 'Locking O/S User'
COLUMN locking_os_pid FORMAT a11 HEADING 'Locking PID'
COLUMN locking_machine FORMAT a15 HEADING 'Locking Machine' TRUNC
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 | BLOCKING LOCKS (Summary) |
PROMPT +------------------------------------------------------------------------+
SELECT
iw.instance_name || ' - ' || lw.sid || ' / ' || sw.serial# waiting_instance_sid_serial
, sw.username waiting_oracle_username
, ROUND(lw.ctime/60) || ' min.' 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-'
) || ' / ' ||
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]'
) waiter_lock_type_mode_req
, ih.instance_name || ' - ' || lh.sid || ' / ' || sh.serial# locking_instance_sid_serial
, sh.username locking_oracle_username
, ROUND(lh.ctime/60) || ' min.' locking_lock_time_min
FROM
gv$lock lw
, gv$lock lh
, gv$instance iw
, gv$instance ih
, gv$session sw
, gv$session sh
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 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
)
ORDER BY
iw.instance_name
, lw.sid;
PROMPT
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | BLOCKING LOCKS (User Details) |
PROMPT +------------------------------------------------------------------------+
SELECT
iw.instance_name || ' - ' || lw.sid || ' / ' || sw.serial# waiting_instance_sid_serial
, sw.username waiting_oracle_username
, sw.osuser waiting_os_username
, sw.machine waiting_machine
, pw.spid waiting_pid
, ih.instance_name || ' - ' || lh.sid || ' / ' || sh.serial# locking_instance_sid_serial
, sh.username locking_oracle_username
, sh.osuser locking_os_username
, sh.machine locking_machine
, ph.spid locking_pid
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
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 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 (+)
ORDER BY
iw.instance_name
, lw.sid;
PROMPT
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | BLOCKING LOCKS (Waiting SQL) |
PROMPT +------------------------------------------------------------------------+
SELECT
iw.instance_name || ' - ' || lw.sid || ' / ' || sw.serial# waiting_instance_sid_serial
, aw.sql_text waiting_sql_text
FROM
gv$lock lw
, gv$lock lh
, gv$instance iw
, gv$instance ih
, gv$session sw
, gv$session sh
, 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 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.sql_address = aw.address
ORDER BY
iw.instance_name
, lw.sid;
PROMPT
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | LOCKED OBJECTS |
PROMPT +------------------------------------------------------------------------+
SELECT
i.instance_name instance_name
, l.session_id sid
, s.status session_status
, l.oracle_username locking_oracle_user
, s.osuser locking_os_user
, s.machine locking_machine
, p.spid locking_os_pid
, 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$process p
, gv$locked_object l
, gv$instance i
WHERE
i.inst_id = l.inst_id
AND s.inst_id = l.inst_id
AND s.inst_id = p.inst_id
AND s.sid = l.session_id
AND o.object_id = l.object_id
AND s.paddr = p.addr
ORDER BY
i.instance_name
, l.session_id;
Nenhum comentário:
Postar um comentário