VIEW: SYS.DBA_PROPAGATION

Source

View Text - Preformatted

SELECT p.propagation_name, p.source_queue_schema, p.source_queue,
       p.destination_queue_schema, p.destination_queue, p.destination_dblink,
       p.ruleset_schema, p.ruleset, p.negative_ruleset_schema,
       p.negative_ruleset,
       case when bitand(p.spare1, 1) = 1 THEN 'TRUE' ELSE 'FALSE' END,
       decode (qs.schedule_disabled, 'Y',
                case when qs.failures >= 16 THEN 'ABORTED' ELSE 'DISABLED' END,
                                     'N', 'ENABLED',
                                     null, 'ABORTED'),
       case when bitand(p.spare1, 4) = 4 THEN  p.error_msg
                                         ELSE qs.last_error_msg END,
       case when bitand(p.spare1, 4) = 4 THEN  p.error_date
                                         ELSE qs.last_error_date END,
                                    /* look at errors based on last run mode */
       p.original_propagation_name,
       p.original_source_queue_schema, p.original_source_queue, p.acked_scn,
       auto_merge_threshold
  FROM sys.streams$_propagation_process p, dba_queue_schedules qs
  WHERE p.source_queue_schema = qs.schema (+) and
        p.source_queue = qs.qname (+) and
        qs.destination (+) =
        case when bitand(p.spare1, 1) = 1 THEN
        dbms_logrep_util.canonical_concat(p.destination_queue_schema,
                                          p.destination_queue) || '@' ||
        p.destination_dblink
        ELSE p.destination_dblink END and
        qs.message_delivery_mode (+) = 'PERSISTENT'

View Text - HTML Formatted

SELECT P.PROPAGATION_NAME
, P.SOURCE_QUEUE_SCHEMA
, P.SOURCE_QUEUE
, P.DESTINATION_QUEUE_SCHEMA
, P.DESTINATION_QUEUE
, P.DESTINATION_DBLINK
, P.RULESET_SCHEMA
, P.RULESET
, P.NEGATIVE_RULESET_SCHEMA
, P.NEGATIVE_RULESET
, CASE WHEN BITAND(P.SPARE1
, 1) = 1 THEN 'TRUE' ELSE 'FALSE' END
, DECODE (QS.SCHEDULE_DISABLED
, 'Y'
, CASE WHEN QS.FAILURES >= 16 THEN 'ABORTED' ELSE 'DISABLED' END
, 'N'
, 'ENABLED'
, NULL
, 'ABORTED')
, CASE WHEN BITAND(P.SPARE1
, 4) = 4 THEN P.ERROR_MSG ELSE QS.LAST_ERROR_MSG END
, CASE WHEN BITAND(P.SPARE1
, 4) = 4 THEN P.ERROR_DATE ELSE QS.LAST_ERROR_DATE END
, /* LOOK AT ERRORS BASED ON LAST RUN MODE */ P.ORIGINAL_PROPAGATION_NAME
, P.ORIGINAL_SOURCE_QUEUE_SCHEMA
, P.ORIGINAL_SOURCE_QUEUE
, P.ACKED_SCN
, AUTO_MERGE_THRESHOLD
FROM SYS.STREAMS$_PROPAGATION_PROCESS P
, DBA_QUEUE_SCHEDULES QS
WHERE P.SOURCE_QUEUE_SCHEMA = QS.SCHEMA (+) AND P.SOURCE_QUEUE = QS.QNAME (+) AND QS.DESTINATION (+) = CASE WHEN BITAND(P.SPARE1
, 1) = 1 THEN DBMS_LOGREP_UTIL.CANONICAL_CONCAT(P.DESTINATION_QUEUE_SCHEMA
, P.DESTINATION_QUEUE) || '@' || P.DESTINATION_DBLINK ELSE P.DESTINATION_DBLINK END AND QS.MESSAGE_DELIVERY_MODE (+) = 'PERSISTENT'