VIEW: SYS.DBA_STREAMS_RULES

Source

View Text - Preformatted

select decode(r.streams_type, 1, 'CAPTURE',
                              2, 'PROPAGATION',
                              3, 'APPLY',
                              4, 'DEQUEUE',
                              5, 'SYNC_CAPTURE') streams_type,
       r.streams_name, r.rule_set_owner, r.rule_set_name,
       r.rule_owner, r.rule_name, r.rule_condition, r.rule_set_type,
       decode(sr.object_type, 1, 'TABLE',
                              2, 'SCHEMA',
                              3, 'GLOBAL',
                              4, 'PROCEDURE') streams_rule_type,
       sr.schema_name, sr.object_name,
       decode(sr.subsetting_operation, 1, 'INSERT',
                                       2, 'UPDATE',
                                       3, 'DELETE') subsetting_operation,
       sr.dml_condition,
       decode(sr.include_tagged_lcr, 0, 'NO',
                                     1, 'YES') include_tagged_lcr,
       sr.source_database,
       decode(sr.rule_type, 1, 'DML',
                            2, 'DDL',
                            3, 'PROCEDURE') rule_type,
       smr.msg_type_owner message_type_owner,
       smr.msg_type_name message_type_name,
       smr.msg_rule_var message_rule_variable,
       NVL(sr.rule_condition, smr.rule_condition) original_rule_condition,
       decode(NVL(sr.rule_condition, smr.rule_condition),
              NULL, NULL,
              dbms_lob.substr(r.rule_condition), 'YES',
              decode(least(4001,dbms_lob.getlength(r.rule_condition)),
                     4001, NULL, 'NO')) same_rule_condition
  from "_DBA_STREAMS_RULES_H" r, streams$_rules sr, streams$_message_rules smr
  where r.rule_name = sr.rule_name(+)
    and r.rule_owner = sr.rule_owner(+)
    and r.rule_name = smr.rule_name(+)
    and r.rule_owner = smr.rule_owner(+)
    and (r.streams_type NOT IN (1, 3) or
         r.streams_name in
           (select apply_name from dba_apply
            where purpose NOT IN ('XStream Out', 'XStream In',
                                  'GoldenGate Capture', 'GoldenGate Apply')
            union
            select capture_name from dba_capture
            where purpose NOT IN ('XStream Out','GoldenGate Capture')))

View Text - HTML Formatted

SELECT DECODE(R.STREAMS_TYPE
, 1
, 'CAPTURE'
, 2
, 'PROPAGATION'
, 3
, 'APPLY'
, 4
, 'DEQUEUE'
, 5
, 'SYNC_CAPTURE') STREAMS_TYPE
, R.STREAMS_NAME
, R.RULE_SET_OWNER
, R.RULE_SET_NAME
, R.RULE_OWNER
, R.RULE_NAME
, R.RULE_CONDITION
, R.RULE_SET_TYPE
, DECODE(SR.OBJECT_TYPE
, 1
, 'TABLE'
, 2
, 'SCHEMA'
, 3
, 'GLOBAL'
, 4
, 'PROCEDURE') STREAMS_RULE_TYPE
, SR.SCHEMA_NAME
, SR.OBJECT_NAME
, DECODE(SR.SUBSETTING_OPERATION
, 1
, 'INSERT'
, 2
, 'UPDATE'
, 3
, 'DELETE') SUBSETTING_OPERATION
, SR.DML_CONDITION
, DECODE(SR.INCLUDE_TAGGED_LCR
, 0
, 'NO'
, 1
, 'YES') INCLUDE_TAGGED_LCR
, SR.SOURCE_DATABASE
, DECODE(SR.RULE_TYPE
, 1
, 'DML'
, 2
, 'DDL'
, 3
, 'PROCEDURE') RULE_TYPE
, SMR.MSG_TYPE_OWNER MESSAGE_TYPE_OWNER
, SMR.MSG_TYPE_NAME MESSAGE_TYPE_NAME
, SMR.MSG_RULE_VAR MESSAGE_RULE_VARIABLE
, NVL(SR.RULE_CONDITION
, SMR.RULE_CONDITION) ORIGINAL_RULE_CONDITION
, DECODE(NVL(SR.RULE_CONDITION
, SMR.RULE_CONDITION)
, NULL
, NULL
, DBMS_LOB.SUBSTR(R.RULE_CONDITION)
, 'YES'
, DECODE(LEAST(4001
, DBMS_LOB.GETLENGTH(R.RULE_CONDITION))
, 4001
, NULL
, 'NO')) SAME_RULE_CONDITION
FROM "_DBA_STREAMS_RULES_H" R
, STREAMS$_RULES SR
, STREAMS$_MESSAGE_RULES SMR
WHERE R.RULE_NAME = SR.RULE_NAME(+)
AND R.RULE_OWNER = SR.RULE_OWNER(+)
AND R.RULE_NAME = SMR.RULE_NAME(+)
AND R.RULE_OWNER = SMR.RULE_OWNER(+)
AND (R.STREAMS_TYPE NOT IN (1
, 3) OR R.STREAMS_NAME IN (SELECT APPLY_NAME
FROM DBA_APPLY
WHERE PURPOSE NOT IN ('XSTREAM OUT'
, 'XSTREAM IN'
, 'GOLDENGATE CAPTURE'
, 'GOLDENGATE APPLY') UNION SELECT CAPTURE_NAME
FROM DBA_CAPTURE
WHERE PURPOSE NOT IN ('XSTREAM OUT'
, 'GOLDENGATE CAPTURE')))