oracle 10 g stream table

一、要求

二、创建用户及搭建环境

1、首先是source
conn / as sysdba
create tablespace stream_tbs datafile 'tream01.dbf' size 200m;
create user stradmin identified by stradmin default tablespace stream_tbs;
grant dba to stradmin;

2、target创建专用表空间及用户。
create tablespace stream_tbs datafile 'stream01.dbf' size 200m;
create user stradmin identified by stradmin default tablespace stream_tbs;
grant dba to stradmin;

3、切换回source数据库，以streams的操作用户stradmin连接
conn stradmin/stradmin

创建连接到target的数据库链：
create database link db2 connect to stradmin identified by stradmin using 'db2';
exec dbms_streams_adm.set_up_queue();

4、再次切换到target数据库，以streams的操作用户stradmin连接
conn stradmin/stradmin
create database link db1 connect to stradmin identified by stradmin using 'db1';
exec dbms_streams_adm.set_up_queue();

三、配置复制过程
1、首先到source数据库
--创建捕获规则
begin
dbms_streams_adm.add_table_rules(
table_name => 'scott.emp',
streams_type => 'capture',
streams_name => 'capture_stream',
queue_name => 'stradmin.streams_queue',
include_dml => true,
include_ddl => true,
inclusion_rule => true);
end;
/

--创建传播规则
begin
dbms_streams_adm.add_table_propagation_rules(
table_name => 'scott.emp',
streams_name => 'sour_to_targ',
source_queue_name => 'stradmin.streams_queue',
destination_queue_name => 'stradmin.streams_queue@db2',
include_dml => true,
include_ddl => true,
source_database => 'db1',
inclusion_rule => true,
queue_to_queue => true);
end;
/

select capture_name,status from dba_capture;

capture_name status
------------------------------ --------
capture_stream disabled

2、切换到target数据库
--创建应用规则
begin
dbms_streams_adm.add_table_rules(
table_name => 'scott.emp',
streams_type => 'apply',
streams_name => 'apply_stream',
queue_name => 'stradmin.streams_queue',
include_dml => true,
include_ddl => true,
source_database => 'db1',
inclusion_rule => true);
end;
/

。
--初始化数据，首先从源库导出，再执行导入
impdp system/sys network_link=db1 schemas=hr

--设置起始应用的scn值
declare
iscn number;
begin
iscn := dbms_flashback.get_system_change_number();
dbms_apply_adm.set_table_instantiation_scn@db2(
source_object_name => 'hr1.test',
source_database_name => 'db1',
instantiation_scn => iscn);
end;
/

--启动应用进程
exec dbms_apply_adm.start_apply('apply_stream');
select apply_name,status from dba_apply;

3、切换到source数据库，启动捕获进程
exec dbms_capture_adm.start_capture('capture_stream');
select capture_name,status from dba_capture;

capture_name status
------------------------------ --------
capture_stream enabled

四、测试

http://blog.itpub.net/post/29894/470010 (引用上述)==============================================================

[@more@]

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

转载于:http://blog.itpub.net/9879835/viewspace-1060464/