More Streams monitoring queries

Some more queries that are useful when monitoring Streams replication.

Viewing capture processes for LCRs in each buffered queue:
COLUMN SENDER_NAME HEADING 'Capture|Process' FORMAT A13
COLUMN SENDER_ADDRESS HEADING 'Sender Queue' FORMAT A27
COLUMN QUEUE_NAME HEADING 'Queue Name' FORMAT A15
COLUMN CNUM_MSGS HEADING 'Number|of LCRs|Enqueued' FORMAT 99999999
COLUMN LAST_ENQUEUED_MSG HEADING 'Last|Enqueued|LCR' FORMAT 99999999

SELECT sender_name,
sender_address,
queue_name,
cnum_msgs,
last_enqueued_msg
FROM v$buffered_publishers;

Determining the number of messages in each buffered queue:
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;

Number of messages and bytes sent by propagation processes:
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;

Performance statistics for propagation processes that send buffered messages:
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;

Propagations dequeuing messages from each buffered queue:
COLUMN PROPAGATION_NAME HEADING 'Propagation' FORMAT A15
COLUMN SUBSCRIBER_ADDRESS HEADING 'Destination|Database' FORMAT A11
COLUMN CURRENT_ENQ_SEQ HEADING 'Current|Enqueued|Sequence' FORMAT 99999999
COLUMN LAST_BROWSED_SEQ HEADING 'Last|Browsed|Sequence' FORMAT 99999999
COLUMN LAST_DEQUEUED_SEQ HEADING 'Last|Dequeued|Sequence' FORMAT 99999999
COLUMN NUM_MSGS HEADING 'Number of|Messages|in Queue|(Current)' FORMAT 99999999
COLUMN TOTAL_SPILLED_MSG HEADING 'Number of|Spilled|Messages|(Cumulative)'
FORMAT 99999999

SELECT p.propagation_name,
s.subscriber_address,
s.current_enq_seq,
s.last_browsed_seq,
s.last_dequeued_seq,
s.num_msgs,
s.total_spilled_msg
FROM dba_propagation p,
v$buffered_subscribers s,
v$buffered_queues q
WHERE q.queue_id = s.queue_id
AND p.source_queue_owner = q.queue_schema
AND p.source_queue_name = q.queue_name
AND s.subscriber_address LIKE '%' || p.destination_dblink;

Information about propagation processes that send buffered messages:
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;