Skip Headers

Oracle9i Streams
Release 2 (9.2)

Part Number A96571-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

17
Monitoring a Streams Environment

This chapter provides information about the static data dictionary views and dynamic performance views related to Streams. You can use these views to monitor your Streams environment. This chapter also illustrates example queries that you may want to use to monitor your Streams environment.

This chapter contains these topics:

Summary of Streams Static Data Dictionary Views

The following table lists the Streams static data dictionary views.

Table 17-1 Streams Static Data Dictionary Views 
ALL_ Views DBA_ Views USER_ Views

ALL_APPLY

DBA_APPLY

No USER_ view

ALL_APPLY_CONFLICT_COLUMNS

DBA_APPLY_CONFLICT_COLUMNS

No USER_ view

ALL_APPLY_DML_HANDLERS

DBA_APPLY_DML_HANDLERS

No USER_ view

ALL_APPLY_ERROR

DBA_APPLY_ERROR

No USER_ view

No ALL_ view

DBA_APPLY_INSTANTIATED_OBJECTS

No USER_ view

ALL_APPLY_KEY_COLUMNS

DBA_APPLY_KEY_COLUMNS

No USER_ view

ALL_APPLY_PARAMETERS

DBA_APPLY_PARAMETERS

No USER_ view

ALL_APPLY_PROGRESS

DBA_APPLY_PROGRESS

No USER_ view

ALL_CAPTURE

DBA_CAPTURE

No USER_ view

ALL_CAPTURE_PARAMETERS

DBA_CAPTURE_PARAMETERS

No USER_ view

ALL_CAPTURE_PREPARED_DATABASE

DBA_CAPTURE_PREPARED_DATABASE

No USER_ view

ALL_CAPTURE_PREPARED_SCHEMAS

DBA_CAPTURE_PREPARED_SCHEMAS

No USER_ view

ALL_CAPTURE_PREPARED_TABLES

DBA_CAPTURE_PREPARED_TABLES

No USER_ view

ALL_EVALUATION_CONTEXT_TABLES

DBA_EVALUATION_CONTEXT_TABLES

USER_EVALUATION_CONTEXT_TABLES

ALL_EVALUATION_CONTEXT_VARS

DBA_EVALUATION_CONTEXT_VARS

USER_EVALUATION_CONTEXT_VARS

ALL_EVALUATION_CONTEXTS

DBA_EVALUATION_CONTEXTS

USER_EVALUATION_CONTEXTS

ALL_PROPAGATION

DBA_PROPAGATION

No USER_ view

ALL_RULE_SET_RULES

DBA_RULE_SET_RULES

USER_RULE_SET_RULES

ALL_RULE_SETS

DBA_RULE_SETS

USER_RULE_SETS

ALL_RULES

DBA_RULES

USER_RULES

ALL_STREAMS_GLOBAL_RULES

DBA_STREAMS_GLOBAL_RULES

No USER_ view

ALL_STREAMS_SCHEMA_RULES

DBA_STREAMS_SCHEMA_RULES

No USER_ view

ALL_STREAMS_TABLE_RULES

DBA_STREAMS_TABLE_RULES

No USER_ view

Summary of Streams Dynamic Performance Views

The following list includes the Streams dynamic performance views

Monitoring a Streams Capture Process

The following sections contain queries that you can run to display information about a capture process:

Displaying the Queue, Rule Set, and Status of Each Capture Process

You can display the following general information about each capture process in a database by running the query in this section:

To display this general information about each capture process in a database, run the following query:

COLUMN CAPTURE_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Capture|Process|Queue' FORMAT A20
COLUMN RULE_SET_NAME HEADING 'Capture|Process|Rule Set' FORMAT A15
COLUMN STATUS HEADING 'Capture|Process|Status' FORMAT A15

SELECT CAPTURE_NAME, QUEUE_NAME, RULE_SET_NAME, STATUS FROM DBA_CAPTURE;

Your output looks similar to the following:

Capture         Capture              Capture         Capture
Process         Process              Process         Process
Name            Queue                Rule Set        Status
--------------- -------------------- --------------- ---------------
CAPTURE         STREAMS_QUEUE        RULESET$_6      ENABLED

Displaying General Information About a Capture Process

The query in this section displays the following general information about a particular capture process:

For example, to display this information for a capture process named capture, run the following query:

COLUMN PROCESS_NAME HEADING 'Capture|Process|Number' FORMAT A7
COLUMN SID HEADING 'Session|ID' FORMAT 9999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999
COLUMN STATE HEADING 'State' FORMAT A17
COLUMN TOTAL_MESSAGES_CAPTURED HEADING 'Redo Entries|Scanned' FORMAT 9999999
COLUMN TOTAL_MESSAGES_ENQUEUED HEADING 'Total|LCRs|Enqueued' FORMAT 999999

SELECT SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME, 
       c.SID,
       c.SERIAL#, 
       c.STATE,
       c.TOTAL_MESSAGES_CAPTURED,
       c.TOTAL_MESSAGES_ENQUEUED 
  FROM V$STREAMS_CAPTURE c, V$SESSION s
  WHERE c.CAPTURE_NAME = 'CAPTURE' AND
        c.SID = s.SID AND
        c.SERIAL# = s.SERIAL#;

Your output looks similar to the following:

Capture         Session                                   Total
Process Session  Serial                   Redo Entries     LCRs
Number       ID  Number State                  Scanned Enqueued
------- ------- ------- ----------------- ------------ --------
CP01         18     150 CAPTURING CHANGES        56900        7

The number of redo entries scanned may be higher than the number of DML and DDL redo entries that evaluate to TRUE for a capture process rule set. Only DML and DDL redo entries that evaluate to TRUE for a capture process rule set are enqueued into the capture process queue. Also, the total LCRs enqueued includes LCRs that contain transaction control statements. These row LCRs contain directives such as COMMIT and ROLLBACK. Therefore, the total LCRs enqueued is a number higher than the number of row changes and DDL changes enqueued by a capture process.

See Also:

"Row LCRs" for more information about transaction control statements

Listing the Parameter Settings for a Capture Process

The query in this section displays the current setting for each capture process parameter for a particular capture process.

For example, to display the settings for the capture process parameters of a capture process named capture, run the following query:

COLUMN PARAMETER HEADING 'Parameter' FORMAT A20
COLUMN VALUE HEADING 'Value' FORMAT A20
COLUMN SET_BY_USER HEADING 'Set by User?' FORMAT A20

SELECT PARAMETER, 
       VALUE,
       SET_BY_USER  
  FROM DBA_CAPTURE_PARAMETERS
  WHERE CAPTURE_NAME = 'CAPTURE';

Your output looks similar to the following:

Parameter            Value                Set by User?
-------------------- -------------------- --------------------
DISABLE_ON_LIMIT     N                    NO
MAXIMUM_SCN          INFINITE             NO
MESSAGE_LIMIT        INFINITE             NO
PARALLELISM          3                    YES
STARTUP_SECONDS      0                    NO
TIME_LIMIT           INFINITE             NO
TRACE_LEVEL          0                    NO
WRITE_ALERT_LOG      Y                    NO

Note:

If the Set by User? column is NO for a parameter, then the parameter is set to its default value. If the Set by User? column is YES for a parameter, then the parameter may or may not be set to its default value.


See Also:

Determining the Applied SCN for All Capture Processes in a Database

The applied system change number (SCN) for a capture process is the SCN of the most recent event dequeued by the relevant apply processes. All changes below this applied SCN have been dequeued by all apply processes that apply changes captured by the capture process. This SCN is important because all redo logs must be kept available to a capture process until all transactions within the redo logs have been applied at all downstream databases.

To display the applied SCN for all of the capture processes in a database, run the following query:

COLUMN CAPTURE_NAME HEADING 'Capture Process Name' FORMAT A30
COLUMN APPLIED_SCN HEADING 'Applied SCN' FORMAT 999999

SELECT CAPTURE_NAME, APPLIED_SCN FROM DBA_CAPTURE;

Your output looks similar to the following:

Capture Process Name           Applied SCN
------------------------------ -----------
CAPTURE_EMP                         177154

Determining Redo Log Scanning Latency for a Capture Process

You can find the following information about a capture process by running the query in this section:

The information displayed by this query is valid only for an enabled capture process.

Run the following query to determine the redo scanning latency for a capture process named capture:

COLUMN LATENCY_SECONDS HEADING 'Latency|in|Seconds' FORMAT 999999
COLUMN LAST_STATUS HEADING 'Seconds Since|Last Status' FORMAT 999999
COLUMN CAPTURE_TIME HEADING 'Current|Process|Time'
COLUMN CREATE_TIME HEADING 'Event|Creation Time' FORMAT 999999

SELECT ((SYSDATE - CAPTURE_MESSAGE_CREATE_TIME)*86400) LATENCY_SECONDS,
       ((SYSDATE - CAPTURE_TIME)*86400) LAST_STATUS,
       TO_CHAR(CAPTURE_TIME, 'HH24:MI:SS MM/DD/YY') CAPTURE_TIME,       
       TO_CHAR(CAPTURE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME
  FROM V$STREAMS_CAPTURE
  WHERE CAPTURE_NAME = 'CAPTURE';

Your output looks similar to the following:

Latency               Current
     in Seconds Since Process           Event
Seconds   Last Status Time              Creation Time
------- ------------- ----------------- -----------------
      4             4 12:04:13 03/01/02 12:04:13 03/01/02

The "Latency in Seconds" returned by this query is the difference between the current time (SYSDATE) and the "Event Creation Time." The "Seconds Since Last Status" returned by this query is the difference between the current time (SYSDATE) and the "Current Process Time."

Determining Event Enqueuing Latency for a Capture Process

You can find the following information about a capture process by running the query in this section:

The information displayed by this query is valid only for an enabled capture process.

Run the following query to determine the event capturing latency for a capture process named capture:

COLUMN LATENCY_SECONDS HEADING 'Latency|in|Seconds' FORMAT 999999
COLUMN CREATE_TIME HEADING 'Event Creation|Time' FORMAT A20
COLUMN ENQUEUE_TIME HEADING 'Enqueue Time' FORMAT A20
COLUMN ENQUEUE_MESSAGE_NUMBER HEADING 'Message|Number' FORMAT 999999

SELECT (ENQUEUE_TIME-ENQUEUE_MESSAGE_CREATE_TIME)*86400 LATENCY_SECONDS, 
       TO_CHAR(ENQUEUE_MESSAGE_CREATE_TIME, 'HH24:MI:SS MM/DD/YY') CREATE_TIME,
       TO_CHAR(ENQUEUE_TIME, 'HH24:MI:SS MM/DD/YY') ENQUEUE_TIME,
       ENQUEUE_MESSAGE_NUMBER
  FROM V$STREAMS_CAPTURE
  WHERE CAPTURE_NAME = 'CAPTURE';

Your output looks similar to the following:

Latency
     in Event Creation                            Message
Seconds Time                 Enqueue Time          Number
------- -------------------- -------------------- -------
      0 10:56:51 03/01/02    10:56:51 03/01/02     253962

The "Latency in Seconds" returned by this query is the difference between the "Enqueue Time" and the "Event Creation Time."

Determining Which Database Objects Are Prepared for Instantiation

You prepare a database object for instantiation using one of the following procedures in the DBMS_CAPTURE_ADM package:

To determine which database objects have been prepared for instantiation, query the following corresponding data dictionary views:

For example, to list all of the tables that have been prepared for instantiation, the SCN for the time when each table was prepared, and the time when each table was prepared, run the following query:

COLUMN TABLE_OWNER HEADING 'Table Owner' FORMAT A15
COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A15
COLUMN SCN HEADING 'Instantiation SCN' FORMAT 999999
COLUMN TIMESTAMP HEADING 'Time Ready for|Instantiation'

SELECT TABLE_OWNER, 
       TABLE_NAME, 
       SCN, 
       TO_CHAR(TIMESTAMP, 'HH24:MI:SS MM/DD/YY') TIMESTAMP
  FROM DBA_CAPTURE_PREPARED_TABLES;

Your output looks similar to the following:

                                                  Time Ready for
Table Owner     Table Name      Instantiation SCN Instantiation
--------------- --------------- ----------------- -----------------
HR              COUNTRIES                  196655 12:59:30 02/28/02
HR              DEPARTMENTS                196658 12:59:30 02/28/02
HR              EMPLOYEES                  196659 12:59:30 02/28/02
HR              JOBS                       196660 12:59:30 02/28/02
HR              JOB_HISTORY                196661 12:59:30 02/28/02
HR              LOCATIONS                  196662 12:59:30 02/28/02
HR              REGIONS                    196664 12:59:30 02/28/02

See Also:

"Preparing Database Objects for Instantiation at a Source Database"

Displaying Supplemental Log Groups at a Source Database

Supplemental logging places additional column data into a redo log whenever an UPDATE operation is performed. The capture process captures this additional information and places it in LCRs. An apply process that applies captured LCRs may need this additional information to schedule or apply changes correctly.

To check whether one or more log groups are specified for the table at the source database, run the following query:

COLUMN LOG_GROUP_NAME HEADING 'Log Group' FORMAT A20
COLUMN TABLE_NAME HEADING 'Table' FORMAT A20
COLUMN ALWAYS HEADING 'Type of Log Group' FORMAT A30

SELECT 
    LOG_GROUP_NAME, 
    TABLE_NAME, 
    DECODE(ALWAYS,
             'ALWAYS', 'Unconditional',
             NULL,     'Conditional') ALWAYS
  FROM DBA_LOG_GROUPS;

Your output looks similar to the following:

Log Group            Table                Type of Log Group
-------------------- -------------------- ------------------------------
LOG_GROUP_DEP_PK     DEPARTMENTS          Unconditional
LOG_GROUP_JOBS_CR    JOBS                 Conditional

To list the columns in a particular log group, query the DBA_LOG_GROUP_COLUMNS data dictionary view. You can also query the V$DATABASE dynamic performance view to display supplemental logging specified at the database level.

See Also:

Monitoring a Streams Queue

The following sections contain queries that you can run to display information about a Streams queue:

Displaying the Streams Queues in a Database

Streams queues are of object type SYS.AnyData. To display all of the Streams queues in a database, run the following query:

COLUMN OWNER HEADING 'Owner' FORMAT A10
COLUMN NAME HEADING 'Queue Name' FORMAT A25
COLUMN QUEUE_TABLE HEADING 'Queue Table' FORMAT A20
COLUMN USER_COMMENT HEADING 'Comment' FORMAT A20

SELECT q.OWNER, q.NAME, t.QUEUE_TABLE, q.USER_COMMENT
  FROM DBA_QUEUES q, DBA_QUEUE_TABLES t
  WHERE t.OBJECT_TYPE = 'SYS.ANYDATA' AND
        q.QUEUE_TABLE = t.QUEUE_TABLE AND
        q.OWNER       = t.OWNER;

Your output looks similar to the following:

Owner      Queue Name                Queue Table          Comment
---------- ------------------------- -------------------- --------------------
STRMADMIN  AQ$_STREAMS_QUEUE_TABLE_E STREAMS_QUEUE_TABLE  exception queue
STRMADMIN  STREAMS_QUEUE             STREAMS_QUEUE_TABLE

An exception queue is created automatically when you create a Streams queue.

See Also:

"Managing Streams Queues"

Determining the Consumer of Each User-Enqueued Event in a Queue

To determine the consumer for each user-enqueued event in a queue, query AQ$queue_table_name in the queue owner's schema, where queue_table_name is the name of the queue table. For example, to find the consumers of the user-enqueued events in the oe_queue_table queue table, run the following query:

COLUMN MSG_ID HEADING 'Message ID' FORMAT 9999
COLUMN MSG_STATE HEADING 'Message State' FORMAT A13
COLUMN CONSUMER_NAME HEADING 'Consumer' FORMAT A30

SELECT MSG_ID, MSG_STATE, CONSUMER_NAME FROM AQ$OE_QUEUE_TABLE;

Your output looks similar to the following:

Message ID                       Message State Consumer
-------------------------------- ------------- ------------------------------
99315B276CFA1872E034080020AE3E0A PROCESSED     APPLY_OE
99315B276CFB1872E034080020AE3E0A PROCESSED     APPLY_OE
99315B276CFA1872E034080020AE3E0A READY         EXPLICIT_DQ
99315B276CFB1872E034080020AE3E0A READY         EXPLICIT_DQ


Note:

This query lists only user-enqueued events, not captured events.


See Also:

Chapter 19, "Streams Messaging Example" for an example that enqueues the events shown in this example into a Streams queue

Viewing the Contents of User-Enqueued Events in a Queue

In a Streams queue, to view the contents of a payload that is encapsulated within a SYS.AnyData payload, you query the queue table using the Accessdata_type static functions of the SYS.AnyData type, where data_type is the type of payload to view.

See Also:

"Wrapping User Message Payloads in a SYS.AnyData Wrapper" for an example that enqueues the events shown in the queries in this section into a Streams queue

For example, to view the contents of payload of type NUMBER in a queue with a queue table named oe_queue_table, run the following query as the queue owner:

SELECT qt.user_data.AccessNumber() "Numbers in Queue" 
  FROM strmadmin.oe_q_table_any qt;

Your output looks similar to the following:

Numbers in Queue
----------------
              16

Similarly, to view the contents of a payload of type VARCHAR2 in a queue with a queue table named oe_q_table_any, run the following query:

SELECT qt.user_data.AccessVarchar2() "Varchar2s in Queue"
   FROM strmadmin.oe_q_table_any qt;

Your output looks similar to the following:

Varchar2s in Queue
--------------------------------------------------------------------------------
Chemicals - SW

To view the contents of a user-defined datatype, you query the queue table using a custom function that you create. For example, to view the contents of a payload of oe.cust_address_typ, connect as the Streams administrator and create a function similar to the following:

CONNECT oe/oe

CREATE OR REPLACE FUNCTION oe.view_cust_address_typ(
in_any IN SYS.AnyData) 
RETURN oe.cust_address_typ
IS
  address   oe.cust_address_typ;
  num_var   NUMBER;
BEGIN
  IF (in_any.GetTypeName() = 'OE.CUST_ADDRESS_TYP') THEN
    num_var := in_any.GetObject(address);
    RETURN address;
  ELSE RETURN NULL;
  END IF;
END;
/

GRANT EXECUTE ON oe.view_cust_address_typ TO STRMADMIN;

GRANT EXECUTE ON oe.cust_address_typ TO STRMADMIN;

Then, query the queue table using the function, as in the following example:

CONNECT strmadmin/strmadminpw

SELECT oe.view_cust_address_typ(qt.user_data) "Customer Addresses"
  FROM strmadmin.oe_q_table_any qt 
  WHERE qt.user_data.GetTypeName() = 'OE.CUST_ADDRESS_TYP';

Your output looks similar to the following:

Customer Addresses(STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID
--------------------------------------------------------------------------------
CUST_ADDRESS_TYP('1646 Brazil Blvd', '361168', 'Chennai', 'Tam', 'IN')

Monitoring Streams Propagations and Propagation Jobs

The following sections contain queries that you can run to display information about propagations and propagation jobs:

Determining the Source Queue and Destination Queue for a Propagation

You can determine the source queue and destination queue for a propagation by querying the DBA_PROPAGATION data dictionary view at the database that contains the source queue.

For example, the following query displays the following information for a propagation named dbs1_to_dbs2:

Your output looks similar to the following:

Source Queue                        Destination Queue
----------------------------------- -----------------------------------
STRMADMIN.STREAMS_QUEUE@DBS1.NET    STRMADMIN.STREAMS_QUEUE@DBS2.NET

Determining the Rule Set for a Propagation

The following query displays the owner and name of a rule set used by a propagation named dbs1_to_dbs2:

COLUMN RULE_SET_OWNER HEADING 'Rule Set Owner' FORMAT A35
COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A35

SELECT RULE_SET_OWNER, RULE_SET_NAME 
  FROM DBA_PROPAGATION 
  WHERE PROPAGATION_NAME = 'DBS1_TO_DBS2';

Your output looks similar to the following:

Rule Set Owner                      Rule Set Name
----------------------------------- -----------------------------------
STRMADMIN                           RULESET$_3

Displaying the Schedule for a Propagation Job

The query in this section displays the following information about the propagation schedule for a propagation job used by a propagation named dbs1_to_dbs2:

Run this query at the database that contains the source queue:

COLUMN START_DATE HEADING 'Start Date'
COLUMN PROPAGATION_WINDOW HEADING 'Duration|in Seconds' FORMAT 99999
COLUMN NEXT_TIME HEADING 'Next|Time' FORMAT A8
COLUMN LATENCY HEADING 'Latency|in Seconds' FORMAT 99999
COLUMN SCHEDULE_DISABLED HEADING 'Status' FORMAT A8
COLUMN PROCESS_NAME HEADING 'Process' FORMAT A8
COLUMN FAILURES HEADING 'Number of|Failures' FORMAT 99

SELECT TO_CHAR(s.START_DATE, 'HH24:MI:SS MM/DD/YY') START_DATE,
       s.PROPAGATION_WINDOW, 
       s.NEXT_TIME, 
       s.LATENCY,
       DECODE(s.SCHEDULE_DISABLED,
                'Y', 'Disabled',
                'N', 'Enabled') SCHEDULE_DISABLED,
       s.PROCESS_NAME,
       s.FAILURES
  FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
  WHERE p.PROPAGATION_NAME = 'DBS1_TO_DBS2'
  AND p.DESTINATION_DBLINK = s.DESTINATION
  AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
  AND s.QNAME = p.SOURCE_QUEUE_NAME;

Your output looks similar to the following:

                    Duration Next        Latency                   Number of
Start Date        in Seconds Time     in Seconds Status   Process   Failures
----------------- ---------- -------- ---------- -------- -------- ---------
15:23:40 03/02/02                              5 Enabled  J002             0

This propagation job uses the default schedule for a Streams propagation job. That is, the duration and next time are both NULL, and the latency is five seconds. When the duration is NULL, the job propagates changes without restarting automatically. When the next time is NULL, the propagation job is running currently.

See Also:

Determining the Total Number of Events and Bytes Propagated

All propagation jobs from a source queue that share the same database link have a single propagation schedule. The query in this section displays the following information for a propagation schedule associated with a particular propagation job:

For example, to display this information for a propagation job used by a propagation named dbs1_to_dbs2, run the following query at the database that contains the source queue:

COLUMN TOTAL_TIME HEADING 'Total Time Executing|in Seconds' FORMAT 999999
COLUMN TOTAL_NUMBER HEADING 'Total Events Propagated' FORMAT 999999999
COLUMN TOTAL_BYTES HEADING 'Total Bytes Propagated' FORMAT 9999999999999

SELECT s.TOTAL_TIME, s.TOTAL_NUMBER, s.TOTAL_BYTES 
  FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
  WHERE p.PROPAGATION_NAME = 'DBS1_TO_DBS2'
  AND p.DESTINATION_DBLINK = s.DESTINATION
  AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
  AND s.QNAME = p.SOURCE_QUEUE_NAME;

Your output looks similar to the following:

Total Time Executing
          in Seconds Total Events Propagated Total Bytes Propagated
-------------------- ----------------------- ----------------------
                  65                      71                  46536

See Also:

Oracle9i Application Developer's Guide - Advanced Queuing and Oracle9i Database Reference for more information about the DBA_QUEUE_SCHEDULES data dictionary view

Monitoring a Streams Apply Process

The following sections contain queries that you can run to display information about an apply process:

Displaying General Information About Each Apply Process

You can display the following general information about each apply process in a database by running the query in this section:

To display this general information about each apply process in a database, run the following query:

COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Apply|Process|Queue' FORMAT A15
COLUMN RULE_SET_NAME HEADING 'Apply|Process|Rule Set' FORMAT A15
COLUMN APPLY_CAPTURED HEADING 'Type of|Events|Applied' FORMAT A15
COLUMN STATUS HEADING 'Apply|Process|Status' FORMAT A8

SELECT APPLY_NAME, 
       QUEUE_NAME, 
       RULE_SET_NAME, 
       DECODE(APPLY_CAPTURED,
              'YES', 'Captured',
              'NO',  'User-Enqueued') APPLY_CAPTURED,
       STATUS 
FROM DBA_APPLY;

Your output looks similar to the following:

Apply           Apply           Apply           Type of         Apply
Process         Process         Process         Events          Process
Name            Queue           Rule Set        Applied         Status
--------------- --------------- --------------- --------------- --------
APPLY_OE        OE_QUEUE        APPLY_OE_RS     User-Enqueued   ENABLED
APPLY           OE_QUEUE        RULESET$_4      Captured        DISABLED

Listing the Parameter Settings for an Apply Process

The query in this section displays the current setting for each apply process parameter for a particular apply process.

For example, to display the settings for the apply process parameters of an apply process named strm01_apply, run the following query:

COLUMN PARAMETER HEADING 'Parameter' FORMAT A20
COLUMN VALUE HEADING 'Value' FORMAT A20
COLUMN SET_BY_USER HEADING 'Set by User?' FORMAT A20

SELECT PARAMETER, 
       VALUE,
       SET_BY_USER  
  FROM DBA_APPLY_PARAMETERS
  WHERE APPLY_NAME = 'STRM01_APPLY';

Your output looks similar to the following:

Parameter            Value                Set by User?
-------------------- -------------------- --------------------
COMMIT_SERIALIZATION FULL                 NO
DISABLE_ON_ERROR     Y                    YES
DISABLE_ON_LIMIT     N                    NO
MAXIMUM_SCN          INFINITE             NO
PARALLELISM          1                    NO
STARTUP_SECONDS      0                    NO
TIME_LIMIT           INFINITE             NO
TRACE_LEVEL          0                    NO
TRANSACTION_LIMIT    INFINITE             NO
WRITE_ALERT_LOG      Y                    NO


Note:

If the Set by User? column is NO for a parameter, then the parameter is set to its default value. If the Set by User? column is YES for a parameter, then the parameter may or may not be set to its default value.


See Also:

Displaying Information About Apply Handlers

This section contains queries that display information about apply process DML handlers, DDL handlers, and error handlers.

See Also:

"Event Processing with an Apply Process"

Displaying All of the DML and Error Handlers for Local Apply

When you specify a local DML or error handler using the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package at a destination database, the handler is run for all apply processes in the database that apply changes locally, when appropriate. DML and error handlers are run for a specified operation on a specific table.

To display the DML or error handler for each apply process that applies changes locally in a database, run the following query:

COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A5
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A10
COLUMN OPERATION_NAME HEADING 'Operation' FORMAT A9
COLUMN USER_PROCEDURE HEADING 'Handler Procedure' FORMAT A40
COLUMN ERROR_HANDLER HEADING 'Type of|Handler' FORMAT A10

SELECT OBJECT_OWNER, 
       OBJECT_NAME, 
       OPERATION_NAME, 
       USER_PROCEDURE, 
       DECODE(ERROR_HANDLER,
              'Y', 'Error',
              'N', 'DML') ERROR_HANDLER
  FROM DBA_APPLY_DML_HANDLERS
  WHERE APPLY_DATABASE_LINK IS NULL
  ORDER BY OBJECT_OWNER, OBJECT_NAME, ERROR_HANDLER;

Your output looks similar to the following:

Table                                                               Type of
Owner Table Name Operation Handler Procedure                        Handler
----- ---------- --------- ---------------------------------------- ----------
HR    LOCATIONS  UPDATE    STRMADMIN.HISTORY_DML                    DML
HR    REGIONS    INSERT    STRMADMIN.ERRORS_PKG.REGIONS_PK_ERROR    Error


Note:

You can also specify DML handlers to process changes for remote non-Oracle databases. This query does not display such DML handlers because it lists a DML handler only if the APPLY_DATABASE_LINK column is NULL for a DML handler.


See Also:

Displaying the DDL Handler and Message Handler for Each Apply Process

To display the DDL handler and message handler for each apply process in a database, run the following query:

COLUMN APPLY_NAME HEADING 'Apply Process Name' FORMAT A20
COLUMN DDL_HANDLER HEADING 'DDL Handler' FORMAT A20
COLUMN MESSAGE_HANDLER HEADING 'Message Handler' FORMAT A20

SELECT APPLY_NAME, DDL_HANDLER, MESSAGE_HANDLER FROM DBA_APPLY;

Your output looks similar to the following:

Apply Process Name   DDL Handler          Message Handler
-------------------- -------------------- --------------------
APPLY                oe.ddl_handler
APPLY_OE                                  oe.msg_handler

See Also:

Displaying the Substitute Key Columns Specified at a Destination Database

You can designate a substitute key at a destination database, which is a column or set of columns that Oracle can use to identify rows in the table during apply. Substitute key columns can be used to specify key columns for a table that has no primary key, or they can be used instead of a table's primary key when the table is processed by any apply process at a destination database.

To display all of the substitute key columns specified at a destination database, run the following query:

COLUMN OBJECT_OWNER HEADING 'Table Owner' FORMAT A20
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A20
COLUMN COLUMN_NAME HEADING 'Substitute Key Name' FORMAT A20
COLUMN APPLY_DATABASE_LINK HEADING 'Database Link|for Remote|Apply' FORMAT A15

SELECT OBJECT_OWNER, OBJECT_NAME, COLUMN_NAME, APPLY_DATABASE_LINK 
  FROM DBA_APPLY_KEY_COLUMNS
  ORDER BY APPLY_DATABASE_LINK, OBJECT_OWNER, OBJECT_NAME;

Your output looks similar to the following:

                                                               Database Link
                                                               for Remote
Table Owner          Table Name           Substitute Key Name  Apply
-------------------- -------------------- -------------------- ---------------
HR                   DEPARTMENTS          DEPARTMENT_NAME
HR                   DEPARTMENTS          LOCATION_ID
HR                   EMPLOYEES            FIRST_NAME
HR                   EMPLOYEES            LAST_NAME
HR                   EMPLOYEES            HIRE_DATE


Note:

This query shows the database link in the last column if the substitute key columns are for a remote non-Oracle database. The last column is NULL if a substitute key column is specified for the local destination database.


See Also:

Displaying Information About Update Conflict Handlers for a Destination Database

When you specify an update conflict handler using the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package, the update conflict handler is run for all apply processes in the database, when a relevant conflict occurs.

The query in this section displays all of the columns for which conflict resolution has been specified using a prebuilt update conflict handler. That is, it shows the columns in all of the column lists specified in the database. This query also shows the type of prebuilt conflict handler specified and the resolution column specified for the column list.

To display information about all of the update conflict handlers in a database, run the following query:

COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A5
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A12
COLUMN METHOD_NAME HEADING 'Method' FORMAT A12
COLUMN RESOLUTION_COLUMN HEADING 'Resolution|Column' FORMAT A13
COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A30

SELECT OBJECT_OWNER, 
       OBJECT_NAME, 
       METHOD_NAME, 
       RESOLUTION_COLUMN, 
       COLUMN_NAME
  FROM DBA_APPLY_CONFLICT_COLUMNS
  ORDER BY OBJECT_OWNER, OBJECT_NAME, RESOLUTION_COLUMN;

Your output looks similar to the following:

Table                           Resolution
Owner Table Name   Method       Column        Column Name
----- ------------ ------------ ------------- ------------------------------
HR    COUNTRIES    MAXIMUM      TIME          COUNTRY_NAME
HR    COUNTRIES    MAXIMUM      TIME          REGION_ID
HR    COUNTRIES    MAXIMUM      TIME          TIME
HR    DEPARTMENTS  MAXIMUM      TIME          DEPARTMENT_NAME
HR    DEPARTMENTS  MAXIMUM      TIME          LOCATION_ID
HR    DEPARTMENTS  MAXIMUM      TIME          MANAGER_ID
HR    DEPARTMENTS  MAXIMUM      TIME          TIME

See Also:

Determining the Tables for Which an Instantiation SCN Has Been Set

An instantiation SCN is set at a destination database. It controls which captured LCRs for a table are ignored by an apply process and which captured LCRs for a database object are applied by an apply process. If the commit SCN of an LCR for a table from a source database is less than or equal to the instantiation SCN for that table at a destination database, then the apply process at the destination database discards the LCR. Otherwise, the apply process applies the LCR.

The following query lists each table for which an instantiation SCN has been set at a destination database and the instantiation SCN for each table:

COLUMN SOURCE_DATABASE HEADING 'Source Database' FORMAT A15
COLUMN SOURCE_OBJECT_OWNER HEADING 'Object Owner' FORMAT A15
COLUMN SOURCE_OBJECT_NAME HEADING 'Object Name' FORMAT A15
COLUMN INSTANTIATION_SCN HEADING 'Instantiation SCN' FORMAT 999999

SELECT SOURCE_DATABASE, 
       SOURCE_OBJECT_OWNER, 
       SOURCE_OBJECT_NAME, 
       INSTANTIATION_SCN 
  FROM DBA_APPLY_INSTANTIATED_OBJECTS;

Your output looks similar to the following:

Source Database Object Owner    Object Name     Instantiation SCN
--------------- --------------- --------------- -----------------
DBS1.NET        HR              REGIONS                    196660
DBS1.NET        HR              COUNTRIES                  196660
DBS1.NET        HR              LOCATIONS                  196660

See Also:

"Setting Instantiation SCNs at a Destination Database"

Displaying Information About the Reader Server for an Apply Process

The reader server for an apply process dequeues events from the queue. The reader server is a parallel execution server that computes dependencies between LCRs and assembles events into transactions. The reader server then returns the assembled transactions to the coordinator, which assigns them to idle apply servers.

The query in this section displays the following information about the reader server for a particular apply process:

The information displayed by this query is valid only for an enabled apply process.

For example, to display this information for an apply process named apply, run the following query:

COLUMN APPLY_CAPTURED HEADING 'Apply Type' FORMAT A22
COLUMN PROCESS_NAME HEADING 'Process Name' FORMAT A12
COLUMN STATE HEADING 'State' FORMAT A17
COLUMN TOTAL_MESSAGES_DEQUEUED HEADING 'Total Events Dequeued' FORMAT 99999999

SELECT DECODE(ap.APPLY_CAPTURED,
                'YES','Captured LCRS',
                'NO','User-enqueued messages','UNKNOWN') APPLY_CAPTURED,
       SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME,
       r.STATE,
       r.TOTAL_MESSAGES_DEQUEUED
       FROM V$STREAMS_APPLY_READER r, V$SESSION s, DBA_APPLY ap 
       WHERE r.APPLY_NAME = 'APPLY' AND
             r.SID = s.SID AND 
             r.SERIAL# = s.SERIAL# AND 
             r.APPLY_NAME = ap.APPLY_NAME;

Your output looks similar to the following:

Apply Type             Process Name State             Total Events Dequeued
---------------------- ------------ ----------------- ---------------------
Captured LCRS          P000         DEQUEUE MESSAGES                   3803

Determining Capture to Dequeue Latency for an Event

The query in this section displays the following information about the last event dequeued by a particular apply process:

The information displayed by this query is valid only for an enabled apply process.

For example, to display the capture and propagation latency for the last captured event dequeued by an apply process named apply, run the following query:

COLUMN LATENCY HEADING 'Latency|in|Seconds' FORMAT 9999
COLUMN CREATION HEADING 'Event Creation' FORMAT A17
COLUMN LAST_DEQUEUE HEADING 'Last Dequeue Time' FORMAT A20
COLUMN DEQUEUED_MESSAGE_NUMBER HEADING 'Dequeued|Message Number' FORMAT 999999

SELECT (DEQUEUE_TIME-DEQUEUED_MESSAGE_CREATE_TIME)*86400 LATENCY,
     TO_CHAR(DEQUEUED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') CREATION,
     TO_CHAR(DEQUEUE_TIME,'HH24:MI:SS MM/DD/YY') LAST_DEQUEUE,
     DEQUEUED_MESSAGE_NUMBER  
  FROM V$STREAMS_APPLY_READER 
  WHERE APPLY_NAME = 'APPLY';

Your output looks similar to the following:

Latency
     in                                              Dequeued
Seconds Event Creation    Last Dequeue Time    Message Number
------- ----------------- -------------------- --------------
     36 10:56:51 03/01/02 10:57:27 03/01/02            253962

Displaying Information About the Coordinator Process

A coordinator process gets transactions from the reader server and passes these transactions to apply servers. The coordinator process name is apnn, where nn is a coordinator process number.

The query in this section displays the following information about the coordinator process for a particular apply process:

The information displayed by this query is valid only for an enabled apply process.

For example, to display this information for an apply process named apply, run the following query:

COLUMN PROCESS_NAME HEADING 'Coordinator|Process|Name' FORMAT A11
COLUMN SID HEADING 'Session|ID' FORMAT 9999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999
COLUMN STATE HEADING 'State' FORMAT A21
COLUMN TOTAL_RECEIVED HEADING 'Total|Trans|Received' FORMAT 99999999
COLUMN TOTAL_APPLIED HEADING 'Total|Trans|Applied' FORMAT 99999999
COLUMN TOTAL_ERRORS HEADING 'Total|Apply|Errors' FORMAT 9999

SELECT SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME,
       c.SID,
       c.SERIAL#,
       c.STATE,
       c.TOTAL_RECEIVED,
       c.TOTAL_APPLIED,
       c.TOTAL_ERRORS  
       FROM V$STREAMS_APPLY_COORDINATOR c, V$SESSION s
       WHERE c.APPLY_NAME = 'APPLY' AND
             c.SID = s.SID AND
             c.SERIAL# = s.SERIAL#;

Your output looks similar to the following:

Coordinator         Session                           Total     Total  Total
Process     Session  Serial                           Trans     Trans  Apply
Name             ID  Number State                  Received   Applied Errors
----------- ------- ------- --------------------- --------- --------- ------
AP01             11      40 APPLYING                     78        73      2

Determining the Capture to Apply Latency for an Event

This section contains two different queries that show the capture to apply latency for a particular event. That is, for captured events, these queries show the amount of time between when the event was created at a source database and when the event was applied by the apply process. One query uses the V$STREAMS_APPLY_COORDINATOR dynamic performance view, while the other uses the DBA_APPLY_PROGRESS static data dictionary view.


Note:

These queries assume that the apply process applies captured events, not user-enqueued events.


The following are the major differences between these two queries:

Both queries display the following information about an event applied by a particular apply process:

Example V$STREAMS_APPLY_COORDINATOR Query for Latency

To display the capture to apply latency using the V$STREAMS_APPLY_COORDINATOR view for an event applied by an apply process named apply, run the following query:

COLUMN 'Latency in Seconds' FORMAT 999999
COLUMN 'Event Creation' FORMAT A17
COLUMN 'Apply Time' FORMAT A17
COLUMN 'Applied Message Number' FORMAT 999999

SELECT (HWM_TIME-HWM_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
     TO_CHAR(HWM_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') 
        "Event Creation",
     TO_CHAR(HWM_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
     HWM_MESSAGE_NUMBER "Applied Message Number"  
  FROM V$STREAMS_APPLY_COORDINATOR 
  WHERE APPLY_NAME = 'APPLY';

Your output looks similar to the following:

Latency in Seconds Event Creation    Apply Time        Applied Message Number
------------------ ----------------- ----------------- ----------------------
                36 10:56:51 03/01/02 10:57:27 03/01/02                 253962

Example DBA_APPLY_PROGRESS Query for Latency

To display the capture to apply latency using the DBA_APPLY_PROGRESS view for an event applied by an apply process named apply, run the following query:

COLUMN 'Latency in Seconds' FORMAT 999999
COLUMN 'Event Creation' FORMAT A17
COLUMN 'Apply Time' FORMAT A17
COLUMN 'Applied Message Number' FORMAT 999999

SELECT (APPLY_TIME-APPLIED_MESSAGE_CREATE_TIME)*86400 "Latency in Seconds",
     TO_CHAR(APPLIED_MESSAGE_CREATE_TIME,'HH24:MI:SS MM/DD/YY') 
        "Event Creation",
     TO_CHAR(APPLY_TIME,'HH24:MI:SS MM/DD/YY') "Apply Time",
     APPLIED_MESSAGE_NUMBER "Applied Message Number"  
  FROM DBA_APPLY_PROGRESS 
  WHERE APPLY_NAME = 'APPLY';

Your output looks similar to the following:

Latency in Seconds Event Creation    Apply Time        Applied Message Number
------------------ ----------------- ----------------- ----------------------
                38 10:50:09 03/01/02 10:50:47 03/01/02                 253678

Displaying Information About the Apply Servers for an Apply Process

An apply process can use one or more apply servers that apply LCRs to database objects as DML statements or DDL statements or pass the LCRs to their appropriate handlers. For non-LCR messages, the apply servers pass the events to the message handler. Each apply server is a parallel execution server.

The query in this section displays the following information about the apply servers for a particular apply process:

The information displayed by this query is valid only for an enabled apply process.

For example, to display this information for an apply process named apply, run the following query:

COLUMN APPLY_CAPTURED HEADING 'Apply Type' FORMAT A22
COLUMN PROCESS_NAME HEADING 'Process Name' FORMAT A12
COLUMN STATE HEADING 'State' FORMAT A17
COLUMN TOTAL_ASSIGNED HEADING 'Total|Transactions|Assigned' FORMAT 99999999
COLUMN TOTAL_MESSAGES_APPLIED HEADING 'Total|Events|Applied' FORMAT 99999999

SELECT DECODE(ap.APPLY_CAPTURED,
                'YES','Captured LCRS',
                'NO','User-enqueued messages','UNKNOWN') APPLY_CAPTURED,
       SUBSTR(s.PROGRAM,INSTR(S.PROGRAM,'(')+1,4) PROCESS_NAME,
       r.STATE,
       r.TOTAL_ASSIGNED, 
       r.TOTAL_MESSAGES_APPLIED
  FROM V$STREAMS_APPLY_SERVER R, V$SESSION S, DBA_APPLY AP 
  WHERE r.APPLY_NAME = 'APPLY' AND
        r.SID = s.SID AND 
        r.SERIAL# = s.SERIAL# AND 
        r.APPLY_NAME = ap.APPLY_NAME 
  ORDER BY r.SERVER_ID;

Your output looks similar to the following:

                                                             Total     Total
                                                      Transactions    Events
Apply Type             Process Name State                 Assigned   Applied
---------------------- ------------ ----------------- ------------ ---------
Captured LCRs          P001         IDLE                        94      2141
Captured LCRs          P002         IDLE                        12       276
Captured LCRs          P003         IDLE                         0         0

Displaying Effective Apply Parallelism for an Apply Process

In some environments, an apply process may not use all of the apply servers available to it. For example, apply process parallelism may be set to five, but only three apply servers are ever used by the apply process. In this case, the effective apply parallelism is three.

The following query displays the effective apply parallelism for an apply process named apply:

SELECT COUNT(SERVER_ID) "Effective Parallelism"
  FROM V$STREAMS_APPLY_SERVER
  WHERE APPLY_NAME = 'APPLY' AND
        TOTAL_MESSAGES_APPLIED > 0;

Your output looks similar to the following:

Effective Parallelism
---------------------
                    2

This query returned two for the effective parallelism. If parallelism is set to three for the apply process named apply, then one apply server has not been used since the last time the apply process was started.

You can display the total number of events applied by each apply server by running the following query:

COLUMN SERVER_ID HEADING 'Apply Server ID' FORMAT 99
COLUMN TOTAL_MESSAGES_APPLIED HEADING 'Total Events Applied' FORMAT 999999

SELECT SERVER_ID, TOTAL_MESSAGES_APPLIED 
  FROM V$STREAMS_APPLY_SERVER
  WHERE APPLY_NAME = 'APPLY'
  ORDER BY SERVER_ID;

Your output looks similar to the following:

Apply Server ID Total Events Applied
--------------- --------------------
              1                 2141
              2                  276
              3                    0

In this case, apply server 3 has not been used by the apply process since it was last restarted. If the parallelism setting for an apply process is higher than the effective parallelism for the apply process, then consider lowering the parallelism setting.

Checking for Apply Errors

To check for apply errors, run the following query:

COLUMN APPLY_NAME HEADING 'Apply|Process|Name' FORMAT A8
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A8
COLUMN LOCAL_TRANSACTION_ID HEADING 'Local|Transaction|ID' FORMAT A11
COLUMN ERROR_MESSAGE HEADING 'Error Message' FORMAT A50

SELECT APPLY_NAME, SOURCE_DATABASE, LOCAL_TRANSACTION_ID, ERROR_MESSAGE
  FROM DBA_APPLY_ERROR;

If there are any apply errors, then your output looks similar to the following:

Apply             Local
Process  Source   Transaction
Name     Database ID          Error Message
-------- -------- ----------- --------------------------------------------------
APPLY    DBS1.NET 5.4.312     ORA-00001: unique constraint (HR.JOB_ID_PK) 
                              violated

If there are apply errors, then you can either try to reexecute the transactions that encountered the errors, or you can delete the transactions. If you want to reexecute a transaction that encountered an error, then first correct the condition that caused the transaction to raise an error.

If you want to delete a transaction that encountered an error, then you may need to resynchronize data manually if you are sharing data between multiple databases. Remember to set an appropriate session tag, if necessary, when you resynchronize data manually.

See Also:

Displaying Detailed Information About Apply Errors

This section contains SQL scripts that you can use to display detailed information about the error transactions in the exception queues in a database. These scripts are designed to display information about LCR events, but you can extend them to display information about any non-LCR events used in your environment as well.

To use these scripts, complete the following steps:

  1. Grant Explicit SELECT Privilege on the DBA_APPLY_ERROR View
  2. Create a Procedure That Prints the Value in a SYS.AnyData Object
  3. Create a Procedure That Prints a Specified LCR
  4. Create a Procedure That Prints All the LCRs in All Exception Queues
  5. Create a Procedure that Prints All the Error LCRs for a Transaction


    Note:

    These scripts display only the first 255 characters for VARCHAR2 values in LCR events.


Step 1 Grant Explicit SELECT Privilege on the DBA_APPLY_ERROR View

The user who creates and runs the print_errors and print_transaction procedures described in the following sections must be granted explicit SELECT privilege on the DBA_APPLY_ERROR data dictionary view. This privilege cannot be granted through a role.

  1. Connect as an administrative user who can grant privileges.
  2. Grant SELECT privilege on the DBA_APPLY_ERROR data dictionary view to the appropriate user. For example, to grant this privilege to the strmadmin user, run the following statement:
    GRANT SELECT ON DBA_APPLY_ERROR TO strmadmin;
    
    
  3. Connect to the database as the user to whom you granted the privilege in Step 2.
Step 2 Create a Procedure That Prints the Value in a SYS.AnyData Object

The following procedure prints the value in a specified SYS.AnyData object for some selected value types.

CREATE OR REPLACE PROCEDURE print_any(data IN SYS.AnyData) IS
  tn  VARCHAR2(61);
  str VARCHAR2(255);
  chr CHAR(255);
  num NUMBER;
  dat DATE;
  rw  RAW(4000);
  res NUMBER;
BEGIN
  IF data IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('NULL value');
    RETURN;
  END IF;
  tn := data.GETTYPENAME();
  IF tn = 'SYS.VARCHAR2' THEN
    res := data.GETVARCHAR2(str);
    DBMS_OUTPUT.PUT_LINE(str);
  ELSIF tn = 'SYS.CHAR' then
    res := data.GETCHAR(chr);
    DBMS_OUTPUT.PUT_LINE(chr);
  ELSIF tn = 'SYS.VARCHAR' THEN
    res := data.GETVARCHAR(chr);
    DBMS_OUTPUT.PUT_LINE(chr);
  ELSIF tn = 'SYS.NUMBER' THEN
    res := data.GETNUMBER(num);
    DBMS_OUTPUT.PUT_LINE(num);
  ELSIF tn = 'SYS.DATE' THEN
    res := data.GETDATE(dat);
    DBMS_OUTPUT.PUT_LINE(dat);
  ELSIF tn = 'SYS.RAW' THEN
    res := data.GETRAW(rw);
    DBMS_OUTPUT.PUT_LINE(RAWTOHEX(rw));
  ELSE
    DBMS_OUTPUT.PUT_LINE('typename is ' || tn);
  END IF;
END print_any;
/
Step 3 Create a Procedure That Prints a Specified LCR

The following procedure prints a specified LCR. It calls the print_any procedure created in "Create a Procedure That Prints the Value in a SYS.AnyData Object".

CREATE OR REPLACE PROCEDURE print_lcr(lcr IN SYS.ANYDATA) IS
  typenm   VARCHAR2(61);
  ddllcr   SYS.LCR$_DDL_RECORD;
  proclcr  SYS.LCR$_PROCEDURE_RECORD;
  rowlcr   SYS.LCR$_ROW_RECORD;
  res      NUMBER;
  newlist  SYS.LCR$_ROW_LIST;
  oldlist  SYS.LCR$_ROW_LIST;
  ddl_text CLOB;
BEGIN
  typenm := lcr.GETTYPENAME();
  DBMS_OUTPUT.PUT_LINE('type name: ' || typenm);
  IF (typenm = 'SYS.LCR$_DDL_RECORD') THEN
    res := lcr.GETOBJECT(ddllcr);
    DBMS_OUTPUT.PUT_LINE('source database: ' || 
                         ddllcr.GET_SOURCE_DATABASE_NAME);
    DBMS_OUTPUT.PUT_LINE('owner: ' || ddllcr.GET_OBJECT_OWNER);
    DBMS_OUTPUT.PUT_LINE('object: ' || ddllcr.GET_OBJECT_NAME);
    DBMS_OUTPUT.PUT_LINE('is tag null: ' || ddllcr.IS_NULL_TAG);
    DBMS_LOB.CREATETEMPORARY(ddl_text, TRUE);
    ddllcr.GET_DDL_TEXT(ddl_text);
    DBMS_OUTPUT.PUT_LINE('ddl: ' || ddl_text);
    DBMS_LOB.FREETEMPORARY(ddl_text);
  ELSIF (typenm = 'SYS.LCR$_ROW_RECORD') THEN
    res := lcr.GETOBJECT(rowlcr);
    DBMS_OUTPUT.PUT_LINE('source database: ' || 
                         rowlcr.GET_SOURCE_DATABASE_NAME);
    DBMS_OUTPUT.PUT_LINE('owner: ' || rowlcr.GET_OBJECT_OWNER);
    DBMS_OUTPUT.PUT_LINE('object: ' || rowlcr.GET_OBJECT_NAME);
    DBMS_OUTPUT.PUT_LINE('is tag null: ' || rowlcr.IS_NULL_TAG); 
    DBMS_OUTPUT.PUT_LINE('command_type: ' || rowlcr.GET_COMMAND_TYPE); 
    oldlist := rowlcr.GET_VALUES('old');
    FOR i IN 1..oldlist.COUNT LOOP
      IF oldlist(i) IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('old(' || i || '): ' || oldlist(i).column_name);
        print_any(oldlist(i).data);
      END IF;
    END LOOP;
    newlist := rowlcr.GET_VALUES('new', 'n');
    FOR i in 1..newlist.count LOOP
      IF newlist(i) IS NOT NULL THEN
        DBMS_OUTPUT.PUT_LINE('new(' || i || '): ' || newlist(i).column_name);
        print_any(newlist(i).data);
      END IF;
    END LOOP;
    ELSE
      DBMS_OUTPUT.PUT_LINE('Non-LCR Message with type ' || typenm);
  END IF;
END print_lcr;
/
Step 4 Create a Procedure That Prints All the LCRs in All Exception Queues

The following procedure prints all of the LCRs in all of the exception queues in the database. It calls the print_lcr procedure created in "Create a Procedure That Prints a Specified LCR".

CREATE OR REPLACE PROCEDURE print_errors IS
  CURSOR c IS 
    SELECT LOCAL_TRANSACTION_ID, 
           SOURCE_DATABASE, 
           MESSAGE_COUNT, 
           ERROR_NUMBER, 
           ERROR_MESSAGE
      FROM DBA_APPLY_ERROR
      ORDER BY SOURCE_DATABASE, SOURCE_COMMIT_SCN;  
  i      NUMBER;
  txnid  VARCHAR2(30);
  source VARCHAR2(128);
  msgcnt NUMBER;
  errnum NUMBER := 0;
  errno  NUMBER;
  errmsg VARCHAR2(128); 
  lcr    SYS.AnyData;
  r      NUMBER;
BEGIN
  FOR r IN c LOOP
    errnum := errnum + 1;
    msgcnt := r.MESSAGE_COUNT;
    txnid  := r.LOCAL_TRANSACTION_ID;
    source := r.SOURCE_DATABASE;
    errmsg := r.ERROR_MESSAGE;
    errno  := r.ERROR_NUMBER;
    DBMS_OUTPUT.PUT_LINE('*************************************************');
    DBMS_OUTPUT.PUT_LINE('----- ERROR #' || errnum);
    DBMS_OUTPUT.PUT_LINE('----- Local Transaction ID: ' || txnid);
    DBMS_OUTPUT.PUT_LINE('----- Source Database: ' || source);
    DBMS_OUTPUT.PUT_LINE('----Error Number: '||errno);
    DBMS_OUTPUT.PUT_LINE('----Message Text: '||errmsg);
    FOR i IN 1..msgcnt LOOP
      DBMS_OUTPUT.PUT_LINE('--message: ' || i);
        lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid);
        print_lcr(lcr);
    END LOOP;
  END LOOP;
END print_errors;
/

To run this procedure after you create it, enter the following:

SET SERVEROUTPUT ON SIZE 1000000

EXEC print_errors
Step 5 Create a Procedure that Prints All the Error LCRs for a Transaction

The following procedure prints all the LCRs in an exception queue for a particular transaction. It calls the print_lcr procedure created in "Create a Procedure That Prints a Specified LCR".

CREATE OR REPLACE PROCEDURE print_transaction(ltxnid IN VARCHAR2) IS
  i      NUMBER;
  txnid  VARCHAR2(30);
  source VARCHAR2(128);
  msgcnt NUMBER;
  errno  NUMBER;
  errmsg VARCHAR2(128);
  lcr    SYS.ANYDATA;
BEGIN
  SELECT LOCAL_TRANSACTION_ID, 
         SOURCE_DATABASE,
         MESSAGE_COUNT, 
         ERROR_NUMBER, 
         ERROR_MESSAGE
      INTO txnid, source, msgcnt, errno, errmsg
      FROM DBA_APPLY_ERROR 
      WHERE LOCAL_TRANSACTION_ID =  ltxnid;
  DBMS_OUTPUT.PUT_LINE('----- Local Transaction ID: ' || txnid);
  DBMS_OUTPUT.PUT_LINE('----- Source Database: ' || source);
  DBMS_OUTPUT.PUT_LINE('----Error Number: '||errno);
  DBMS_OUTPUT.PUT_LINE('----Message Text: '||errmsg);
  FOR i IN 1..msgcnt LOOP
  DBMS_OUTPUT.PUT_LINE('--message: ' || i);
    lcr := DBMS_APPLY_ADM.GET_ERROR_MESSAGE(i, txnid); -- gets the LCR
    print_lcr(lcr);
  END LOOP;
END print_transaction;
/

To run this procedure after you create it, pass it the local transaction identifier of a error transaction. For example, if the local transaction identifier is 1.17.2485, then enter the following:

SET SERVEROUTPUT ON SIZE 1000000

EXEC print_transaction('1.17.2485')

Monitoring Rules and Rule-Based Transformations

The following sections contain queries that you can run to display information about rules and rule-based transformations:

Displaying the Streams Rules Used by a Streams Process or Propagation

Streams rules are rules created using the DBMS_STREAMS_ADM package for a capture process, propagation, or apply process. These rules determine behavior of the capture process, propagation, or apply process. For example, if a capture rule evaluates to TRUE for DML changes to the hr.employees table, then the capture process captures DML changes to this table.

You query the following data dictionary views to display Streams rules:

For example, the following query displays all of the schema rules for an apply process named strm01_apply:

COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A10
COLUMN SOURCE_DATABASE HEADING 'Source' FORMAT A10
COLUMN RULE_TYPE HEADING 'Rule Type' FORMAT A10
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A10
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A10
COLUMN INCLUDE_TAGGED_LCR HEADING 'Apply|Tagged|LCRs?' FORMAT A15

SELECT SCHEMA_NAME, SOURCE_DATABASE, RULE_TYPE, 
       RULE_NAME, RULE_OWNER, INCLUDE_TAGGED_LCR
  FROM DBA_STREAMS_SCHEMA_RULES 
  WHERE STREAMS_NAME = 'STRM01_APPLY' AND STREAMS_TYPE = 'APPLY';

Your output looks similar to the following:

                                                       Apply
Schema                                                 Tagged
Name       Source     Rule Type  Rule Name  Rule Owner LCRs?
---------- ---------- ---------- ---------- ---------- ---------------
HR         DBS1.NET   DML        HR1        STRMADMIN  NO

These results show that the apply process applies LCRs containing DML changes to the hr schema that originated at the dbs1.net database. The rule in the apply process rule set that instructs the apply process to apply these changes is owned by the strmadmin user and is named hr1. Also, the apply process applies these changes only if the tag in the LCR is NULL.

See Also:

"System-Created Rules"

Displaying the Condition for a Streams Rule

If you know the name and level of a Streams rule, then you can display its rule condition. The level is either global, schema, or table.

For example, consider the rule returned by the query in "Displaying the Streams Rules Used by a Streams Process or Propagation". The name of the Streams schema rule is hr1, and you can display its condition by running the following query:

SELECT RULE_CONDITION "Schema Rule Condition"
  FROM DBA_STREAMS_SCHEMA_RULES 
  WHERE RULE_NAME = 'HR1' AND
        RULE_OWNER = 'STRMADMIN';

Your output looks similar to the following:

Schema Rule Condition
-----------------------------------------------------------------
(:dml.get_object_owner() = 'HR' and :dml.is_null_tag() = 'Y' and 
:dml.get_source_database_name() = 'DBS1.NET' )

See Also:

Displaying the Evaluation Context for Each Rule Set

The following query displays the default evaluation context for each rule set in a database:

COLUMN RULE_SET_OWNER HEADING 'Rule Set|Owner' FORMAT A15
COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A15
COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12
COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A27

SELECT RULE_SET_OWNER, 
       RULE_SET_NAME, 
       RULE_SET_EVAL_CONTEXT_OWNER,
       RULE_SET_EVAL_CONTEXT_NAME
  FROM DBA_RULE_SETS;

Your output looks similar to the following:

Rule Set                        Eval Context
Owner           Rule Set Name   Owner        Eval Context Name
--------------- --------------- ------------ ---------------------------
STRMADMIN       RULESET$_2      SYS          STREAMS$_EVALUATION_CONTEXT
STRMADMIN       STRM02_QUEUE_R  STRMADMIN    AQ$_STRM02_QUEUE_TABLE_V
STRMADMIN       APPLY_OE_RS     STRMADMIN    OE_EVAL_CONTEXT
STRMADMIN       OE_QUEUE_R      STRMADMIN    AQ$_OE_QUEUE_TABLE_V
STRMADMIN       AQ$_1_RE        STRMADMIN    AQ$_OE_QUEUE_TABLE_V
SUPPORT         RS              SUPPORT      EVALCTX

See Also:

"Rule Evaluation Context"

Displaying Information About the Tables Used by an Evaluation Context

The following query displays information about the tables used by an evaluation context named evalctx, which is owned by the support user:

COLUMN TABLE_ALIAS HEADING 'Table Alias' FORMAT A20
COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A40

SELECT TABLE_ALIAS,
       TABLE_NAME
  FROM DBA_EVALUATION_CONTEXT_TABLES
  WHERE EVALUATION_CONTEXT_OWNER = 'SUPPORT' AND
        EVALUATION_CONTEXT_NAME = 'EVALCTX';

Your output looks similar to the following:

Table Alias          Table Name
-------------------- ----------------------------------------
PROB                 problems

See Also:

"Rule Evaluation Context"

Displaying Information About the Variables Used in an Evaluation Context

The following query displays information about the variables used by an evaluation context named evalctx, which is owned by the support user:

COLUMN VARIABLE_NAME HEADING 'Variable Name' FORMAT A15
COLUMN VARIABLE_TYPE HEADING 'Variable Type' FORMAT A15
COLUMN VARIABLE_VALUE_FUNCTION HEADING 'Variable Value|Function' FORMAT A20
COLUMN VARIABLE_METHOD_FUNCTION HEADING 'Variable Method|Function' FORMAT A20

SELECT VARIABLE_NAME,
       VARIABLE_TYPE,
       VARIABLE_VALUE_FUNCTION,
       VARIABLE_METHOD_FUNCTION
  FROM DBA_EVALUATION_CONTEXT_VARS
  WHERE EVALUATION_CONTEXT_OWNER = 'SUPPORT' AND
        EVALUATION_CONTEXT_NAME = 'EVALCTX';

Your output looks similar to the following:

                                Variable Value       Variable Method
Variable Name   Variable Type   Function             Function
--------------- --------------- -------------------- --------------------
CURRENT_TIME    DATE            timefunc

See Also:

"Rule Evaluation Context"

Displaying All of the Rules in a Rule Set

The query in this section displays the following information about all of the rules in a rule set:

For example, to display this information for each rule in a rule set named oe_queue_r that is owned by the user strmadmin, run the following query:

COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A10
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20
COLUMN RULE_EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A27
COLUMN RULE_EVALUATION_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A11

SELECT R.RULE_OWNER, 
       R.RULE_NAME, 
       R.RULE_EVALUATION_CONTEXT_NAME,
       R.RULE_EVALUATION_CONTEXT_OWNER
  FROM DBA_RULES R, DBA_RULE_SET_RULES RS 
  WHERE RS.RULE_SET_OWNER = 'STRMADMIN' AND 
    RS.RULE_SET_NAME = 'OE_QUEUE_R' AND 
  RS.RULE_NAME = R.RULE_NAME AND 
  RS.RULE_OWNER = R.RULE_OWNER;

Your output looks similar to the following:

                                                            Eval Contex
Rule Owner Rule Name            Eval Context Name           Owner
---------- -------------------- --------------------------- -----------
STRMADMIN  HR1                  STREAMS$_EVALUATION_CONTEXT SYS
STRMADMIN  APPLY_LCRS           STREAMS$_EVALUATION_CONTEXT SYS
STRMADMIN  OE_QUEUE$3
STRMADMIN  APPLY_ACTION

Displaying the Condition for Each Rule in a Rule Set

The following query displays the condition for each rule in a rule set named hr_queue_r that is owned by the user strmadmin:

SET LONGCHUNKSIZE 4000
SET LONG 4000
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A15
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15
COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A45

SELECT R.RULE_OWNER, 
       R.RULE_NAME, 
       R.RULE_CONDITION
  FROM DBA_RULES R, DBA_RULE_SET_RULES RS 
  WHERE RS.RULE_SET_OWNER = 'STRMADMIN' AND 
    RS.RULE_SET_NAME = 'HR_QUEUE_R' AND 
  RS.RULE_NAME = R.RULE_NAME AND 
  RS.RULE_OWNER = R.RULE_OWNER;

Your output looks similar to the following:

Rule Owner      Rule Name       Rule Condition
--------------- --------------- ---------------------------------------------
STRMADMIN       APPLY_ACTION     hr.get_hr_action(tab.user_data) = 'APPLY'
STRMADMIN       APPLY_LCRS      :dml.get_object_owner() = 'HR' AND  (:dml.get
                                _object_name() = 'DEPARTMENTS' OR 
                                :dml.get_object_name() = 'EMPLOYEES')

STRMADMIN       HR_QUEUE$3      hr.get_hr_action(tab.user_data) != 'APPLY'

See Also:

Listing Each Rule that Contains a Specified Pattern in Its Condition

To list each rule in a database that contains a specified pattern in its condition, you can query the DBMS_RULES data dictionary view and use the DBMS_LOB.INSTR function to search for the pattern in the rule conditions. For example, the following query lists each rule that contains the pattern 'HR' in its condition:

COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A30
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A30

SELECT RULE_OWNER, RULE_NAME FROM DBA_RULES 
  WHERE DBMS_LOB.INSTR(RULE_CONDITION, 'HR', 1, 1) > 0;

Your output looks similar to the following:

Rule Owner                     Rule Name
------------------------------ ------------------------------
STRMADMIN                      DEPARTMENTS4
STRMADMIN                      DEPARTMENTS5
STRMADMIN                      DEPARTMENTS6

Displaying the Rule-Based Transformations in a Rule Set

In Streams, a rule-based transformation is specified in a rule action context that has the name STREAMS$_TRANSFORM_FUNCTION in the name-value pair. The value in the name-value pair is the name of the PL/SQL procedure that performs the transformation.

The following query displays all of the rule-based transformations specified for rules in a rule set named RULESET$_4:

COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20
COLUMN ACTION_CONTEXT_VALUE HEADING 'Transformation Procedure' FORMAT A40

SELECT 
    r.RULE_NAME,
    ac.NVN_VALUE.ACCESSVARCHAR2() ACTION_CONTEXT_VALUE
  FROM DBA_RULES r, 
       TABLE(R.RULE_ACTION_CONTEXT.ACTX_LIST) ac,
       DBA_RULE_SET_RULES s
  WHERE ac.NVN_NAME      = 'STREAMS$_TRANSFORM_FUNCTION' AND
        s.RULE_SET_NAME  = 'RULESET$_4' AND
        s.RULE_SET_OWNER = 'STRMADMIN' AND
        r.RULE_NAME      = s.RULE_NAME AND
        r.RULE_OWNER     = s.RULE_OWNER;

If there rule-based transformations specified for rules in the rule set, then your output looks similar to the following:

Rule Name            Transformation Procedure
-------------------- ----------------------------------------
DEPARTMENTS7         hr.executive_to_management
DEPARTMENTS6         hr.executive_to_management
DEPARTMENTS5         hr.executive_to_management

See Also:

Monitoring Streams Tags

The following sections contain queries that you can run to display the Streams tag for the current session and for an apply process:

Displaying the Tag Value for the Current Session

You can display the tag value generated in all redo entries for the current session by querying the DUAL view:

SELECT DBMS_STREAMS.GET_TAG FROM DUAL;

Your output looks similar to the following:

GET_TAG
--------------------------------------------------------------------------------
1D

You can also determine the tag for a session by calling the DBMS_STREAMS.GET_TAG function.

Displaying the Tag Value for an Apply Process

You can get the default tag for all redo entries generated by an apply process by querying for the APPLY_TAG value in the DBA_APPLY data dictionary view. For example, to get the hexadecimal value of the tags generated in the redo entries by an apply process named strm01_apply, run the following query:

SELECT APPLY_TAG "Tag Value for strm01_apply" 
  FROM DBA_APPLY WHERE APPLY_NAME = 'STRM01_APPLY';

Your output looks similar to the following:

Tag Value for strm01_apply
--------------------------------------------------------------------------------
00

A handler or transformation function associated with an apply process can get the tag by calling the DBMS_STREAMS.GET_TAG function.