Gvqtrans (gvqtrans.sql)

What This Script Does

This script monitors transaction enqueue locks, showing which sessions hold transaction-level locks and for how long. Essential for identifying long-running transactions and potential deadlock situations.

The Script

rem  gvtranenq.sql
rem
set linesize 200
rem
ttitle 'GV Transaction Enqueue View'
rem
clear break
rem

col INST_ID        format 9999  heading 'INST|ID'
col osuser         format a8    heading 'OS|USERNAME'
col username       format a15   heading 'ORACLE|USERNAME'
col ADDR           format a20
col KADDR          format a20
col SID            format 99999 heading 'SID'
col TYPE           format a4
col ID1            format 99999999
col ID2            format 99999999
col LMODE          format 99999
col REQUEST        format 999999
col CTIME          format 999,999,999
col BLOCK          format 999

select  te.INST_ID
       ,te.SID
       ,s.osuser
       ,s.username
       ,te.ADDR
       ,te.KADDR
       ,te.TYPE
       ,te.ID1
       ,te.ID2
       ,te.LMODE
       ,te.REQUEST
       ,te.CTIME
       ,te.BLOCK
  from gv$transaction_enqueue te
      ,gv$session s
 where nvl(upper(te.inst_id),'null') like upper(nvl('&inst_id', '%'))
   and nvl(upper(te.sid),'null') like upper(nvl('&sid', '%'))
   and te.sid     = s.sid
   and te.inst_id = s.inst_id
 order by te.ctime desc
;
rem
rem set linesize 80

Parameters

The script prompts for:

&RAC - instance ID to filter (use % for all)
&Session - ID to filter (use % for all)

Usage

-- Basic usage
@gvqtrans.sql

-- When prompted, enter:
-- inst_id: RAC instance ID to filter (use % for all)
-- sid: Session ID to filter (use % for all)

Sample Output

GV Transaction Enqueue View

INST                OS       ORACLE
  ID    SID USERNAME USERNAME        ADDR                 KADDR                TYPE      ID1      ID2  LMODE REQUEST        CTIME BLOCK
---- ------ -------- --------------- -------------------- -------------------- ---- -------- -------- ------ ------- ------------ -----
   1    234 jsmith   HR              00000000600B8E98     00000000600C1A20     TX      65538   123456      6       0        3,456     1
   1    345 appuser  HR_APP          00000000601B9F10     00000000601D2B45     TX      65538   123456      0       6        3,456     0
   2    456 mjones   SCOTT           00000000602C3D78     00000000602D4E89     TX      98765   234567      6       0        1,234     0
   2    567 batch    BATCH_USER      00000000603E5F9A     00000000603F6FAB     TX     131072   345678      6       0          567     0
   1    678 webuser  WEB_APP         00000000604G7HBC     00000000604H8ICD     TX     196608   456789      6       0          123     0