25 Monitoring Oracle Streams Queues and Propagations

Displaying the ANYDATA Queues in a Database

To display all of the ANYDATA queues in a database, run the following query:

COLUMN OWNER HEADING 'Owner' FORMAT A10
COLUMN NAME HEADING 'Queue Name' FORMAT A28
COLUMN QUEUE_TABLE HEADING 'Queue Table' FORMAT A22
COLUMN USER_COMMENT HEADING 'Comment' FORMAT A15

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  DB$APPQ                      DB$APPQT
STRMADMIN  AQ$_DB$APPQT_E               DB$APPQT               exception queue
STRMADMIN  DA$CAPQ                      DA$CAPQT
STRMADMIN  AQ$_DA$CAPQT_E               DA$CAPQT               exception queue
IX         STREAMS_QUEUE                STREAMS_QUEUE_TABLE
IX         AQ$_STREAMS_QUEUE_TABLE_E    STREAMS_QUEUE_TABLE    exception queue

An exception queue is created automatically when you create an ANYDATA queue.

Viewing the Messaging Clients in a Database

You can view the messaging clients in a database by querying the DBA_STREAMS_MESSAGE_CONSUMERS data dictionary view. The query in this section displays the following information about each messaging client:

• The name of the messaging client

• The queue used by the messaging client

• The positive rule set used by the messaging client

• The negative rule set used by the messaging client

Run the following query to view this information about messaging clients:

COLUMN STREAMS_NAME HEADING 'Messaging|Client' FORMAT A25
COLUMN QUEUE_OWNER HEADING 'Queue|Owner' FORMAT A10
COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A18
COLUMN RULE_SET_NAME HEADING 'Positive|Rule Set' FORMAT A11
COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative|Rule Set' FORMAT A11

SELECT STREAMS_NAME, 
       QUEUE_OWNER, 
       QUEUE_NAME, 
       RULE_SET_NAME, 
       NEGATIVE_RULE_SET_NAME 
  FROM DBA_STREAMS_MESSAGE_CONSUMERS;

Your output looks similar to the following:

Messaging                 Queue                         Positive    Negative
Client                    Owner      Queue Name         Rule Set    Rule Set
------------------------- ---------- ------------------ ----------- -----------
SCHEDULER_PICKUP          SYS        SCHEDULER$_JOBQ    RULESET$_8
SCHEDULER_COORDINATOR     SYS        SCHEDULER$_JOBQ    RULESET$_4
HR                        STRMADMIN  STREAMS_QUEUE      RULESET$_15

Viewing Message Notifications

You can configure a message notification to send a notification when a message that can be dequeued by a messaging client is enqueued into a queue. The notification can be sent to an e-mail address, to an HTTP URL, or to a PL/SQL procedure. Run the following query to view the message notifications configured in a database:

COLUMN STREAMS_NAME HEADING 'Messaging|Client' FORMAT A10
COLUMN QUEUE_OWNER HEADING 'Queue|Owner' FORMAT A5
COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A20
COLUMN NOTIFICATION_TYPE HEADING 'Notification|Type' FORMAT A15
COLUMN NOTIFICATION_ACTION HEADING 'Notification|Action' FORMAT A25

SELECT STREAMS_NAME, 
       QUEUE_OWNER, 
       QUEUE_NAME, 
       NOTIFICATION_TYPE, 
       NOTIFICATION_ACTION 
  FROM DBA_STREAMS_MESSAGE_CONSUMERS
  WHERE NOTIFICATION_TYPE IS NOT NULL;

Your output looks similar to the following:

Messaging  Queue                      Notification    Notification
Client     Owner Queue Name           Type            Action
---------- ----- -------------------- --------------- -------------------------
OE         OE    NOTIFICATION_QUEUE   MAIL            mary.smith@example.com

Determining the Consumer of Each Message in a Persistent Queue

To determine the consumer for each message in a persistent 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 messages in the oe_q_table_any 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_Q_TABLE_ANY;

Your output looks similar to the following:

Message ID                       Message State Consumer
-------------------------------- ------------- ------------------------------
B79AC412AE6E08CAE034080020AE3E0A PROCESSED     OE
B79AC412AE6F08CAE034080020AE3E0A PROCESSED     OE
B79AC412AE7008CAE034080020AE3E0A PROCESSED     OE

Viewing the Contents of Messages in a Persistent Queue

In an ANYDATA queue, to view the contents of a payload that is encapsulated within an ANYDATA payload, you query the queue table using the Accessdata_type static functions of the 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 data type, 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, create a function similar to the following:

CREATE OR REPLACE FUNCTION oe.view_cust_address_typ(
in_any IN 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;

Query the queue table using the function, as in the following example:

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

Determining the Number of Messages in Each Buffered Queue

The V$BUFFERED_QUEUES dynamic performance view contains information about the number of messages in a buffered queue. The messages can be captured LCRs, buffered LCRs, or buffered user messages.

You can determine the following information about each buffered queue in a database by running the query in this section:

• The queue owner

• The queue name

• The number of messages currently in memory

• The number of messages that have spilled from memory into the queue table

• The total number of messages in the buffered queue, which includes the messages in memory and the messages spilled to the queue table

To display this information, run the following query:

COLUMN QUEUE_SCHEMA HEADING 'Queue Owner' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A15
COLUMN MEM_MSG HEADING 'Messages|in Memory' FORMAT 99999999
COLUMN SPILL_MSGS HEADING 'Messages|Spilled' FORMAT 99999999
COLUMN NUM_MSGS HEADING 'Total Messages|in Buffered Queue' FORMAT 99999999

SELECT QUEUE_SCHEMA, 
       QUEUE_NAME, 
       (NUM_MSGS - SPILL_MSGS) MEM_MSG, 
       SPILL_MSGS, 
       NUM_MSGS
  FROM V$BUFFERED_QUEUES;

Your output looks similar to the following:

Messages      Messages      Total Messages
Queue Owner     Queue Name          in Memory       Spilled   in Buffered Queue
--------------- --------------- ------------- ------------- -------------------
STRMADMIN       STREAMS_QUEUE             534            21                 555

Viewing the Capture Processes for the LCRs in Each Buffered Queue

A capture process is a queue publisher that enqueues captured LCRs into a buffered queue. These LCRs can be propagated to other queues subsequently. By querying the V$BUFFERED_PUBLISHERS dynamic performance view, you can display each capture process that captured the LCRs in the buffered queue. These LCRs might have been captured at the local database, or they might have been captured at a remote database and propagated to the queue specified in the query.

The query in this section assumes that the buffered queues in the local database only store captured LCRs, not buffered LCRs or buffered user messages. The query displays the following information about each capture process:

• The name of a capture process that captured the LCRs in the buffered queue

• If the capture process is running on a remote database, and the captured LCRs have been propagated to the local queue, then the name of the queue and database from which the captured LCRs were last propagated

• The name of the local queue staging the captured LCRs

• The total number of LCRs captured by a capture process that have been staged in the buffered queue since the database instance was last started

• The message number of the LCR last enqueued into the buffered queue from the sender

• The percentage of the Streams pool used at the capture process database

• The state of the publisher. The capture process is the publisher, and the following states are possible:

  • PUBLISHING MESSAGES

  • IN FLOW CONTROL: TOO MANY UNBROWSED MESSAGES

  • IN FLOW CONTROL: OVERSPILLED MESSAGES

  • IN FLOW CONTROL: INSUFFICIENT MEMORY AND UNBROWSED MESSAGES

To display this information, run the following query:

COLUMN SENDER_NAME HEADING 'Capture|Process' FORMAT A10
COLUMN SENDER_ADDRESS HEADING 'Sender Queue' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A10
COLUMN CNUM_MSGS HEADING 'Number|of LCRs|Enqueued' FORMAT 99999999
COLUMN LAST_ENQUEUED_MSG HEADING 'Last|Enqueued|LCR' FORMAT 9999999999
COLUMN MEMORY_USAGE HEADING 'Percent|Streams|Pool|Used' FORMAT 999
COLUMN PUBLISHER_STATE HEADING 'Publisher|State' FORMAT A10
 
SELECT SENDER_NAME,
       SENDER_ADDRESS,
       QUEUE_NAME,        
       CNUM_MSGS, 
       LAST_ENQUEUED_MSG,
       MEMORY_USAGE,
       PUBLISHER_STATE
  FROM V$BUFFERED_PUBLISHERS;

Your output looks similar to the following:

Percent
                                         Number        Last Streams
Capture                                 of LCRs    Enqueued    Pool Publisher
Process    Sender Queue    Queue Name  Enqueued         LCR    Used State
---------- --------------- ---------- --------- ----------- ------- ----------
DB1$CAP                    DB1$CAPQ        3670     1002253      21 PUBLISHING
                                                                     MESSAGES
 
DB2$CAP    "STRMADMIN"."DB DB2$APPQ        3427      981066      21 PUBLISHING
           2$CAPQ"@DB2.EXA                                           MESSAGES
           MPLE.COM

This output shows following:

• 3670 LCRs from the local db1$cap capture process were enqueued into the local queue named db1$capq. The capture process is local because the Sender Queue column is NULL. The message number of the last enqueued LCR from this capture process was 1002253. 21% of the Streams pool is used at the capture process database, and the capture process is publishing messages normally.

• 3427 LCRs from the db2$cap capture process running on a remote database were propagated from a queue named db2$capq on database db2.example.com to the local queue named db2$appq. The message number of the last enqueued LCR from this sender was 961066. 21% of the Streams pool is used at the remote capture process database, and the capture process is publishing messages normally.

Displaying Information About Propagations that Send Buffered Messages

The query in this section displays the following information about each propagation that sends buffered messages from a buffered queue in the local database:

• The name of the propagation

• The queue owner

• The queue name

• The name of the database link used by the propagation

• The status of the propagation schedule

To display this information, run the following query:

COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15
COLUMN QUEUE_SCHEMA HEADING 'Queue|Owner' FORMAT A10
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A15
COLUMN DBLINK HEADING 'Database|Link' FORMAT A10
COLUMN SCHEDULE_STATUS HEADING 'Schedule Status' FORMAT A20

SELECT p.PROPAGATION_NAME,
       s.QUEUE_SCHEMA,
       s.QUEUE_NAME,
       s.DBLINK,
       s.SCHEDULE_STATUS
  FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
  WHERE p.SOURCE_QUEUE_OWNER      = s.QUEUE_SCHEMA AND
        p.SOURCE_QUEUE_NAME       = s.QUEUE_NAME AND
        p.DESTINATION_QUEUE_OWNER = s.DST_QUEUE_SCHEMA AND
        p.DESTINATION_QUEUE_NAME  = s.DST_QUEUE_NAME;

Your output looks similar to the following:

Queue      Queue           Database
Propagation     Owner      Name            Link       Schedule Status
--------------- ---------- --------------- ---------- --------------------
PROPAGATION$_6  STRMADMIN  DB1$CAPQ        "STRMADMIN SCHEDULE OPTIMIZED
                                           "."DB1$APP
                                           Q"@DB2.EXA
                                           MPLE.COM

When the SCHEDULE_STATUS column in the V$PROPAGATION_SENDER view shows SCHEDULE OPTIMIZED for a propagation, it means that the propagation is part of a combined capture and apply optimization.

Displaying the Number of Messages and Bytes Sent By Propagations

The query in this section displays the number of messages and the number of bytes sent by each propagation that sends buffered messages from a buffered queue in the local database:

• The name of the propagation

• The queue name

• The name of the database link used by the propagation

• The total number of messages sent since the database instance was last started

• The total number of bytes sent since the database instance was last started

To display this information, run the following query:

COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A15
COLUMN DBLINK HEADING 'Database|Link' FORMAT A20
COLUMN TOTAL_MSGS HEADING 'Total|Messages' FORMAT 99999999
COLUMN TOTAL_BYTES HEADING 'Total|Bytes' FORMAT 999999999999

SELECT p.PROPAGATION_NAME,
       s.QUEUE_NAME,
       s.DBLINK,
       s.TOTAL_MSGS,
       s.TOTAL_BYTES
  FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
  WHERE p.SOURCE_QUEUE_OWNER      = s.QUEUE_SCHEMA AND
        p.SOURCE_QUEUE_NAME       = s.QUEUE_NAME AND
        p.DESTINATION_QUEUE_OWNER = s.DST_QUEUE_SCHEMA AND
        p.DESTINATION_QUEUE_NAME  = s.DST_QUEUE_NAME;

Your output looks similar to the following:

Queue           Database            Total     Total
Propagation     Name            Link                 Messages     Bytes
--------------- --------------- -------------------- --------- ---------
MULT1_TO_MULT3  STREAMS_QUEUE   MULT3.EXAMPLE.COM           79     71467
MULT1_TO_MULT2  STREAMS_QUEUE   MULT2.EXAMPLE.COM           79     71467

Displaying Performance Statistics for Propagations that Send Buffered Messages

The query in this section displays the amount of time that a propagation sending buffered messages spends performing various tasks. Each propagation sends messages from the source queue to the destination queue. Specifically, the query displays the following information:

• The name of the propagation

• The queue name

• The name of the database link used by the propagation

• The amount of time spent dequeuing messages from the queue since the database instance was last started, in seconds

• The amount of time spent pickling messages since the database instance was last started, in seconds. Pickling involves changing a message in memory into a series of bytes that can be sent over a network.

• The amount of time spent propagating messages since the database instance was last started, in seconds

To display this information, run the following query:

COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A13
COLUMN DBLINK HEADING 'Database|Link' FORMAT A9
COLUMN ELAPSED_DEQUEUE_TIME HEADING 'Dequeue|Time' FORMAT 99999999.99
COLUMN ELAPSED_PICKLE_TIME HEADING 'Pickle|Time' FORMAT 99999999.99
COLUMN ELAPSED_PROPAGATION_TIME HEADING 'Propagation|Time' FORMAT 99999999.99

SELECT p.PROPAGATION_NAME,
       s.QUEUE_NAME,
       s.DBLINK,
       (s.ELAPSED_DEQUEUE_TIME / 100) ELAPSED_DEQUEUE_TIME,
       (s.ELAPSED_PICKLE_TIME / 100) ELAPSED_PICKLE_TIME,
       (s.ELAPSED_PROPAGATION_TIME / 100) ELAPSED_PROPAGATION_TIME
  FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
  WHERE p.SOURCE_QUEUE_OWNER      = s.QUEUE_SCHEMA AND
        p.SOURCE_QUEUE_NAME       = s.QUEUE_NAME AND
        p.DESTINATION_QUEUE_OWNER = s.DST_QUEUE_SCHEMA AND
        p.DESTINATION_QUEUE_NAME  = s.DST_QUEUE_NAME;

Your output looks similar to the following:

Queue         Database       Dequeue       Pickle  Propagation
Propagation     Name          Link              Time         Time         Time
--------------- ------------- --------- ------------ ------------ ------------
MULT1_TO_MULT2  STREAMS_QUEUE MULT2.EXA        30.65        45.10        10.91
                              MPLE.COM
MULT1_TO_MULT3  STREAMS_QUEUE MULT3.EXA        25.36        37.07         8.35
                              MPLE.COM

Viewing the Propagations Dequeuing Messages from Each Buffered Queue

Propagations are queue subscribers that can dequeue messages. By querying the V$BUFFERED_SUBSCRIBERS dynamic performance view, you can display all the propagations that can dequeue buffered messages.

Apply processes also are queue subscribers. This query joins with the DBA_PROPAGATION and V$BUFFERED_QUEUES views to limit the output to propagations only and to show the propagation name of each propagation.

The query in this section displays the following information about each propagation that can dequeue messages from queues:

• The name of the propagation.

• The owner and name of the queue to which the propagation subscribes

  This queue is the source queue for the propagation.

• The subscriber address

  For a propagation, the subscriber address is the propagation's destination queue and destination database

• The time when the propagation last started

• The cumulative number of messages dequeued by the propagation since the database last started

• The total number of messages dequeued by the propagation since the propagation last started

• The message number of the message most recently dequeued by the propagation

To display this information, run the following query:

COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A11
COLUMN QUEUE_SCHEMA HEADING 'Queue|Owner' FORMAT A5
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A5
COLUMN SUBSCRIBER_ADDRESS HEADING 'Subscriber|Address' FORMAT A15
COLUMN STARTUP_TIME HEADING 'Startup|Time' FORMAT A9
COLUMN CNUM_MSGS HEADING 'Cumulative|Messages' FORMAT 99999999
COLUMN TOTAL_DEQUEUED_MSG HEADING 'Total|Messages' FORMAT 99999999
COLUMN LAST_DEQUEUED_NUM HEADING 'Last|Dequeued|Message|Number' FORMAT 99999999
 
SELECT p.PROPAGATION_NAME,
       s.QUEUE_SCHEMA,
       s.QUEUE_NAME,
       s.SUBSCRIBER_ADDRESS,
       s.STARTUP_TIME,
       s.CNUM_MSGS,          
       s.TOTAL_DEQUEUED_MSG,
       s.LAST_DEQUEUED_NUM
FROM DBA_PROPAGATION p, V$BUFFERED_SUBSCRIBERS s
WHERE p.SOURCE_QUEUE_OWNER = s.QUEUE_SCHEMA AND
      p.SOURCE_QUEUE_NAME  = s.QUEUE_NAME AND 
      p.PROPAGATION_NAME   = s.SUBSCRIBER_NAME AND
      s.SUBSCRIBER_ADDRESS LIKE '%' || p.DESTINATION_DBLINK;

Your output looks similar to the following:

Last
                                                                        Dequeued
            Queue Queue Subscriber      Startup   Cumulative     Total   Message
Propagation Owner Name  Address         Time        Messages  Messages    Number
----------- ----- ----- --------------- --------- ---------- --------- ---------
PROPAGATION STRMA DB1$C "STRMADMIN"."DB 25-JUN-09      11079     11079   1525762
$_5         DMIN  APQ   1$APPQ"@DB2.EXA
                        MPLE.COM

Displaying Performance Statistics for Propagations That Receive Buffered Messages

The query in this section displays the amount of time that each propagation receiving buffered messages spends performing various tasks. Each propagation receives the messages and enqueues them into the destination queue for the propagation. Specifically, the query displays the following information:

• The name of the source queue from which messages are propagated.

• The name of the source database.

• The amount of time spent unpickling messages since the database instance was last started, in seconds. Unpickling involves changing a series of bytes that can be sent over a network back into a buffered message in memory.

• The amount of time spent evaluating rules for propagated messages since the database instance was last started, in seconds.

• The amount of time spent enqueuing messages into the destination queue for the propagation since the database instance was last started, in seconds.

To display this information, run the following query:

COLUMN SRC_QUEUE_NAME HEADING 'Source|Queue|Name' FORMAT A20
COLUMN SRC_DBNAME HEADING 'Source|Database' FORMAT A20
COLUMN ELAPSED_UNPICKLE_TIME HEADING 'Unpickle|Time' FORMAT 99999999.99
COLUMN ELAPSED_RULE_TIME HEADING 'Rule|Evaluation|Time' FORMAT 99999999.99
COLUMN ELAPSED_ENQUEUE_TIME HEADING 'Enqueue|Time' FORMAT 99999999.99

SELECT SRC_QUEUE_NAME,
       SRC_DBNAME,
       (ELAPSED_UNPICKLE_TIME / 100) ELAPSED_UNPICKLE_TIME,
       (ELAPSED_RULE_TIME / 100) ELAPSED_RULE_TIME,
       (ELAPSED_ENQUEUE_TIME / 100) ELAPSED_ENQUEUE_TIME
  FROM V$PROPAGATION_RECEIVER;

Your output looks similar to the following:

Source                                                    Rule
Queue                Source              Unpickle   Evaluation      Enqueue
Name                 Database                Time         Time         Time
-------------------- -------------------- ------------ ------------ ------------
STREAMS_QUEUE        MULT2.EXAMPLE.COM           45.65         5.44        45.85
STREAMS_QUEUE        MULT3.EXAMPLE.COM           53.35         8.01        50.41

Viewing the Apply Processes Dequeuing Messages from Each Buffered Queue

Apply processes are queue subscribers that can dequeue messages. By querying the V$BUFFERED_SUBSCRIBERS dynamic performance view, you can display all the apply processes that can dequeue messages.

This query joins with the V$BUFFERED_QUEUES views to show the name of the queue. In addition, propagations also are queue subscribers, and this query limits the output to subscribers where the SUBSCRIBER_ADDRESS is NULL to return only apply processes.

The query in this section displays the following information about the apply processes that can dequeue messages from queues:

• The name of the apply process

• The queue owner

• The queue name

• The time when the apply process last started

• The cumulative number of messages dequeued by the apply process since the database last started

• The total number of messages dequeued by the apply process since the apply process last started

• The message number of the message most recently dequeued by the apply process

To display this information, run the following query:

COLUMN SUBSCRIBER_NAME HEADING 'Apply Process' FORMAT A16
COLUMN QUEUE_SCHEMA HEADING 'Queue|Owner' FORMAT A5
COLUMN QUEUE_NAME HEADING 'Queue|Name' FORMAT A5
COLUMN STARTUP_TIME HEADING 'Startup|Time' FORMAT A9
COLUMN CNUM_MSGS HEADING 'Cumulative|Messages' FORMAT 99999999
COLUMN TOTAL_DEQUEUED_MSG HEADING 'Number of|Dequeued|Messages' 
  FORMAT 99999999
COLUMN LAST_DEQUEUED_NUM HEADING 'Last|Dequeued|Message|Number' FORMAT 99999999

SELECT s.SUBSCRIBER_NAME,
       q.QUEUE_SCHEMA,
       q.QUEUE_NAME, 
       s.STARTUP_TIME,
       s.CNUM_MSGS,          
       s.TOTAL_DEQUEUED_MSG,
       s.LAST_DEQUEUED_NUM
FROM V$BUFFERED_QUEUES q, V$BUFFERED_SUBSCRIBERS s, DBA_APPLY a
WHERE q.QUEUE_ID = s.QUEUE_ID AND 
      s.SUBSCRIBER_ADDRESS IS NULL AND
      s.SUBSCRIBER_NAME = a.APPLY_NAME;

Your output looks similar to the following:

Last
                                                   Number of  Dequeued
                 Queue Queue Startup   Cumulative   Dequeued   Message
Apply Process    Owner Name  Time        Messages   Messages    Number
---------------- ----- ----- --------- ---------- ---------- ---------
APPLY$_DB2_2     STRMA DB2$A 25-JUN-09      11039      11039   1509859
                 DMIN  PPQ

Displaying the Queues and Database Link for Each Propagation

You can display information about each propagation by querying the DBA_PROPAGATION data dictionary view. This view contains information about each propagation with a source queue is at the local database.

The query in this section displays the following information about each propagation:

• The propagation name

• The source queue name

• The database link used by the propagation

• The destination queue name

• The status of the propagation, either ENABLED, DISABLED, or ABORTED

• Whether the propagation is a queue-to-queue propagation

To display this information about each propagation in a database, run the following query:

COLUMN PROPAGATION_NAME        HEADING 'Propagation|Name'   FORMAT A19
COLUMN SOURCE_QUEUE_NAME       HEADING 'Source|Queue|Name'  FORMAT A17
COLUMN DESTINATION_DBLINK      HEADING 'Database|Link'      FORMAT A9
COLUMN DESTINATION_QUEUE_NAME  HEADING 'Dest|Queue|Name'    FORMAT A15
COLUMN STATUS                  HEADING 'Status'             FORMAT A8
COLUMN QUEUE_TO_QUEUE          HEADING 'Queue-|to-|Queue?'  FORMAT A6
 
SELECT PROPAGATION_NAME,
       SOURCE_QUEUE_NAME,
       DESTINATION_DBLINK, 
       DESTINATION_QUEUE_NAME,
       STATUS,
       QUEUE_TO_QUEUE
  FROM DBA_PROPAGATION;

Your output looks similar to the following:

Source                      Dest                     Queue-
Propagation         Queue             Database  Queue                    to-
Name                Name              Link      Name            Status   Queue?
------------------- ----------------- --------- --------------- -------- ------
PROPAGATION$_6      DA$CAPQ           DB.EXAMPL DA$APPQ         ENABLED  TRUE
                                      E.COM

Determining the Source Queue and Destination Queue for Each Propagation

You can determine the source queue and destination queue for each propagation by querying the DBA_PROPAGATION data dictionary view.

The query in this section displays the following information about each propagation:

• The propagation name

• 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 that contains the destination queue

To display this information about each propagation in a database, run the following query:

COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A20
COLUMN SOURCE_QUEUE_OWNER HEADING 'Source|Queue|Owner' FORMAT A10
COLUMN 'Source Queue' HEADING 'Source|Queue' FORMAT A15
COLUMN DESTINATION_QUEUE_OWNER HEADING 'Dest|Queue|Owner'   FORMAT A10
COLUMN 'Destination Queue' HEADING 'Destination|Queue' FORMAT A15

SELECT p.PROPAGATION_NAME,
       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;

Your output looks similar to the following:

Source                     Dest
Propagation          Queue      Source          Queue      Destination
Name                 Owner      Queue           Owner      Queue
-------------------- ---------- --------------- ---------- ---------------
PROPAGATION$_6       STRMADMIN  DA$CAPQ@DA.EXAM STRMADMIN  DA$APPQ@DB.EXAM
                                PLE.COM                    PLE.COM

Determining the Rule Sets for Each Propagation

The query in this section displays the following information for each propagation:

• The propagation name

• The owner of the positive rule set for the propagation

• The name of the positive rule set used by the propagation

• The owner of the negative rule set used by the propagation

• The name of the negative rule set used by the propagation

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

COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A20
COLUMN RULE_SET_OWNER HEADING 'Positive|Rule Set|Owner' FORMAT A10
COLUMN RULE_SET_NAME HEADING 'Positive Rule|Set Name' FORMAT A15
COLUMN NEGATIVE_RULE_SET_OWNER HEADING 'Negative|Rule Set|Owner' FORMAT A10
COLUMN NEGATIVE_RULE_SET_NAME HEADING 'Negative Rule|Set Name' FORMAT A15

SELECT PROPAGATION_NAME, 
       RULE_SET_OWNER, 
       RULE_SET_NAME, 
       NEGATIVE_RULE_SET_OWNER, 
       NEGATIVE_RULE_SET_NAME
  FROM DBA_PROPAGATION;

Your output looks similar to the following:

Positive                   Negative
Propagation          Rule Set   Positive Rule   Rule Set   Negative Rule
Name                 Owner      Set Name        Owner      Set Name
-------------------- ---------- --------------- ---------- ---------------
PROPAGATION$_6       STRMADMIN  RULESET$_7      STRMADMIN  RULESET$_9

Displaying Information About the Schedules for Propagation Jobs

The query in this section displays the following information about the propagation schedules for each propagation job used by a propagation in the database:

• The name of the propagation

• 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 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 is aborted automatically.

• Whether the propagation is queue-to-queue or queue-to-dblink

• The error message text of the last unsuccessful propagation execution

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

COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15
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 'Failures' FORMAT 999
COLUMN QUEUE_TO_QUEUE HEADING 'Queue|to|Queue'
COLUMN LAST_ERROR_MSG HEADING 'Last Error|Message' FORMAT A15
 
SELECT p.PROPAGATION_NAME,
       s.LATENCY,
       DECODE(s.SCHEDULE_DISABLED,
                'Y', 'Disabled',
                'N', 'Enabled') SCHEDULE_DISABLED,
       s.PROCESS_NAME,
       s.FAILURES,
       p.QUEUE_TO_QUEUE,
       s.LAST_ERROR_MSG
  FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
  WHERE s.MESSAGE_DELIVERY_MODE = 'BUFFERED'
    AND s.DESTINATION LIKE '%' || p.DESTINATION_DBLINK
    AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
    AND s.QNAME  = p.SOURCE_QUEUE_NAME
  ORDER BY PROPAGATION_NAME;

Your output looks similar to the following:

Queue
                   Latency                            to     Last Error
Propagation     in Seconds Status   Process  Failures Queue  Message
--------------- ---------- -------- -------- -------- ------ ---------------
PROPAGATION$_6          19 Enabled  CS00            0 TRUE

Determining the Total Number of Messages and Bytes Propagated

A propagation can be queue-to-queue or queue-to-database link (queue-to-dblink). A queue-to-queue propagation always has its own exclusive propagation job to propagate messages from the source queue to the destination queue. Because each propagation job has its own propagation schedule, the propagation schedule of each queue-to-queue propagation can be managed separately. All queue-to-dblink propagations that share the same database link have a single propagation schedule.

The query in this section displays the following information for each propagation:

• The name of the propagation

• The total time spent by the system executing the propagation schedule

• The total number of messages propagated by the propagation schedule

• The total number of bytes propagated by the propagation schedule

Run the following query to display this information for each propagation with a source queue at the local database:

COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A20
COLUMN TOTAL_TIME HEADING 'Total Time|Executing|in Seconds' FORMAT 999999
COLUMN TOTAL_NUMBER HEADING 'Total Messages|Propagated' FORMAT 999999999
COLUMN TOTAL_BYTES HEADING 'Total Bytes|Propagated' FORMAT 9999999999999

SELECT p.PROPAGATION_NAME, s.TOTAL_TIME, s.TOTAL_NUMBER, s.TOTAL_BYTES 
  FROM DBA_QUEUE_SCHEDULES s, DBA_PROPAGATION p
  WHERE s.DESTINATION LIKE '%' || p.DESTINATION_DBLINK
    AND s.SCHEMA = p.SOURCE_QUEUE_OWNER
    AND s.QNAME  = p.SOURCE_QUEUE_NAME
    AND s.MESSAGE_DELIVERY_MODE = 'BUFFERED';

Your output looks similar to the following:

Total Time
Propagation           Executing Total Messages    Total Bytes
Name                 in Seconds     Propagated     Propagated
-------------------- ---------- -------------- --------------
PROPAGATION$_6                0         432615       94751013

Displaying Information About Propagation Senders

A propagation sender sends messages from a source queue to a destination queue.

The query in this section displays the following information about each propagation sender in a database:

• The name of the propagation

• The session identifier of the propagation sender

• The session serial number of the propagation sender

• The operating system process identification number of the propagation sender

• The state of the propagation sender

In a combined capture and apply optimization, the capture process acts as the propagation sender and transmits messages directly to the propagation receiver. When a propagation is part of a combined capture and apply optimization, this query shows the capture process session ID, session serial number, operating system process ID, and state.

When a propagation is not part of a combined capture and apply optimization, this query shows the propagation job session ID, session serial number, operating system process ID, and state.

To view this information, run the following query:

COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A11
COLUMN SESSION_ID HEADING 'Session ID' FORMAT 9999
COLUMN SERIAL# HEADING 'Session|Serial Number' FORMAT 9999
COLUMN SPID HEADING 'Operating System|Process ID' FORMAT A24
COLUMN STATE HEADING 'State' FORMAT A16

SELECT p.PROPAGATION_NAME, 
       s.SESSION_ID, 
       s.SERIAL#, 
       s.SPID, 
       s.STATE
  FROM DBA_PROPAGATION p, V$PROPAGATION_SENDER s
  WHERE p.SOURCE_QUEUE_OWNER      = s.QUEUE_SCHEMA AND
        p.SOURCE_QUEUE_NAME       = s.QUEUE_NAME AND
        p.DESTINATION_QUEUE_OWNER = s.DST_QUEUE_SCHEMA AND
        p.DESTINATION_QUEUE_NAME  = s.DST_QUEUE_NAME;

Your output looks similar to the following:

Propagation                  Session Operating System
Name        Session ID Serial Number Process ID               State
----------- ---------- ------------- ------------------------ ----------------
PROPAGATION         61            17 21145                    Waiting on empty
$_6                                                            queue

Displaying Information About Propagation Receivers

A propagation receiver enqueues messages sent by propagation senders into a destination queue. The query in this section displays the following information about each propagation receiver in a database:

• The name of the propagation

• The session ID of the propagation receiver

• The session serial number propagation receiver

• The operating system process identification number of the propagation receiver

• The state of the propagation receiver

To view this information, run the following query:

COLUMN PROPAGATION_NAME HEADING 'Propagation|Name' FORMAT A15
COLUMN SESSION_ID HEADING 'Session ID' FORMAT 999999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 999999
COLUMN SPID HEADING 'Operating|System|Process ID' FORMAT 999999
COLUMN STATE HEADING 'State' FORMAT A16
 
SELECT PROPAGATION_NAME, 
       SESSION_ID, 
       SERIAL#, 
       SPID, 
       STATE
  FROM V$PROPAGATION_RECEIVER;

Your output looks similar to the following:

Session Operating
Propagation                 Serial System
Name            Session ID  Number Process ID               State
--------------- ---------- ------- ------------------------ ----------------
PROPAGATION$_5          60       5 21050                    Waiting for mess
                                                            age from propaga
                                                            tion sender

Displaying Session Information About Each Propagation

The query in this section displays the following session information about each session associated with a propagation in a database:

• The Oracle Streams component

• The session identifier

• The serial number

• The operating system process identification number

• The process names of the propagation sender and propagation receiver processes

To display this information for each propagation in a database, run the following query:

COLUMN ACTION HEADING 'Streams Component' FORMAT A28
COLUMN SID HEADING 'Session ID' FORMAT 99999
COLUMN SERIAL# HEADING 'Session|Serial|Number' FORMAT 9999999
COLUMN PROCESS HEADING 'Operating System|Process Number' FORMAT A20
COLUMN PROCESS_NAME HEADING 'Process|Names' FORMAT A7
 
SELECT /*+PARAM('_module_action_old_length',0)*/ ACTION,
       SID,
       SERIAL#,
       PROCESS,
       SUBSTR(PROGRAM,INSTR(PROGRAM,'(')+1,4) PROCESS_NAME
  FROM V$SESSION
  WHERE MODULE ='Streams' AND
        ACTION LIKE '%Propagation%';

Your output looks similar to the following:

Session
                                         Serial Operating System     Process
Streams Component            Session ID   Number Process Number       Names
---------------------------- ---------- -------- -------------------- -------
APPLY$_DB_3 - Propagation Re         60        5 21048                TNS
ceiver CCA
 
PROPAGATION$_6 - Propagation         61       17 21145                CS00
Sender CCA

The CCA in the Streams component sample output indicates that the propagation is part of a combined capture and apply optimization. The TNS process name indicates that the propagation receiver was initiated remotely by a capture process.