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 => ,
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();
(
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@]