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

最新推荐文章于 2024-07-26 09:33:20 发布

呆瓜呆呆

最新推荐文章于 2024-07-26 09:33:20 发布

阅读量3.7k

点赞数 1

本文链接：https://blog.csdn.net/ch7543658/article/details/38370639

版权

oracle 11g 同时被 3 个专栏收录

34 篇文章 1 订阅

订阅专栏

streams

7 篇文章 1 订阅

订阅专栏

流

7 篇文章 0 订阅

订阅专栏

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

1、查看evaluation context信息

col VARIABLE_NAME format a13
col VARIABLE_TYPE format a25
col VARIABLE_VALUE_FUNCTION format a25
col VARIABLE_METHOD_FUNCTION format a25
col EVALUATION_CONTEXT_NAME format a23
col EVALUATION_CONTEXT_OWNER format a24

select * 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
                                                                                                                   ON

SYS                      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                       TION

SYS                      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 a10
col RULE_NAME format a9
col RULE_CONDITION format a50
col RULE_EVALUATION_CONTEXT_OWNER format a29
col RULE_EVALUATION_CONTEXT_NAME format a28
col RULE_ACTION_CONTEXT format a19
col RULE_COMMENT format a11

select * 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.get

STRADMIN   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

declare
		l_dml_rule_name varchar2(30);
		l_ddl_rule_name varchar2(30);
begin
	dbms_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 rule
		source_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_name
from dba_streams_rules
where 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		--用于捕获DML
CHENHAO14 DDL POSITIVE RULESET$_15                    CAPTURE      CAPTURE_SM1		--用于捕获DDL

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

declare
		l_dml_rule_name varchar2(30);
		l_ddl_rule_name varchar2(30);
begin
	dbms_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 rule
		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"."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_name
from dba_streams_rules
where 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 set
BONUS26    DML       NEGATIVE       RULESET$_28     CAPTURE      DBXA_CAP			--negative rule set

7、查看 rule condition

set long 4000
select rule_name,
	rule_condition
from dba_streams_rules
where 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)

declare
	l_dml_rule_name varchar2(30);
	l_ddl_rule_name varchar2(30);
begin
	dbms_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

declare
	l_dml_rule_name varchar2(30);
	l_ddl_rule_name varchar2(30);
begin
	dbms_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进行处理)

declare
	l_insert_rule_name varchar2(30);
	l_update_rule_name varchar2(30);
	l_delete_rule_name varchar2(30);
begin
	dbms_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_condition
from dba_streams_transformations
where 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 评估上下文
begin
	dbms_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		   
begin
	dbms_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 中		   
begin
	dbms_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 functions
declare
	l_dml_rule_name varchar2(30);
	l_ddl_rule_name varchar2(30);
begin
	dbms_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.