Database altered.
SQL> show parameter global_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_names boolean TRUE

SQL> create tablespace stream_tbs datafile '/u01/oradata/oracle10/stream_tbs.dbf' size 100m;

Tablespace created.

SQL> create user strmadmin identified by oracle default tablespace stream_tbs;

User created.

SQL> grant dba to strmadmin;

Grant succeeded.

Database altered.
SQL> show parameter global_name
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
global_names boolean TRUE

SQL> create tablespace stream_tbs datafile 'd:apporadataorclstream_tbs.dbf' size 100m;

Tablespace created.

SQL> create user strmadmin identified by oracle default tablespace stream_tbs;

User created.

SQL> grant dba to strmadmin;

Grant succeeded.

SQL> conn strmadmin/oracle

Connected.
SQL> create database link test.com.cn connect to strmadmin identified by oracle using 'test';

Database link created.

database link必须和target 数据库的global_name必须一样(修改global name使用alter database rename global_name to 'xxxxx';)

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
ORACLE10G.COM.CN

SQL> exec dbms_streams_adm.set_up_queue();

PL/SQL procedure successfully completed.

Connected.
SQL> create database link oracle10g.com.cn connect to strmadmin identified by oracle using 'oracle10g';
Database link created.

SQL> select * from global_name;

GLOBAL_NAME
-------------------------------------------------------
TEST.COM.CN

SQL> exec dbms_streams_adm.set_up_queue();

PL/SQL procedure successfully completed.

SQL> begin

2 dbms_streams_adm.add_table_rules(
3 table_name=>'scott.emp_test',
4 streams_type=>'capture',
5 streams_name=>'capture_stream',
6 queue_name=>'strmadmin.streams_queue',
7 include_dml=>true,
8 include_ddl=>true,
9 inclusion_rule => true
10 );
11 end;
12 /
PL/SQL procedure successfully completed.

2 dbms_streams_adm.add_table_propagation_rules(

3 table_name=>'scott.emp_test',

4 streams_name=>'source_to_target',

5 source_queue_name=>'strmadmin.streams_queue',

6 destination_queue_name=>'strmadmin.streams_queue@test.com.cn',

7 include_dml=>true,

8 include_ddl=>true,

9 inclusion_rule => true,

10 queue_to_queue=>true,

11 source_database=>'oracle10g.com.cn'

12 );

13 end;

14 /

PL/SQL procedure successfully completed.

SQL> select capture_name,status from dba_capture;

CAPTURE_NAME STATUS
------------------------------ --------
CAPTURE_STREAM DISABLED

在target端创建应用进程

SQL> begin
2 dbms_streams_adm.add_table_rules(
3 table_name =>'scott.emp_test',
4 streams_type => 'apply',
5 streams_name => 'apply_stream',
6 queue_name => 'strmadmin.streams_queue',
7 include_dml => true,
8 include_ddl => true,
9 source_database => 'oracle10g.com.cn',
10 inclusion_rule => true);
11 end;
12 /

在target端设置起始应用scn

SQL> DECLARE
2 iscn NUMBER;
3 BEGIN
4 iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
5 DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@test.com.cn(
6 source_object_name => 'scott.emp_test',
7 source_database_name => 'oracle10g.com.cn',
8 instantiation_scn => iscn);
9 END;
10 /

PL/SQL procedure successfully completed.

在target端启动应用进程

SQL> exec dbms_apply_adm.start_apply('apply_stream')

PL/SQL procedure successfully completed.

SQL> select apply_name,status from dba_apply;

APPLY_NAME STATUS
------------------------------------------------------------ ----------------
APPLY_STREAM ENABLED

在source端启动capture进程

SQL> exec dbms_capture_adm.start_capture('capture_stream');

PL/SQL procedure successfully completed.

SQL> select capture_name,status from dba_capture;

CAPTURE_NAME STATUS
------------------------------ --------
CAPTURE_STREAM ENABLED

SQL> insert into emp_test select * from emp_test;

18432 rows created.

SQL> select count(*) from emp_test;

COUNT(*)

----------

36863

SQL> alter table emp_test add test varchar2(100);

Table altered.

SQL> desc emp_test

Name Null? Type

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

EMPNO NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

TEST VARCHAR2(100)

在target database端检验结果

SQL> select count(*) from emp_test;

COUNT(*)
----------
36863

SQL> desc emp_test
Name Null? Type
----------------------------------------------------------------------------------------- -------- ---------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
TEST VARCHAR2(100