begin
dbms_streams_adm.set_up_queue(
queue_table=> 'source_queue_table',
queue_name=> 'source_queue'
);
end;
begin
dbms_streams_adm.set_up_queue(
queue_table=> 'target_queue_table',
queue_name=> 'target_queue'
);
end;
===========================
source database:
exec dbms_streams_adm.set_up_queue();
target database:
exec dbms_streams_adm.set_up_queue();
grant connect,resource,dba,aq_administrator_role to strmadmin;
begin
dbms_streams_auth.grant_admin_privilege(
grantee => 'strmadmin',
grant_privileges => true);
end;
------------------------------------------------------------------------------------------------------------------------------
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'scott',
streams_type=> 'capture',
streams_name=> 'capture_stream',
queue_name=> 'strmadmin.streams_queue',
include_dml=> true,
include_ddl=> true,
inclusion_rule => true);
end;
===========================
begin
dbms_streams_adm.add_schema_rules(
schema_name => 'scott',
streams_type=> 'capture',
streams_name=> 'capture_stream',
queue_name=> 'strmadmin.source_queue',
include_dml=> true,
include_ddl=> true,
inclusion_rule => true);
end;
------------------------------------------------------------------------------------------------------------------------------
begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name=> 'scott',
streams_name=> 'source_to_target',
source_queue_name=> 'strmadmin.streams_queue',
destination_queue_name=> 'strmadmin.streams_queue@test.com.cn',
include_ddl=> true,
include_dml=> true,
inclusion_rule => true,
queue_to_queue=>true,
source_database=> 'oracle10g.com.cn'
);
end;
=============================
begin
dbms_streams_adm.add_schema_propagation_rules(
schema_name=> 'scott',
streams_name=> 'source_to_target',
source_queue_name=> 'strmadmin.source_queue',
destination_queue_name=> 'strmadmin.target_queue@test.com.cn',
include_ddl=> true,
include_dml=> true,
inclusion_rule => true,
queue_to_queue=>true,
source_database=> 'oracle10g.com.cn'
);
end;
------------------------------------------------------------------------------------------------------------------------------connect strmadmin/strmadmin@oracle10g
set serveroutput on
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_OUTPUT.PUT_LINE ('Instantiation SCN is: ' || iscn);
END;
connect strmadmin/strmadmin
BEGIN
DBMS_APPLY_ADM.SET_SCHEMA_INSTANTIATION_SCN(
source_schema_name=> 'scott',
source_database_name => 'oracle10g',
instantiation_scn => &iscn);
END;
===========================================
DECLARE
iscn NUMBER;
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@test.com.cn(
source_object_name => 'scott',
source_database_name => 'oracle10g.com.cn',
instantiation_scn => iscn);
END;