看一个简单的streams的capture的规则

SQL> @pt

Enter value for 1:  select * from dba_streams_schema_rules where streams_name=''O11203_CTAIS2_CAP$1''

old   2:    p_query         VARCHAR2(4000) := '&1';

new   2:    p_query         VARCHAR2(4000) := ' select * from dba_streams_schema_rules where streams_name=''O11203_CTAIS2_CAP$1''';

这个表只保留创建时的规则，一般查询规则不以此为准

-----------------

STREAMS_NAME                  : O11203_CTAIS2_CAP$1

STREAMS_TYPE                  : CAPTURE

SCHEMA_NAME                   : CTAIS2

RULE_TYPE                     : DML           -- DML触发，这个无意义，一切其实以RULE为准

INCLUDE_TAGGED_LCR            : YES          -- 复制所有LCR

SOURCE_DATABASE               : O11203

RULE_NAME                     : CTAIS2116

RULE_OWNER                    : STRMADMIN

RULE_CONDITION                : ((((:dml.get_object_owner() = 'CTAIS2') and

:dml.get_source_database_name() = 'O11203' )) and (:dml.get_compatible() <=

dbms_streams.compatible_11_2))                -- 这个只是创建时的RULE,以后修改RULE这个不变

-----------------

STREAMS_NAME                  : O11203_CTAIS2_CAP$1

STREAMS_TYPE                  : CAPTURE

SCHEMA_NAME                   : CTAIS2

RULE_TYPE                     : DDL           -- DDL触发

INCLUDE_TAGGED_LCR            : YES

SOURCE_DATABASE               : O11203

RULE_NAME                     : CTAIS2117

RULE_OWNER                    : STRMADMIN

RULE_CONDITION                : ((((:ddl.get_object_owner() = 'CTAIS2' or

:ddl.get_base_table_owner() = 'CTAIS2') and :ddl.get_source_database_name() =

'O11203' )) and (:ddl.get_compatible() <= dbms_streams.compatible_11_2))

-----------------

PL/SQL procedure successfully completed.

SQL> @pt

Enter value for 1: select * from DBA_STREAMS_RULES where rule_name in (''CTAIS2116'',''CTAIS2117'')

old   2:    p_query         VARCHAR2(4000) := '&1';

new   2:    p_query         VARCHAR2(4000) := 'select * from DBA_STREAMS_RULES where rule_name in (''CTAIS2116'',''CTAIS2117'')';

-----------------

STREAMS_TYPE                  : CAPTURE

STREAMS_NAME                  : O11203_CTAIS2_CAP$1

RULE_SET_OWNER                : STRMADMIN

RULE_SET_NAME                 : RULESET$_118  -- RULE加入RULE SET,在赋给一个STREAMS对象

RULE_OWNER                    : STRMADMIN

RULE_NAME                     : CTAIS2116

RULE_CONDITION                : ((((:dml.get_object_owner() = 'CTAIS2') and

:dml.get_source_database_name() = 'O11203' )) and (:dml.get_compatible() <=

dbms_streams.compatible_11_2))                -- 当前生效的RULE

RULE_SET_TYPE                 : POSITIVE      -- RULE的种类

STREAMS_RULE_TYPE             : SCHEMA

SCHEMA_NAME                   : CTAIS2

OBJECT_NAME                   :

SUBSETTING_OPERATION          :

DML_CONDITION                 :

INCLUDE_TAGGED_LCR            : YES

SOURCE_DATABASE               : O11203

RULE_TYPE                     : DML

MESSAGE_TYPE_OWNER            :

MESSAGE_TYPE_NAME             :

MESSAGE_RULE_VARIABLE         :

ORIGINAL_RULE_CONDITION       : ((((:dml.get_object_owner() = 'CTAIS2') and

:dml.get_source_database_name() = 'O11203' )) and (:dml.get_compatible() <=

dbms_streams.compatible_11_2))                -- 创建时的RULE

SAME_RULE_CONDITION           : YES

-----------------

STREAMS_TYPE                  : CAPTURE

STREAMS_NAME                  : O11203_CTAIS2_CAP$1

RULE_SET_OWNER                : STRMADMIN

RULE_SET_NAME                 : RULESET$_118

RULE_OWNER                    : STRMADMIN

RULE_NAME                     : CTAIS2117

RULE_CONDITION                : ((((:ddl.get_object_owner() = 'CTAIS2' or

:ddl.get_base_table_owner() = 'CTAIS2') and :ddl.get_source_database_name() =

'O11203' )) and (:ddl.get_compatible() <= dbms_streams.compatible_11_2))

RULE_SET_TYPE                 : POSITIVE

STREAMS_RULE_TYPE             : SCHEMA

SCHEMA_NAME                   : CTAIS2

OBJECT_NAME                   :

SUBSETTING_OPERATION          :

DML_CONDITION                 :

INCLUDE_TAGGED_LCR            : YES

SOURCE_DATABASE               : O11203

RULE_TYPE                     : DDL

MESSAGE_TYPE_OWNER            :

MESSAGE_TYPE_NAME             :

MESSAGE_RULE_VARIABLE         :

ORIGINAL_RULE_CONDITION       : ((((:ddl.get_object_owner() = 'CTAIS2' or

:ddl.get_base_table_owner() = 'CTAIS2') and :ddl.get_source_database_name() =

'O11203' )) and (:ddl.get_compatible() <= dbms_streams.compatible_11_2))

SAME_RULE_CONDITION           : YES

-----------------

默认创建的rule中，INCLUDE_TAGGED_LCR=YES，这会导致问题的，需要修改RULE，增加

exec DBMS_RULE_ADM.ALTER_RULE('CTAIS2116',condition=>'((((:dml.get_object_owner() = ''CTAIS2'') and :dml.get_source_database_name() = ''O11203'' )) and :dml.is_null_tag() = ''Y'' and (:dml.get_compatible() <= dbms_streams.compatible_11_2))')

exec DBMS_RULE_ADM.ALTER_RULE('CTAIS2117',condition=>'((((:ddl.get_object_owner() = ''CTAIS2'' or :ddl.get_base_table_owner() = ''CTAIS2'') and :ddl.is_null_tag() = ''Y'' and :ddl.get_source_database_name() =''O11203'' )) and (:ddl.get_compatible() <= dbms_streams.compatible_11_2))')

修改后的规则查询，在dba_streams_schema_rules中没有，直接以DBA_STREAMS_RULES.RULE_CONDITION列为准。没有找到其他修改INCLUDE_TAGGED_LCR的方法

如何添加一个RULE？

DBMS_STREAMS_ADM封装了添加RULE的方法，不需要写繁琐的表达式

BEGIN

DBMS_STREAMS_ADM.ADD_TABLE_RULES (

table_name         => '.',

streams_type       => 'capture',

streams_name       => 'O11203_CTAIS2_CAP$1',

queue_name         => 'STRMADMIN.O11203_CTAIS2_CAPQ$1',

include_dml        => true,     -- 添加一个取:dml变量的规则

include_ddl        => true,     -- 添加一个取:ddl变量的规则

include_tagged_lcr => false,       -- 添加 :dml/ddl.is_null_tag() = 'Y'

source_database    => 'O11203',

inclusion_rule    => false,        -- 加入positive还是 negative

and_condition      => null) ;   -- 添加额外的条件   

END;

/

当然，也可以手工创建

BEGIN

  DBMS_RULE_ADM.CREATE_RULE_SET (

    rule_set_name => 'STRMADMIN.CTAIS2_RULES',

    evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT');

END;

/

BEGIN

DBMS_RULE_ADM.CREATE_RULE(

rule_name => 'STRMADMIN.CTAIS2_DML',

condition => '((((:dml.get_object_owner() = ''CTAIS2'') and :dml.get_source_database_name() = ''O11203'' )) and :dml.is_null_tag() = ''Y'' and (:dml.get_compatible() <= dbms_streams.compatible_11_2))');

END;

/

BEGIN

  DBMS_RULE_ADM.ADD_RULE(

    rule_name => 'STRMADMIN.CTAIS2_DML',

    rule_set_name => 'STRMADMIN.CTAIS2_RULES');

END;

/

BEGIN

  DBMS_CAPTURE_ADM.ALTER_CAPTURE(

    capture_name =>'strm01_capture',

    rule_set_name =>'STRMADMIN.CTAIS2_RULES');

END;

/