Blockers and Waiters with RowID
--
-- Blockers and Waiters with RowID
--
-- Shows blocked session, blocking session, RowIDs on the Object being Waited on and details of the programs and machines they are running from.
-- In addition, shows the CTIME value which details how long the blocking session has had hold of the lock
--
-- Best run from SQL Developer where the output is formatted.  Running from SQLPLUS makes it hard to read
-- due to the verbose nature of the query.
--
 
SELECT 
  gvw.inst_id Waiter_Inst,
  gvw.sid Waiter_Sid,
  gvs_w.osuser waiter_osuser,
  gvs_w.program waiter_program,
  gvs_w.machine waiter_machine,
  gvs_w.client_identifier waiter_identifer,
  gvs_w.client_info waiter_thread,
  gvs_w.seconds_in_wait waiter_secs_in_wait,
  gvs_w.sql_id waiter_sql,
  dbms_rowid.rowid_create(
     1,
     gvs_w.ROW_WAIT_OBJ#,
     gvs_w.ROW_WAIT_FILE#,
     gvs_w.ROW_WAIT_BLOCK#,
     gvs_w.ROW_WAIT_ROW#
     ) waiter_rowid_Waiting_on, 
  gvs_w.event waiter_event, 
  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,
  decode(gvh.type,
             'AE', 'Edition Enqueue',
             'AT', 'Lock held for the ALTER TABLE statement',
             'BL', 'Buffer hash table instance',
             'CF', 'Control file schema global enqueue',
             'CI', 'Cross-instance function invocation instance',
             'CU', 'Cursor bind',
             'DF', 'datafile instance',
             'DL', 'Direct loader parallel index create',
             'DM', 'Mount/startup db primary/secondary instance',
             'DR', 'Distributed recovery process',
             'DX', 'Distrted_Transaxion',
             'FS', 'File set',
             'HW', 'Space management operations on a specific segment',
             'IN', 'Instance number',
             'IR', 'Instance recovery serialization global enqueue',
             'IS', 'Instance state',
             'IV', 'Library cache invalidation instance',
             'JQ', 'Job queue',
             'KK', 'Thread kick',
             'MM', 'Mount definition global enqueue',
             'MR', 'Media_recovery',
             'PF', 'Password File',
             'PI', 'Parallel Operation',
             'PS', 'Parallel Operation',
             'PR', 'Process startup',
             'RT', 'Redo thread global enqueue',
             'SC', 'System change number instance',
             'SM', 'SMON',
             'SN', 'Sequence number instance',
             'SQ', 'Sequence number enqueue',
             'SS', 'Sort segment',
             'ST', 'Space transaction enqueue',
             'SV', 'Sequence number value',
             'TA', 'Generic enqueue',
             'TS', 'Temporary segment enqueue (ID2=0) or New block allocation enqueue (ID2=1)',
             'TT', 'Temporary table enqueue',
             'UN', 'User name',
             'US', 'Undo segment DDL',
             'WL', 'Being-written redo log instance',
             'TX', 'Transaction (Left for backwards compatability)',
             'TM', 'Dml (Left for backwards compatability)',
             'UL', 'PLSQL User_lock (Left for backwards compatability)',
             'LS', 'LogStaartORswitch (Left for backwards compatability)',
             'RW', 'Row_wait (Left for backwards compatability)',
             'TE', 'Extend_table (Left for backwards compatability)',
             'Nothing or Library cache lock instance lock (LA..LP) or Library cache pin instance (NA..NZ)') Waiter_Lock_Type,
  gvh.inst_id Locker_Inst, 
  gvh.sid Locker_Sid, 
  gvs.osuser locker_osuser, 
  gvs.machine locker_machine, 
  gvs.program locker_program,
  gvs.client_identifier locker_identifer,
  gvs.client_info locker_thread,
  gvs.seconds_in_wait locker_secs_in_wait, 
  gvs.serial# Locker_Serial,
  gvs.event locker_event,
  gvs.sql_id locker_sql,
  gvs.prev_sql_id locker_prev_sql,
  gvs.status locker_Status, 
  gvs.module locker_Module,
  gvs_w.row_wait_obj# object_locked,
  gvh.ctime secs_object_locked
FROM gv$lock gvh, 
     gv$lock gvw, 
     gv$session gvs,
     gv$session gvs_w 
WHERE (gvh.id1, gvh.id2) 
IN (SELECT 
      id1, 
      id2 
    FROM gv$lock 
    WHERE request=0
    INTERSECT
    SELECT 
      id1, 
      id2 
    FROM 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 gvw.sid=gvs_w.sid
AND gvh.inst_id=gvs.inst_id
AND gvw.inst_id=gvs_w.inst_id
AND gvs_w.sql_id is not null
/

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License