Skip to content
Oracle DBA Scripts & Database Utilities

Gvscapture (gvscapture.sql)

What This Script Does

Section titled “What This Script Does”

This script provides comprehensive monitoring of Oracle Streams capture processes, showing message flow, timing, and performance metrics. Essential for Streams replication troubleshooting and performance tuning.

The Script

Section titled “The Script”
set linesize 450


col inst_id format 999 heading 'INST|ID'
col sid_serial format a12 heading 'SID_SERIAL#'
col capture_dblink_apply format a25 heading 'Capture_NM|DBLink_NM|Apply_NM'
col capture_name format a25 heading 'Capture NM'
col apply_dblink format a28 heading 'DB Link NM'
col apply_name format a25 heading 'Apply NM'
col startup_tm format a11 heading 'Capture|Startup TM'
col state format a25 heading 'Capture State'
col total_messages_captured format 999,999,999,999 heading 'Total|Msg Captured'
col capture_tm format a11 heading 'Last|Capture TM'
col capture_message_number heading 'Capture Msg NBR'
col capture_message_create_tm format a11 heading 'Capture MSG|Create TM'
col total_messages_created format 999,999,999,999 heading 'Total|Msg Created'
col total_messages_enqueued format 999,999,999,999 heading 'Total|Msg Enqueued'
col enqueue_tm format a11 heading 'Enqueue TM'
col enqueue_message_number heading 'Enq Msg NBR'
col enqueue_message_create_tm format a11 heading 'Enqueue Msg|Create TM'
col available_message_number heading 'Avail Msg NBR'
col available_message_create_tm format a11 heading 'Avail Msg|Create TM'
col state_changed_tm format a11 heading 'State|Changed TM'
col apply_messages_sent format 999,999,999,999 heading 'Total|Apply Msg Sent'




select inst_id              inst_id
      ,sid||','||serial#    sid_serial
--    ,capture
      ,capture_name||' * '||apply_dblink||' * '||apply_name capture_dblink_apply
--    ,logminer_id
      ,state
      ,to_char(startup_time,'mm-dd-yyyy hh24:mi:ss') startup_tm
      ,to_char(state_changed_time,'mm-dd-yyyy hh24:mi:ss') state_changed_tm
      ,to_char(capture_time,'mm-dd-yyyy hh24:mi:ss') capture_tm
      ,to_char(capture_message_create_time,'mm-dd-yyyy hh24:mi:ss') capture_message_create_tm
      ,to_char(enqueue_time,'mm-dd-yyyy hh24:mi:ss') enqueue_tm
      ,to_char(enqueue_message_create_time,'mm-dd-yyyy hh24:mi:ss')  enqueue_message_create_tm
      ,to_char(available_message_create_time,'mm-dd-yyyy hh24:mi:ss') available_message_create_tm
--    ,total_prefilter_discarded
--    ,total_prefilter_kept
--    ,total_prefilter_evaluations
      ,total_messages_captured
      ,total_messages_created
      ,total_messages_enqueued
      ,apply_messages_sent
--    ,total_full_evaluations
--    ,elapsed_capture_time/100          elapsed_capture_time
--    ,elapsed_rule_time/100            elapsed_rule_time
--    ,elapsed_enqueue_time/100         elapsed_enqueue_time
--    ,elapsed_lcr_time/100             elapsed_lcr_time
--    ,elapsed_redo_wait_time/100       elapsed_redo_wait_time
--    ,elapsed_pause_time/100           elapsed_pause_time
      ,capture_message_number
      ,enqueue_message_number
      ,available_message_number
--      ,apply_bytes_sent
--      ,optimization
  from gv$streams_capture a
;

Parameters

Section titled “Parameters”

The script prompts for:

  • None - this script requires no parameters## Usage
-- Basic usage
@gvscapture.sql

Sample Output

Section titled “Sample Output”
INST                 Capture_NM                Capture State             Capture        State        Last       Capture MSG      Total           Total           Total     Enqueue      Enqueue Msg  Avail Msg           Total CAPTURE MSG NBR ENQ MSG NBR AVAIL MSG NBR
  ID SID_SERIAL#    DBLink_NM                                           Startup TM   Changed TM   Capture TM   Create TM    Msg Captured    Msg Created    Msg Enqueued         TM       Create TM  Create TM  Apply Msg Sent
---- -------------- ------------------------- ------------------------- ----------- ----------- ----------- ----------- ---------------- ---------------- ---------------- ----------- ----------- ----------- ---------------- --------------- ----------- -------------
   1 234,5678       HR_CAPTURE * REMOTE_DB *  CAPTURING CHANGES         01-05-2025  01-05-2025  01-05-2025  01-05-2025        1,234,567        1,234,567        1,234,560 01-05-2025  01-05-2025  01-05-2025        1,234,550       123456789   123456782     123456780
                    HR_APPLY                                            08:00:00    14:35:22    14:35:45    14:35:45                                                       14:35:44    14:35:44    14:35:44


   2 345,6789       SCOTT_CAPTURE * * SCOTT_  WAITING FOR REDO          01-05-2025  01-05-2025  01-05-2025  01-05-2025          567,890          567,890          567,885 01-05-2025  01-05-2025  01-05-2025          567,880        56789012    56789007      56789005
                    APPLY                                               09:30:00    14:30:00    14:34:55    14:34:55                                                       14:34:54    14:34:54    14:34:54


   1 456,7890       TEST_CAPTURE * TEST_LNK * PAUSED FOR FLOW CONTROL   01-04-2025  01-05-2025  01-05-2025  01-05-2025           89,012           89,012           89,000 01-05-2025  01-05-2025  01-05-2025           88,995         8901234     8901222       8901220
                    TEST_APPLY                                          15:00:00    12:00:00    11:59:30    11:59:30                                                       11:59:29    11:59:29    11:59:29