这些都是看书的一些笔记,为了以后能方便的找到这些示例小程序。今天就把他给记下来。
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.