通过写规则,控制streams复制.

下面的代码是通来写代码实现RULE,然后应用到streams,这个例子比较简单,做复杂的业务转换,可通过类似示例中的function rename_schema_01来实现.

Declarative Rule-Based transformation Streams能实现的功能,自己写规则都可以实现,更复杂的功能，可以通来PL/SQL来实现，然后包装成规则.

[@more@]

SQL> alter database add supplemental log data;

Database altered.

SQL> select log_mode SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DAT A_ALL from v$database;

SUPPLEMENTAL SUP SUP SUP SUP
------------ --- --- --- ---
ARCHIVELOG NO NO NO NO

SQL> create user sour identified by sour default tablespace users;

User created.

SQL> create user dest identified by dest default tablespace users;

User created.

SQL> create user strmadmin identified by strmadmin default tablespace users;

User created.

SQL> grant connect,resource to sour;

Grant succeeded.

SQL> grant connect,resource to dest;

Grant succeeded.

SQL> grant connect,resource,dba,aq_administrator_role to strmadmin;

Grant succeeded.

SQL> begin
2 dbms_streams_auth.grant_admin_privilege(
3 grantee=>'strmadmin',
4 grant_privileges=>true);
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> conn strmadmin/strmadmin
Connected.
SQL> begin
2 dbms_streams_adm.set_up_queue(
3 queue_name=>'queue_01',
4 queue_table=>'queue_tab_01');
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> create table sour.test1(a number, b date);

Table created.

SQL> create table dest.test1(a number, b date);

Table created.

SQL> begin
2 dbms_streams_adm.add_schema_rules(
3 schema_name=>'sour',
4 streams_type=>'CAPTURE',
5 streams_name=>'capture_01',
6 queue_name=>'queue_01',
7 include_dml=>true,
8 include_ddl=>false,
9 include_tagged_lcr=>false,
10 inclusion_rule=>true);
11 end;
12 /
PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 begin
2 dbms_streams_adm.add_schema_rules(
3 schema_name=>'sour',
4 streams_type=>'APPLY',
5 streams_name=>'apply_01',
6 queue_name=>'queue_01',
7 include_dml=>true,
8 include_ddl=>false,
9 include_tagged_lcr=>false,
10 inclusion_rule=>true);
11* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_apply_adm.set_parameter(
3 apply_name=>'apply_01',
4 parameter=>'disable_on_error',
5 value=>'n');
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
2870023

SQL> ed
Wrote file afiedt.buf

1 begin
2 dbms_apply_adm.set_schema_instantiation_scn(
3 source_schema_name=>'sour',
4 source_database_name=>'',
5 instantiation_scn=>2870023,
6 recursive=>true);
7* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

1 begin
2 dbms_apply_adm.set_schema_instantiation_scn(
3 source_schema_name=>'dest',
4 source_database_name=>'',
5 instantiation_scn=>2870023,
6 recursive=>true);
7* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> create or replace function rename_schema_01(in_any IN sys.anydata) return sys.anydata
2 is
3 lcr sys.lcr$_row_record;
4 ret pls_integer;
5 objnm varchar2(30);
6 begin
7 ret := in_any.getobject(lcr);
8 objnm := lcr.get_object_name;
9 lcr.set_object_owner('DEST');
10 return sys.anydata.convertobject(lcr);
11 end;
12 /

Function created.

SQL> begin
2 dbms_rule_adm.create_rule_set(
rule_set_name=>'strmadmin.rlset_apply_01',
evaluation_context=>'sys.streams$_evaluation_context');
end; 3 4 5
6 /

PL/SQL procedure successfully completed.

SQL> declare
2 action_ctx sys.re$nv_list ;
3 ac_name VARCHAR2(30) := 'STREAMS$_TRANSFORM_FUNCTION' ;
4 begin
5 action_ctx := sys.re$nv_list(sys.re$nv_array()) ;
6 action_ctx.add_pair(ac_name, sys.anydata.convertvarchar2('STRMADMIN.RENAME_SCHEMA_01')) ;
7 dbms_rule_adm.create_rule(
8 rule_name=>'strmadmin.rule_for_test1',
9 condition=>':dml.get_object_owner() = ''SOUR'' AND ' || ':dml.is_null_tag() = ''Y''',
10 evaluation_context=>'sys.streams$_evaluation_context',
11 action_context => action_ctx) ;
12
13 dbms_rule_adm.add_rule(
14 rule_set_name=>'strmadmin.rlset_apply_01',
15 rule_name=>'strmadmin.rule_for_test1') ;
16 end ;
17 /

PL/SQL procedure successfully completed.

SQL> select apply_name,rule_set_name ,status from dba_apply;
APPLY_NAME RULE_SET_NAME STATUS
------------------------------ ------------------------------ --------
APPLY_01 RULESET$_4 DISABLED
SQL> begin
2 dbms_apply_adm.alter_apply(
3 apply_name=>'apply_01',
4 rule_set_name=>'rlset_apply_01');
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select apply_name,rule_set_name ,status from dba_apply;
APPLY_NAME RULE_SET_NAME STATUS
------------------------------ ------------------------------ --------
APPLY_01 RLSET_APPLY_01 DISABLED

SQL> exec dbms_apply_adm.start_apply('apply_01');

PL/SQL procedure successfully completed.

SQL> select status from dba_capture;

STATUS
--------
ENABLED

SQL> insert into sour.test1 values(1,sysdate-20);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from sour.test1;

A B
---------- -------------------
1 2009-05-30 18:39:11

SQL> select * from dest.test1;

A B
---------- -------------------
1 2009-05-30 18:39:11

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45188/viewspace-1023323/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45188/viewspace-1023323/