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/，如需轉載，請註明出處，否則將追究法律責任。

STREAMS筆記(6) rule

如何新增一個RULE？

相關文章