STREAMS筆記(6) rule

rule 是Streams用來定義複製規則的東西

 

看一個簡單的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;

/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-763554/，如需轉載，請註明出處，否則將追究法律責任。