oracle 11g streams rule和rule set 使用示例

这些都是看书的一些笔记，为了以后能方便的找到这些示例小程序。今天就把他给记下来。

1、查看evaluation context信息

col VARIABLE_NAME format a13col VARIABLE_TYPE format a25col VARIABLE_VALUE_FUNCTION format a25col VARIABLE_METHOD_FUNCTION format a25col EVALUATION_CONTEXT_NAME format a23col EVALUATION_CONTEXT_OWNER format a24select * from dba_evaluation_context_vars;EVALUATION_CONTEXT_OWNER EVALUATION_CONTEXT_NAME VARIABLE_NAME VARIABLE_TYPE             VARIABLE_VALUE_FUNCTION   VARIABLE_METHOD_FUNCTION------------------------ ----------------------- ------------- ------------------------- ------------------------- -------------------------SYS                      STREAMS$_EVALUATION_CON               SYS.ANYDATA                                         SYS.DBMS_STREAMS_INTERNAL                         TEXT                                                                                      .ANYDATA_FAST_EVAL_FUNCTI                                                                                                                   ONSYS                      STREAMS$_EVALUATION_CON DDL           SYS.LCR$_DDL_RECORD       SYS.DBMS_STREAMS_INTERNAL SYS.DBMS_STREAMS_INTERNAL                         TEXT                                                            .DDL_VARIABLE_VALUE_FUNCT .DDL_FAST_EVALUATION_FUNC                                                                                         ION                       TIONSYS                      STREAMS$_EVALUATION_CON DML           SYS.LCR$_ROW_RECORD       SYS.DBMS_STREAMS_INTERNAL SYS.DBMS_STREAMS_INTERNAL                         TEXT                                                            .ROW_VARIABLE_VALUE_FUNCT .ROW_FAST_EVALUATION_FUNC                                                                                         ION                       TION

2、查看rule condition、action context

col RULE_OWNER format a10col RULE_NAME format a9col RULE_CONDITION format a50col RULE_EVALUATION_CONTEXT_OWNER format a29col RULE_EVALUATION_CONTEXT_NAME format a28col RULE_ACTION_CONTEXT format a19col RULE_COMMENT format a11select * from dba_rules;RULE_OWNER RULE_NAME RULE_CONDITION                                     RULE_EVALUATION_CONTEXT_OWNER RULE_EVALUATION_CONTEXT_NAME RULE_ACTION_CONTEXT RULE_COMMEN---------- --------- -------------------------------------------------- ----------------------------- ---------------------------- ------------------- -----------STRADMIN   CHENHAO13 ((:dml.get_object_owner() = 'CHENHAO') and :dml.is SYS                           STREAMS$_EVALUATION_CONTEXT                     _null_tag() = 'Y' )STRADMIN   CHENHAO14 ((:ddl.get_object_owner() = 'CHENHAO' or :ddl.get_ SYS                           STREAMS$_EVALUATION_CONTEXT                     base_table_owner() = 'CHENHAO'STRADMIN   CHENHAO16 ((:dml.get_object_owner() = 'CHENHAO') and :dml.is SYS                           STREAMS$_EVALUATION_CONTEXT                     _null_tag() = 'Y' and :dml.getSTRADMIN   CHENHAO17 ((:ddl.get_object_owner() = 'CHENHAO' or :ddl.get_ SYS                           STREAMS$_EVALUATION_CONTEXT                     base_table_owner() = 'CHENHAO'SYS        ALERT_QUE tab.user_data.MESSAGE_LEVEL <> 32 AND tab.user_dat SYS                           AQ$_ALERT_QT_V           $1        a.MESSAGE_GROUP = 'High Availa'

3、为捕获进程（capture） 添加 schema rule

declarel_dml_rule_name varchar2(30);l_ddl_rule_name varchar2(30);begindbms_streams_adm.add_schema_rules (schema_name => 'SCOTT',streams_type => 'CAPTURE',streams_name => 'DBXA_CAP',queue_name => 'DBXA_CAP_Q',include_dml => true,include_ddl => false,inclusion_rule => true,-- 代表是positive rulesource_database => 'DBXA.WORLD',dml_rule_name => l_dml_rule_name,--为dml规则分配名字（不是指定已经存在的名字）ddl_rule_name => l_ddl_rule_name--为ddl规则分配名字（不是指定已经存在的名字）);dbms_output.put_line('DML Rule Name is: ' || l_dml_rule_name);dbms_output.put_line('DDL Rule Name is: ' || l_ddl_rule_name);end;/DML Rule Name is: "STRMADMIN"."SCOTT13"DDL Rule Name is: "STRMADMIN"."SCOTT14"PL/SQL procedure successfully completed.

4、查看创建的 rule 信息

select rule_name,rule_type,rule_set_type,rule_set_name,streams_type,streams_namefrom dba_streams_ruleswhere rule_name in ('CHENHAO13','CHENHAO14');RULE_NAME RUL RULE_SET RULE_SET_NAME                  STREAMS_TYPE STREAMS_NAME--------- --- -------- ------------------------------ ------------ ------------CHENHAO13 DML POSITIVE RULESET$_15                    CAPTURE      CAPTURE_SM1--用于捕获DMLCHENHAO14 DDL POSITIVE RULESET$_15                    CAPTURE      CAPTURE_SM1--用于捕获DDL

5、为capture进程 添加 table rule(当捕获到表 SCOTT.BOUNS 则不给予处理，创建的是negative rule)

declarel_dml_rule_name varchar2(30);l_ddl_rule_name varchar2(30);begindbms_streams_adm.add_table_rules (table_name => 'SCOTT.BONUS',streams_type => 'CAPTURE',streams_name => 'DBXA_CAP',queue_name => 'DBXA_CAP_Q',include_dml => true,include_ddl => true,inclusion_rule => false,--指定是 negative rulesource_database => 'DBXA.WORLD',dml_rule_name => l_dml_rule_name,ddl_rule_name => l_ddl_rule_name);dbms_output.put_line('DML Rule Name is: ' || l_dml_rule_name);dbms_output.put_line('DDL Rule Name is: ' || l_ddl_rule_name);end;/DML Rule Name is: "STRMADMIN"."BONUS26"DDL Rule Name is: "STRMADMIN"."BONUS27"PL/SQL procedure successfully completed.

6、查看创建的RULE

select rule_name,rule_type,rule_set_type,rule_set_name,streams_type,streams_namefrom dba_streams_ruleswhere rule_name in ('BONUS26','BONUS27');RULE_NAME  RULE_TYPE RULE_SET_TYPE  RULE_SET_NAME   STREAMS_TYPE STREAMS_NAME---------- --------- -------------- --------------- ------------ ------------BONUS27    DDL       NEGATIVE       RULESET$_28     CAPTURE      DBXA_CAP--negative rule setBONUS26    DML       NEGATIVE       RULESET$_28     CAPTURE      DBXA_CAP--negative rule set

7、查看 rule condition

set long 4000select rule_name,rule_conditionfrom dba_streams_ruleswhere rule_name in ('SCOTT13','SCOTT14');RULE_NAME  RULE_CONDITION---------- ---------------------------------------------------------------------SCOTT14    ((:ddl.get_object_owner() = 'SCOTT' or :ddl.get_base_table_owner() =   'SCOTT') and :ddl.is_null_tag() = 'Y' and :ddl.get_source_database_na   me() = 'DBXA.WORLD')SCOTT13    ((:dml.get_object_owner() = 'SCOTT') and :dml.is_null_tag() = 'Y' and           :dml.get_source_database_name() = 'DBXA.WORLD' )

8、自定义 rule(只执行 DML 的insert 和 update)

declarel_dml_rule_name varchar2(30);l_ddl_rule_name varchar2(30);begindbms_streams_adm.add_schema_rules (schema_name => 'SCOTT',streams_type => 'CAPTURE',streams_name => 'DBXA_CAP',queue_name => 'DBXA_CAP_Q',include_dml => true,include_ddl => false,--不对DDL进行执行inclusion_rule => true,source_database => 'DBXA.WORLD',and_condition => '(:lcr.get_command_type() = ''INSERT'' OR:lcr.get_command_type() = ''UPDATE'')',--定义对insert和update进行执行dml_rule_name => l_dml_rule_name,ddl_rule_name => l_ddl_rule_name);dbms_output.put_line('DML Rule Name is: ' || l_dml_rule_name);end;/DML Rule Name is: "STRMADMIN"."SCOTT40"PL/SQL procedure successfully completed.RULE_NAME  RULE_CONDITION---------- ------------------------------------------------------------SCOTT40    ((((:dml.get_object_owner() = 'SCOTT') and :dml.is_null_tag(           ) = 'Y' and :dml.get_source_database_name() = 'DBXA.WORLD' ))           and ((:dml.get_command_type() = 'INSERT' OR :dml.get_command_--自定义的条件被添加到rule condition中           type = 'UPDATE')))

9、不支持 streams 的 对象 和 column

dba_streams_unsupported <span style="white-space:pre"></span>--不支持的对象dba_streams_columns--不支持的列

10、添加positive global rule

declarel_dml_rule_name varchar2(30);l_ddl_rule_name varchar2(30);begindbms_streams_adm.add_global_rules (streams_type => 'CAPTURE',streams_name => 'DBXA_CAP',queue_name => 'DBXA_CAP_Q',include_dml => true,include_ddl => true,inclusion_rule => true,source_database => 'DBXA.WORLD',dml_rule_name => l_dml_rule_name,ddl_rule_name => l_ddl_rule_name);dbms_output.put_line('DML Rule Name is: ' || l_dml_rule_name);dbms_output.put_line('DDL Rule Name is: ' || l_ddl_rule_name); end;/DML Rule Name is: "STRMADMIN"."DBXA29"DDL Rule Name is: "STRMADMIN"."DBXA30"PL/SQL procedure successfully completed.RULE_NAME  RULE_CONDITION---------- ------------------------------------------------------------DBXA29     (:dml.is_null_tag() = 'Y' and :dml.get_source_database_name(           ) = 'DBXA.WORLD' )DBXA30     (:ddl.is_null_tag() = 'Y' and :ddl.get_source_database_name(           ) = 'DBXA.WORLD' )

11、添加 subset rule(对SCOTT.EMP中deptno 为20、30的DML进行处理)

declarel_insert_rule_name varchar2(30);l_update_rule_name varchar2(30);l_delete_rule_name varchar2(30);begindbms_streams_adm.add_subset_rules (table_name => 'SCOTT.EMP',dml_condition => ' DEPTNO IN (20, 30) ',streams_type => 'CAPTURE',streams_name => 'DBXA_CAP',queue_name => 'DBXA_CAP_Q',source_database => 'DBXA.WORLD',insert_rule_name => l_insert_rule_name,update_rule_name => l_update_rule_name,delete_rule_name => l_delete_rule_name);dbms_output.put_line('Insert Rule Name is: ' || l_insert_rule_name);dbms_output.put_line('Update Rule Name is: ' || l_update_rule_name);dbms_output.put_line('Delete Rule Name is: ' || l_delete_rule_name);end;/Insert Rule Name is: "STRMADMIN"."EMP53"Update Rule Name is: "STRMADMIN"."EMP54"Delete Rule Name is: "STRMADMIN"."EMP55"PL/SQL procedure successfully completed.

12、查看 rule transformation 信息

select rule_name,transform_type,subsetting_operation OPER,dml_conditionfrom dba_streams_transformationswhere rule_name in ('EMP53','EMP54','EMP55');RULE_NAME  TRANSFORM_TYPE   OPER  DML_CONDITION---------- -------------------------- ------ --------------------EMP53    SUBSET RULE   INSERT DEPTNO IN (20,30)EMP54    SUBSET RULE   UPDATE DEPTNO IN (20,30)EMP55    SUBSET RULE   DELETE DEPTNO IN (20,30)

13、创建自定义rule和rule set

--创建 rule set 使用 SYS.STREAMS$_EVALUATION_CONTEXT 评估上下文begindbms_rule_adm.create_rule_set(rule_set_name => 'STRMADMIN.IGNORE_DELETE_RS',evaluation_context => 'SYS.STREAMS$_EVALUATION_CONTEXT');end;/PL/SQL procedure successfully completed.   --创建 rule   begindbms_rule_adm.create_rule(rule_name => 'STRMADMIN.IGNORE_DELETE_R1',condition => ' :dml.get_object_owner() = ''SCOTT'' '|| ' AND :dml.get_source_database_name() = ''DBXA.WORLD'' '|| ' AND :dml.is_null_tag() = ''Y'' '|| ' AND (:dml.get_command_type() = ''INSERT'' OR '|| ' :dml.get_command_type() = ''UPDATE'' ) ' ,evaluation_context => NULL,action_context => NULL);end;/PL/SQL procedure successfully completed.      --将 rule 加入 rule set 中   begindbms_rule_adm.add_rule(rule_name => 'STRMADMIN.IGNORE_DELETE_R1',rule_set_name => 'STRMADMIN.IGNORE_DELETE_RS',rule_comment => 'To ignore DELETE commands');end;/PL/SQL procedure successfully completed.

14、添加版本兼容 condition

--COMPATIBLE_9_2 Returns a value corresponding to Oracle 9.2.0 release--COMPATIBLE_10_1 Returns a value corresponding to Oracle 10.1.0 release--COMPATIBLE_10_2 Returns a value corresponding to Oracle 10.2.0 release--COMPATIBLE_11_1 Returns a value corresponding to Oracle 11g R1 release--MAX_COMPATIBLE Returns a value which is always greater than any other values returned by other functionsdeclarel_dml_rule_name varchar2(30);l_ddl_rule_name varchar2(30);begindbms_streams_adm.add_schema_rules (schema_name => 'SCOTT',streams_type => 'CAPTURE',streams_name => 'DBXA_CAP',queue_name => 'DBXA_CAP_Q',include_dml => true,include_ddl => true,inclusion_rule => true,source_database => 'DBXA.WORLD',and_condition => ' :lcr.get_compatible() >=  dbms_streams.compatible_10_2 ',--指定版本兼容条件dml_rule_name => l_dml_rule_name,ddl_rule_name => l_ddl_rule_name);dbms_output.put_line('DML Rule Name is: ' || l_dml_rule_name);dbms_output.put_line('DDL Rule Name is: ' || l_ddl_rule_name);end;/DML Rule Name is: "STRMADMIN"."SCOTT89"DDL Rule Name is: "STRMADMIN"."SCOTT90"PL/SQL procedure successfully completed.