Declarative Rule-Based transformation Streams-优快云博客

本文介绍Oracle Streams中声明式规则转换的应用实例，包括模式、表及字段的重命名、添加及删除等操作，展示了如何利用Oracle内置规则进行高效的数据刷新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

As I mentioned last time, there are 2 type of Rule transformations, one is declarative rule-based transformation (rules provided by Oracle itself), and the other is Custom rule-base transformation ( rules coded by Oracle users, write PL/SQL to realize the rule).[@more@]

Why Declarative Rule-based transformations, not Custom Rule-base transformation?

I, Declarative Rule-based transformation is realized by internal code, not by PL/SQL, the performance will be better over Custom rule-based transformation. So if the Declarative Rule-based transformation can achieve the goal, there is no need to code by yourself, choose it first.

II, it is maintenanced by Oracle Corp. Oracle users do not pay much cost for this.

Disadvantage of Declarative rule-based transformation: only provide few functions, from complex business logic; you will have to code by yourself.

As far as I know, it only provides only below functions (below example will show them):

I, Rename schema, rename schema in a row LCR.

II, Rename table, rename tables from a row LCR.

III, Rename column, rename columns from a row LCR.

IV, Add columns, it add new columns to row LCR.

V, Delete columns, excludes columns from row LCR.

But with customer’s rule, it can make it to realize complex business. For a new beginner to learn Streams duplication, the roadmap: Declarative rule-based transformation first, then custom rule-base transformation, so in this document, I will give examples to apply declarative rule-based transformation.

Environment:

OS: Linux 2.6.9-67.ELsmp

DB: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod

1) Example to rename schema, data refresh from schema SOUR to schema DEST:

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;

SUPPLEME SUP SUP SUP SUP

-------- --- --- --- ---

NO NO NO NO NO

SQL> alter database add supplemental log data;

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;

SUPPLEME SUP SUP SUP SUP

-------- --- --- --- ---

YES NO NO NO NO

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

User created.

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

User created.

SQL> grant connect,resource to sour;

Grant succeeded.

SQL> grant connect,resource to dest;

Grant succeeded.

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

User created.

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_1',

4 queue_table=>'queue_table_1');

5 end;

6 /

PL/SQL procedure successfully completed.

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_1',

7 include_dml=>true,

8 include_ddl=>true,

9 source_database=>null,

10 inclusion_rule=>true);

11 end;

12 /

begin

ERROR at line 1:

ORA-00258: manual archiving in NOARCHIVELOG mode must identify log

ORA-06512: at "SYS.DBMS_STREAMS_ADM_UTL_INVOK", line 289

ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 439

ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 397

ORA-06512: at line 2

SQL> conn / as sysdba

Connected.

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 2315255808 bytes

Fixed Size 1268776 bytes

Variable Size 738198488 bytes

Database Buffers 1560281088 bytes

Redo Buffers 15507456 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> conn strmadmin/strmadmin

Connected.

SQL> begin

dbms_streams_adm.add_schema_rules(

schema_name=>'sour',

streams_type=>'capture',

streams_name=>'capture_01',

queue_name=>'queue_1',

include_dml=>true,

include_ddl=>true,

source_database=>null,

inclusion_rule=>true);

end;

2 3 4 5 6 7 8 9 10 11 12

13 /

PL/SQL procedure successfully completed.

SQL> 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_1',

7 include_dml=>true,

8 include_ddl=>true,

9 source_database=>null,

10 inclusion_rule=>ture);

11 end;

12 /

inclusion_rule=>ture);

ERROR at line 10:

ORA-06550: line 10, column 17:

PLS-00201: identifier 'TURE' must be declared

ORA-06550: line 2, column 1:

PL/SQL: Statement ignored

SQL> c/ture/true/

10* inclusion_rule=>true);

SQL> /

PL/SQL procedure successfully completed.

SQL> set linesize 200;

SQL> select streams_name,streams_type,rule_name,rule_type from dba_streams_schema_rules;

STREAMS_NAME STREAMS_TYP RULE_NAME RULE_TY

------------------------------ ----------- ------------------------------ -------

CAPTURE_01 CAPTURE SOUR4 DML

CAPTURE_01 CAPTURE SOUR5 DDL

APPLY_01 APPLY SOUR7 DML

APPLY_01 APPLY SOUR8 DDL

SQL> begin

2 dbms_streams_adm.rename_schema(

3 rule_name=>'SOUR7',

4 from_schema_name=>'sour',

5 to_schema_name=>'dest',

6 operation=>'ADD');

7 end;

8 /

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> create table sour.schema_rename(a number, b date);

Table created.

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

Table created.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

2871472

SQL> begin

2 dbms_apply_adm.set_schema_instantiation_scn(

3 SOURCE_SCHEMA_NAME=>'sour',

4 SOURCE_DATABASE_NAME=>'',

5 INSTANTIATION_SCN=>2871472);

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> begin

2 dbms_apply_adm.set_schema_instantiation_scn(

3 SOURCE_SCHEMA_NAME=>'dest',

4 SOURCE_DATABASE_NAME=>'',

5 INSTANTIATION_SCN=>2871472);

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> begin

2 dbms_apply_adm.start_apply('APPLY_01');

3 end;

4 /

PL/SQL procedure successfully completed.

SQL> begin

2 dbms_capture_adm.start_capture('CAPTURE_01');

3 end;

4 /

PL/SQL procedure successfully completed.

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

1 row created.

SQL> commit;

Commit complete.

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select * from sour.schema_rename;

A B

---------- -------------------

1 2009-05-22 14:38:31

SQL> select * from dest.schema_rename;

no rows selected

=============================

SYS@ >select error_number,error_message from dba_apply_error;

ERROR_NUMBER

------------

ERROR_MESSAGE

--------------------------------------------------------------------------------

26687

ORA-26687: no instantiation SCN provided for "DEST"."SCHEMA_RENAME" in source database ""

=============================

SQL> delete from sour.schema_rename;

1 row deleted.

SQL> commit;

Commit complete.

SQL> begin

dbms_apply_adm.set_schema_instantiation_scn(

SOURCE_SCHEMA_NAME=>'sour',

SOURCE_DATABASE_NAME=>'',

INSTANTIATION_SCN=>2871472,

RECURSIVE=>true);

end;

/ 2 3 4 5 6 7 8

PL/SQL procedure successfully completed.

SQL> begin

dbms_apply_adm.set_schema_instantiation_scn(

SOURCE_SCHEMA_NAME=>'dest',

SOURCE_DATABASE_NAME=>'',

INSTANTIATION_SCN=>2871472,

RECURSIVE=>true);

end;

2 3 4 5 6 7 8

PL/SQL procedure successfully completed.

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

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dest.schema_rename;

A B

---------- -------------------

1 2009-05-22 14:45:15

2) Rename table, based on first example, to rename table SOUR.TEST1 to DEST.TABLE_RENAME

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

Table created.

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

Table created.

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

------------------------

2888913

SQL> begin

2 dbms_apply_adm.set_table_instantiation_scn(

3 source_object_name=>'sour.test1',

4 source_database_name=>'',

5 instantiation_scn=>2888913);

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> begin

2 dbms_apply_adm.set_table_instantiation_scn(

3 source_object_name=>'dest.table_rename',

4 source_database_name=>'',

5 instantiation_scn=>2888913);

6 end;

7 /

PL/SQL procedure successfully completed.

SQL> set linesize 200;

SQL> select streams_name,streams_type,rule_name,rule_type from dba_streams_schema_rules;

STREAMS_NAME STREAMS_TYP RULE_NAME RULE_TY

------------------------------ ----------- ------------------------------ -------

CAPTURE_01 CAPTURE SOUR4 DML

CAPTURE_01 CAPTURE SOUR5 DDL

APPLY_01 APPLY SOUR7 DML

APPLY_01 APPLY SOUR8 DDL

SQL> begin

2 dbms_streams_adm.rename_table(

3 rule_name=>'SOUR7',

4 from_table_name=>'sour.test1',

5 to_table_name=>'dest.table_rename',

6 operation=>'ADD');

7 end;

8 /

PL/SQL procedure successfully completed.

SQL> insert into sour.test1 values(-10,sysdate-30);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from sour.test1;

A B

---------- -------------------

-10 2009-05-12 17:04:32

SQL> select * from dest.table_rename;

A B

---------- -------------------

-10 2009-05-12 17:04:32

3) Rename columns

SQL> alter table dest.table_rename add c date;

Table altered.

SQL> select * from sour.test1;

A B

---------- -------------------

-10 2009-05-12 17:04:32

SQL> select * from dest.table_rename;

A B C

---------- ------------------- -------------------

-10 2009-05-12 17:04:32

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

1 row created.

SQL> commit;

Commit complete.

SQL> select * from sour.test1;

A B

---------- -------------------

-10 2009-05-12 17:04:32

-100 2009-03-04 09:59:50

SQL> select * from dest.table_rename;

A B C

---------- ------------------- -------------------

-10 2009-05-12 17:04:32

-100 2009-03-04 09:59:50

SQL> begin

dbms_streams_adm.rename_column(

rule_name=>'SOUR7',

table_name=>'sour.test1',

from_column_name=>'B',

to_column_name=>'C',

operation=>'ADD');

end;

/ 2 3 4 5 6 7 8 9

PL/SQL procedure successfully completed.

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

1 row created.

SQL> commit;

Commit complete.

SQL> select * from sour.test1;

A B

---------- -------------------

-200 2008-11-24 10:04:41

-10 2009-05-12 17:04:32

-100 2009-03-04 09:59:50

SQL> select * from dest.table_rename;

A B C

---------- ------------------- -------------------

-200 2008-11-24 10:04:41

-10 2009-05-12 17:04:32

-100 2009-03-04 09:59:50

4) Add columns

SQL> alter table dest.table_rename add d date;

Table altered.

SQL> begin

2 dbms_streams_adm.add_column(

3 rule_name=>'SOUR7',

4 table_name=>'sour.test1',

5 column_name=>'D',

6 column_function=>'sysdate',

7 operation=>'ADD');

8 end;

9 /

PL/SQL procedure successfully completed.

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

1 row created.

SQL> commit;

Commit complete.

SQL> select * from sour.test1;

A B

---------- -------------------

-200 2008-11-24 10:04:41

-10 2009-05-12 17:04:32

-100 2009-03-04 09:59:50

-300 2008-08-16 10:19:24

SQL> select * from dest.table_rename;

A B C D

---------- ------------------- ------------------- -------------------

-200 2008-11-24 10:04:41

-10 2009-05-12 17:04:32

-100 2009-03-04 09:59:50

-300 2008-08-16 10:19:24 2009-06-12 10:23:09

5) Delete columns

SQL> alter table sour.test1 add c date default sysdate;

Table altered.

SQL> begin

2 dbms_streams_adm.delete_column(

3 rule_name=>'SOUR7',

4 table_name=>'sour.test1',

5 column_name=>'C',

6 operation=>'ADD');

7 end;

8 /

PL/SQL procedure successfully completed.

SQL> insert into sour.test1(a,b) values(2,sysdate+2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dest.table_rename;

A B C D

---------- ------------------- ------------------- -------------------

-200 2008-11-24 10:04:41

-10 2009-05-12 17:04:32

-100 2009-03-04 09:59:50

-300 2008-08-16 10:19:24 2009-06-12 10:23:09

SQL> begin

2 dbms_streams_adm.delete_column(

3 rule_name=>'SOUR7',

4 table_name=>'sour.test1',

5 column_name=>'C',

6 operation=>'REMOVE');

7 end;

8 /

PL/SQL procedure successfully completed.

Get error from dba_apply "1430 ORA-01430: column being added already exists in table",it conflicts from the privious rules.

SQL> alter table sour.test1 drop column c;

Table altered.

SQL> alter table sour.test1 add e date default sysdate;

Table altered.

SQL> begin

2 dbms_streams_adm.delete_column(

3 rule_name=>'SOUR7',

4 table_name=>'sour.test1',

5 column_name=>'E',

6 operation=>'ADD');

7 end;

8 /

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

Insert data into sour.test1, which action caused apply process to be aborted. The error message from dba_apply_error is

12801 ORA-12801: error signaled in parallel query server P000

ORA-26673: duplicate column name C

Such message confused me. I inferred that the LCR kept in the buffered queue included the previous operations, so my next step is to

purge the queue:

SQL>declare

po dbms_aqadm.aq$_purge_options_t;

begin

po.block:=true;

po.delivery_mode:=DBMS_AQADM.PERSISTENT_OR_BUFFERED;

dbms_aqadm.purge_queue_table(

queue_table=>'strmadmin.queue_table_1',

purge_condition=>null,

purge_options=>po); 2 3 4 5 6 7 8 9

10 end;

11 /

PL/SQL procedure successfully completed.

SQL>select * from v$buffered_queues;

QUEUE_ID QUEUE_SCHEMA QUEUE_NAME STARTUP_TIME NUM_MSGS SPILL_MSGS CNUM_MSGS CSPILL_MSGS EXPIRED_MSGS

---------- ------------------------------ ------------------------------ ------------------- ---------- ---------- ---------- ----------- ------------

13827 STRMADMIN QUEUE_1 2009-06-11 14:21:56 0 0 0 0 0

12082 SYS KUPC$C_1_20080604054235 2009-06-11 14:16:12 0 0 0 0 0

And tried to pump data into sour.test, the error remains, lastly, reset the instantiation scn from both schema:

SQL> begin

2 dbms_apply_adm.set_schema_instantiation_scn(

3 source_schema_name=>'sour',

4 source_database_name=>'',

5 instantiation_scn=>2979049,

6 recursive=>true);

7 end;

8 /

PL/SQL procedure successfully completed.