Gvlock (gvlock.sql)

What This Script Does

This script provides detailed information about database locks, including the objects being locked, lock modes, and session details. Essential for troubleshooting lock contention and blocked sessions.

The Script

set linesize 300
set pagesize 300

col oracle_username format a15 heading 'ORACLE|USERNAME'
col object_owner    format a15
col object_name     format a20
col object_type     format a15
col osuser          format a8 heading 'OS|USERNAME'
col sid_ser         format a12
col status          format a12
col mode_held       format a15
col obj_file_block_row format a30
col module    format a25  heading 'MODULE'
col machine format a15 heading 'MACHINE'

SELECT
       v.oracle_username USERNAME
     , decode(s.machine,null,s.terminal, s.machine) machine
     , s.osuser
     , s.sid||','||s.serial# sid_ser
     , decode(s.module,null,s.program,s.module) module
     , s.SQL_ID
     , d.owner           OBJECT_OWNER
     , d.object_name
     , d.object_type
     , l.type
     , l.ctime
--     , v.process
     , DECODE(l.block,0, 'Not Blocking'
                     ,1, 'Blocking'
                     ,2, 'Global'
              ) STATUS
     , DECODE(v.locked_mode,0, 'None'
                           ,1, 'Null'
                           ,2, 'Row-S (SS)'
                           ,3, 'Row-X (SX)'
                           ,4, 'Share'
                           ,5, 'S/Row-X (SSX)'
                           ,6, 'Exclusive'
                           , TO_CHAR(lmode)
              ) MODE_HELD
     , s.row_wait_obj#||' - '||s.row_wait_file#||' - '|| s.row_wait_block#||' - '||s.row_wait_row# obj_file_block_row
  FROM gv$lock l
     , gv$locked_object v
     , gv$session s
     , dba_objects d
 WHERE l.sid        = v.session_id
   AND l.inst_id    = v.inst_id
   AND v.object_id  = d.object_id
   AND v.inst_id    = s.inst_id
   AND v.session_id = s.sid
   and nvl(v.inst_id, 0) like nvl('&inst_id', '%')
   and nvl(v.oracle_username,'null') like nvl(upper('&user'),'%')
   and nvl(d.object_name,'null') like nvl(upper('&object_name'),'%')
--   AND s.ROW_WAIT_OBJ# = d.OBJECT_ID
--   AND v.object_id  = l.id1
ORDER BY v.oracle_username
       , v.session_id
/

--     , dbms_rowid.rowid_create( 1, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW# ) ora_rowid

Parameters

The script prompts for:

&RAC - instance ID to filter (use % for all)
&Oracle - username to filter (use % for all)
&Object - name pattern to filter (use % for all)

Usage

-- Basic usage
@gvlock.sql

-- When prompted, enter:
-- inst_id: RAC instance ID to filter (use % for all)
-- user: Oracle username to filter (use % for all)
-- object_name: Object name pattern to filter (use % for all)

Sample Output

ORACLE          OS
USERNAME        MACHINE         USERNAME SID_SER      MODULE                    SQL_ID        OBJECT_OWNER    OBJECT_NAME          OBJECT_TYPE     TYPE      CTIME STATUS       MODE_HELD       OBJ_FILE_BLOCK_ROW
--------------- --------------- -------- ------------ ------------------------- ------------- --------------- -------------------- --------------- ---- ---------- ------------ --------------- ------------------------------
HR              workstation01   jsmith   234,5678     SQL Developer             4yu2d9fkxqp6g HR              EMPLOYEES            TABLE           TM         125 Not Blocking Row-X (SX)      73847 - 7 - 1234 - 0
HR              workstation01   jsmith   234,5678     SQL Developer             4yu2d9fkxqp6g HR              EMPLOYEES_PK         INDEX           TX          45 Blocking     Exclusive       0 - 0 - 0 - 0
HR              appserver02     appuser  345,6789     JDBC Thin Client          8abc3def5hij2 HR              DEPARTMENTS          TABLE           TM          10 Not Blocking Row-S (SS)      73850 - 7 - 5678 - 0
SCOTT           workstation03   mjones   456,7890     SQL*Plus                  2xyz9mno7pqr3 SCOTT           EMP                  TABLE           TM         300 Not Blocking S/Row-X (SSX)   84592 - 8 - 9012 - 0