1、查看evaluation context信息
[sql]view plain copy
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_internaltext.anydata_fast_eval_function
sys streams$_evaluation_con ddl sys.lcr$_ddl_record sys.dbms_streams_internal sys.dbms_streams_internaltext.ddl_variable_value_funct.ddl_fast_evaluation_funcion tion
sys streams$_evaluation_con dml sys.lcr$_row_record sys.dbms_streams_internal sys.dbms_streams_internaltext.row_variable_value_funct.row_fast_evaluation_funcion tion
2、查看rule condition、action context
[sql]view plain copy
col rule_owner format a10
col rule_name format a9
col rule_condition format a50
col rule_evaluation_context_owner format a29col rule_evaluation_context_name format a28col 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.** sys streams$_evaluation_context_**_tag()='y')
stradmin chenhao14((:ddl.get_object_owner()='chenhao' or:ddl.get_sys streams$_evaluation_contextbase_table_owner()='chenhao'
stradmin chenhao16((:dml.get_object_owner()='chenhao')and:dml.** sys streams$_evaluation_context_**_tag()='y' and:dml.get
stradmin chenhao17((:ddl.get_object_owner()='chenhao' or:ddl.get_sys streams$_evaluation_contextbase_table_owner()='chenhao'
sys alert_que tab.user_data.**_level<>32 and tab.user_dat sys aq$_alert_qt_v$1 a.**_group='high **aila'
3、为捕获进程（capture）添加 schema rule[sql]view plain copy
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 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 **:'|l_dml_rule_name);dbms_output.put_line('ddl rule name **:'|l_ddl_rule_name);end;dml rule name **:"strmadmin"."scott13
ddl rule name **:"strmadmin"."scott14
pl/sql procedure successfully completed.
4、查看创建的 rule 信息
[sql]view plain copy
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-用于捕获dmlchenhao14 ddl positive ruleset$_15 capture capture_sm1-用于捕获ddl5、为capture进程 table rule(当捕获到表 scott.bouns 则不给予处理，创建的是negative rule)[sql]view plain copy
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 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 **:'|l_dml_rule_name);dbms_output.put_line('ddl rule name **:'|l_ddl_rule_name);end;dml rule name **:"strmadmin"."bonus26
ddl rule name **:"strmadmin"."bonus27
pl/sql procedure successfully completed.
6、查看创建的rule
[sql]view plain copy
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 setbonus26 dml negative ruleset$_28 capture dbxa_cap-negative rule set7、查看 rule condition
[sql]view plain copy
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.**_**_tag()='y' and:ddl.get_source_database_name()='dbxa.world')
scott13((:dml.get_object_owner()='scott')and:dml.**_**_tag()='y' and:dml.get_source_database_name()='dbxa.world')8、自定义 rule(只执行 dml 的insert 和 update)[sql]view plain copy
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 **:'|l_dml_rule_name);end;dml rule name **:"strmadmin"."scott40
pl/sql procedure successfully completed.
rule_name rule_condition
scott40((((:dml.get_object_owner()='scott')and:dml.**_**_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
[sql]view plain copy
dba_streams_unsupported<span style="white-space:pre"></span>-不支持的对象dba_streams_columns-不支持的列
10、添加positive global rule
[sql]view plain copy
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 **:'|l_dml_rule_name);dbms_output.put_line('ddl rule name **:'|l_ddl_rule_name);end;dml rule name **:"strmadmin"."dbxa29
ddl rule name **:"strmadmin"."dbxa30
pl/sql procedure successfully completed.
rule_name rule_condition
dbxa29(:dml.**_**_tag()='y' and:dml.get_source_database_name()='dbxa.world')
dbxa30(:ddl.**_**_tag()='y' and:ddl.get_source_database_name()='dbxa.world')
11、添加 subset rule(对scott.emp中deptno 为20、30的dml进行处理)[sql]view plain copy
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 **:'|...