16
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
Summary of Streams Dynamic Performance Views
Monitoring a Streams Capture Process
Monitoring a Streams Queue
Monitoring a Streams Propagation Job
Monitoring a Streams Apply Process
Monitoring Rules and Rule-Based Transformations
Monitoring Streams Tags

Note:
The Streams tool in Oracle Enterprise Manager is also an excellent way to monitor a Streams environment. See the online help for the Streams tool for more information.

See Also:
Oracle9i Database Reference for information about the data dictionary views described in this chapter

Summary of Streams Static Data Dictionary Views

The following table lists the Streams static data dictionary views.

Table 16-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:

The capture process name
The name of the queue used by the capture process
The name of the rule set used by the capture process
The status of the capture process, which may be ENABLE, DISABLED, or ABORTED

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

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

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:

The process number (cpnn)
The session identifier
The serial number of the session
The current state of the capture process, either INITIALIZING, CAPTURING CHANGES, EVALUATING RULE, ENQUEUING MESSAGE, SHUTTING DOWN, or CREATING LCR
The total number of redo entries scanned
The total number LCRs enqueued

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 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 redo log scanning latency, which specifies the number of seconds between the creation time of the most recent redo log event scanned by a capture process and the current time. This number may be relatively large immediately after you start a capture process.
The seconds since last recorded status, which is the number of seconds since a capture process last recorded its status
The current capture process time, which is the latest time when the capture process recorded its status
The event creation time, which is the time when the data manipulation language (DML) or data definition language (DDL) change generated the redo information for the most recently captured event

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 event enqueuing latency, which specifies the number of seconds between when an event was recorded in the redo log and when the event was enqueued by the capture process
The event creation time, which is the time when the data manipulation language (DML) or data definition language (DDL) change generated the redo information for the most recently enqueued event
The enqueue time, which is when the capture process enqueued the event into its queue
The message number of the enqueued event

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:

PREPARE_TABLE_INSTANTIATION prepares a single table for instantiation.
PREPARE_SCHEMA_INSTANTIATION prepares all of the database objects in a schema for instantiation.
PREPARE_GLOBAL_INSTANTIATION prepares all of the database objects in a database for instantiation.

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

DBA_CAPTURE_PREPARED_TABLES
DBA_CAPTURE_PREPARED_SCHEMAS
DBA_CAPTURE_PREPARED_DATABASE

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

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 18, "Example Streams Messaging Environment" 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.

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')

See Also:

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

Monitoring a Streams Propagation Job

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

Determining the Source Queue and Destination Queue for a Propagation Job

You can determine the source queue and destination queue for a propagation job 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 job named dbs1_to_dbs2:

The source queue owner
The source queue name
The database that contains the source queue
The destination queue owner
The destination queue name

The database link used by the propagation job

COLUMN "Source Queue" FORMAT A35
COLUMN "Destination Queue" FORMAT A35

SELECT p.SOURCE_QUEUE_OWNER ||'.'|| 
       p.SOURCE_QUEUE_NAME ||'@'|| 
       g.GLOBAL_NAME "Source Queue", 
       p.DESTINATION_QUEUE_OWNER ||'.'|| 
       p.DESTINATION_QUEUE_NAME ||'@'|| 
       p.DESTINATION_DBLINK "Destination Queue"
  FROM DBA_PROPAGATION p, GLOBAL_NAME g
  WHERE PROPAGATION_NAME = '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 Job

The following query displays the owner and name of a rule set used by a propagation job 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 named dbs1_to_dbs2:

The date and time when the propagation schedule started (or will start)
The duration of the propagation job, which is the amount of time the job propagates events before restarting
The latency of the propagation job, which is the maximum wait time to propagate a new message during the duration, when all other messages in the queue to the relevant destination have been propagated
Whether or not the propagation job is enabled
The name of the process that most recently executed the schedule
The number of consecutive times schedule execution has failed, if any. After 16 consecutive failures, a propagation job becomes disabled automatically.

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, 
       TO_CHAR(s.NEXT_TIME, 'HH24:MI:SS MM/DD/YY') NEXT_TIME, 
       s.LATENCY,
       DECODE(s.SCHEDULE_DISABLED,
                'Y', 'Disabled',
                'N', 'Enabled') SCHEDULE_DISABLED,
       PROCESS_NAME,
       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:

"Propagation Scheduling" for more information about the default propagation schedule for a Streams propagation job
"Is the Propagation Job Enabled?" if the propagation job is disabled
Oracle9i Application Developer's Guide - Advanced Queuing and Oracle9i Database Reference for more information about the DBA_QUEUE_SCHEDULES data dictionary view

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:

The total time spent by the system executing the propagation schedule
The total number of events propagated by the propagation schedule
The total number of bytes propagated by the propagation schedule

For example, to display this information for a propagation job 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:

The apply process name
The name of the queue used by the apply process
The name of the rule set used by the apply process
The type of events applied by the apply process. An apply process may apply either events that were captured by a capture process or events that were enqueued by a user or application.
The status of the apply process, which may be ENABLED, DISABLED, or ABORTED

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

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

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                                Apply
Process  Process              Process              Type of         Process
Name     Queue                Rule Set             Events 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:

See Also:

Displaying Information About Apply Handlers

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

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

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 type of events dequeued by the reader server, either captured LCRs or user-enqueued messages
The name of the parallel execution server used by the reader server
The current state of the reader server, either IDLE, DEQUEUE MESSAGES, or SCHEDULE MESSAGES
The total number of events dequeued by the reader server since the last time the apply process was started

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 amount of time between when the event was created at a source database and when the event was dequeued by the apply process
The event creation time. For captured events, the event creation time is the time when the data manipulation language (DML) or data definition language (DDL) change generated the redo information at the source database for the event. For user-enqueued events, the event creation time is the last time the event was enqueued. A user-enqueued event may be enqueued one or more additional times by propagation before it reaches an apply process.
The time when the event was dequeued by the apply process
The message number of the event that was last dequeued by the apply process

Here, the latency is the amount of time required for an event recently dequeued by the apply process to be captured by a capture process, propagated to the destination database, and dequeued by an 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 number of the coordinator in the process name (apnn)
The session identifier of the coordinator's session
The serial number of the coordinator's session
The current state of the coordinator, either INITIALIZING, APPLYING, SHUTTING DOWN CLEANLY, or ABORTING
The total number of transactions received by the coordinator process since the apply process was last started
The total number of transactions successfully applied by the apply process since the apply process was last started
The number of transactions applied by the apply process that resulted in an apply error since the apply process was last started

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, 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. The following are the major differences between these two queries:

The apply process must be enabled when you run the query on the V$STREAMS_APPLY_COORDINATOR view, while the apply process can be enabled or disabled when you run the query on the DBA_APPLY_PROGRESS view.
The query on the V$STREAMS_APPLY_COORDINATOR view may show the latency for a more recent transaction than the query on the DBA_APPLY_PROGRESS view.

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

The capture to apply latency for the event
The event creation time. For captured events, the event creation time is the time when the data manipulation language (DML) or data definition language (DDL) change generated the redo information at the source database for the event. For user-enqueued events, the event creation time is the last time the event was enqueued. A user-enqueued event may be enqueued one or more additional times by propagation before it reaches an apply process.
The time when the event was applied by the apply process
The message number of the event

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 type of events applied by each apply server, either captured LCRs or user-enqueued messages
The process names of the parallel execution servers, in order
The current state of each apply server, either IDLE, POLL SHUTDOWN, RECORD LOW-WATERMARK, ADD PARTITION, DROP PARTITION, EXECUTE TRANSACTION, WAIT COMMIT, WAIT DEPENDENCY, GET TRANSACTIONS, or WAIT FOR NEXT CHUNK
The total number of transactions assigned to each apply server since the last time the apply process was started. A transaction may contain more than one event.
The total number of events applied by each apply server since the last time the apply process was started

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 from the error queue. 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:

"The Error Queue"
"Managing Apply Errors"
"Considerations for Applying DML Changes to Tables" for information about the possible causes of apply errors
"Managing Streams Tags for the Current Session"

Displaying Detailed Information About Apply Errors

This section contains SQL scripts that you can use to display detailed information about the error transactions in an error queue. 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.

Note:

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.

Creating a Procedure That Prints the Value in a Specified 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(4000);
  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;
/

Creating a Procedure That Prints a Specified LCR

The following procedure prints a specified LCR. It calls the print_any procedure created in "Creating a Procedure That Prints the Value in a Specified 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');
    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;
/

Creating a Procedure That Prints All the LCRs in the Error Queue

The following procedure prints all of the LCRs in the error queue. It calls the print_lcr procedure created in "Creating 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

Creating a Procedure that Prints All the Error LCRs for a Transaction

The following procedure prints all the LCRs in the error queue for a particular transaction. It calls the print_lcr procedure created in "Creating 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 transaction in the error queue. For example, if the local transaction identifier is 1.17.2485, then enter the following:

SET SERVEROUTPUT ON SIZE 1000000

EXEC print_transaction('1.6.2753')

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 Job

Streams rules are rules created using the DBMS_STREAMS_ADM package for a capture process, propagation job, or apply process. These rules determine behavior of the process or job. 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:

ALL_STREAMS_GLOBAL_RULES
DBA_STREAMS_GLOBAL_RULES
ALL_STREAMS_SCHEMA_RULES
DBA_STREAMS_SCHEMA_RULES
ALL_STREAMS_TABLE_RULES

DBA_STREAMS_TABLE_RULES

Note:

These views display only rules created using the DBMS_STREAMS_ADM package or the Streams tool in Oracle Enterprise Manager. These views do not display any manual modifications to these rules made by the DBMS_RULE_ADM package, nor do they display rules created using the DBMS_RULE_ADM package.

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 Job". 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 A20
COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A11
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 Contex
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 A20

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:

The owner of the rule
The name of the rule
The evaluation context for the rule, if any. If a rule does not have an evaluation context, and no evaluation context is specified in the ADD_RULE procedure when the rule is added to a rule set, then it inherits the evaluation context of the rule set
The evaluation context owner, if the rule has an evaluation context

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 oe_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 = 'OE_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     oe.get_oe_action(tab.user_data) = 'APPLY'
STRMADMIN       APPLY_LCRS      :dml.get_object_owner() = 'OE' AND  (:dml.get
                                _object_name() = 'ORDERS' OR :dml.get_object_
                                name() = 'CUSTOMERS')

STRMADMIN       OE_QUEUE$3      oe.get_oe_action(tab.user_data) != 'APPLY'

See Also:

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
Displaying the Tag Value for an Apply Process
See Also:

Chapter 8, "Streams Tags"

"Managing Streams Tags"

Oracle9i Supplied PL/SQL Packages and Types Reference for more information about the DBMS_STREAMS package

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 apply_oe, run the following query:

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

Your output looks similar to the following:

Tag Value for APPLY_OE
--------------------------------------------------------------------------------
00

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

16 Monitoring a Streams Environment

Summary of Streams Static Data Dictionary Views

Table 16-1 Streams Static Data Dictionary Views

Summary of Streams Dynamic Performance Views

Monitoring a Streams Capture Process

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

Displaying General Information About a Capture Process

Listing the Parameter Settings for a Capture Process

Determining Redo Log Scanning Latency for a Capture Process

Determining Event Enqueuing Latency for a Capture Process

Determining Which Database Objects Are Prepared for Instantiation

Displaying Supplemental Log Groups at a Source Database

Monitoring a Streams Queue

Displaying the Streams Queues in a Database

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

Viewing the Contents of User-Enqueued Events in a Queue

Monitoring a Streams Propagation Job

Determining the Source Queue and Destination Queue for a Propagation Job

Determining the Rule Set for a Propagation Job

Displaying the Schedule for a Propagation Job

Determining the Total Number of Events and Bytes Propagated

Monitoring a Streams Apply Process

Displaying General Information About Each Apply Process

Listing the Parameter Settings for an Apply Process

Displaying Information About Apply Handlers

Displaying All of the DML and Error Handlers for Local Apply

Displaying the DDL Handler and Message Handler for Each Apply Process

Displaying the Substitute Key Columns Specified at a Destination Database

Displaying Information About Update Conflict Handlers for a Destination Database

Determining the Tables for Which an Instantiation SCN Has Been Set

Displaying Information About the Reader Server for an Apply Process

Determining Capture to Dequeue Latency for an Event

Displaying Information About the Coordinator Process

Determining the Capture to Apply Latency for an Event

Example V$STREAMS_APPLY_COORDINATOR Query for Latency

Example DBA_APPLY_PROGRESS Query for Latency

Displaying Information About the Apply Servers for an Apply Process

Displaying Effective Apply Parallelism for an Apply Process

Checking for Apply Errors

Displaying Detailed Information About Apply Errors

Creating a Procedure That Prints the Value in a Specified SYS.AnyData Object

Creating a Procedure That Prints a Specified LCR

Creating a Procedure That Prints All the LCRs in the Error Queue

Creating a Procedure that Prints All the Error LCRs for a Transaction

Monitoring Rules and Rule-Based Transformations

Displaying the Streams Rules Used by a Streams Process or Job

Displaying the Condition for a Streams Rule

Displaying the Evaluation Context for Each Rule Set

Displaying Information About the Tables Used by an Evaluation Context

Displaying Information About the Variables Used in an Evaluation Context

Displaying All of the Rules in a Rule Set

Displaying the Condition for Each Rule in a Rule Set

Displaying the Rule-Based Transformations in a Rule Set

Monitoring Streams Tags

Displaying the Tag Value for the Current Session

Displaying the Tag Value for an Apply Process

16
Monitoring a Streams Environment