set echo on
set serveroutput on
spool setup.out
connect sys/oracle@hora10r24 as sysdba
exec dbms_propagation_adm.stop_propagation('STREAMS_PROPAGATION')
exec dbms_streams_adm.remove_streams_configuration;
drop user strmadmin cascade;
create user strmadmin identified by streams;
grant DBA, IMP_FULL_DATABASE, EXP_FULL_DATABASE to strmadmin;
grant CREATE DATABASE LINK to strmadmin;
grant CREATE ANY DIRECTORY to strmadmin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/
ALTER USER strmadmin DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
drop user test cascade;
create user test identified by test;
grant connect, resource to test;
alter user test default tablespace users;
connect test/test@hora10r24
CREATE TABLE TESTA ( COL1A VARCHAR(4) PRIMARY KEY);
grant select, update, delete, insert on test.testA to strmadmin;
connect sys/oracle@hora10r242 as sysdba;
exec dbms_streams_adm.remove_streams_configuration;
drop user strmadmin cascade;
create user strmadmin identified by streams;
grant DBA, IMP_FULL_DATABASE, EXP_FULL_DATABASE to strmadmin;
grant CREATE DATABASE LINK to strmadmin;
grant CREATE ANY DIRECTORY to strmadmin;
BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee => 'strmadmin',
grant_privileges => true);
END;
/
ALTER USER strmadmin DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS;
drop user test cascade;
create user test identified by test;
grant connect, resource to test;
alter user test default tablespace users;
connect test/test@hora10r242
CREATE TABLE TESTA ( COL1A VARCHAR(4) PRIMARY KEY);
grant select, update, delete, insert on test.testa to strmadmin;
connect STRMADMIN/streams@hora10r24;
CREATE DATABASE LINK hora10r242 connect to strmadmin identified by streams using 'hora10r242';
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'streams_capture_qt',
queue_name => 'streams_capture_q',
queue_user => 'strmadmin');
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'test.testa',
streams_name => 'STREAMS_PROPAGATION',
source_queue_name => 'STRMADMIN.STREAMS_CAPTURE_Q',
destination_queue_name => 'STRMADMIN.STREAMS_APPLY_Q@hora10r242.uk.oracle.com',
include_dml => true,
include_ddl => true,
source_database => 'hora10r24.uk.oracle.com',
inclusion_rule => true,
queue_to_queue => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'test.testa',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'STRMADMIN.STREAMS_CAPTURE_Q',
include_dml => true,
include_ddl => true,
source_database => 'hora10r24.uk.oracle.com',
include_tagged_lcr => false,
inclusion_rule => true);
END;
/
connect STRMADMIN/streams@hora10r242;
CREATE DATABASE LINK hora10r24 connect to STRMADMIN identified by streams using 'hora10r24';
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'STREAMS_APPLY_QT',
queue_name => 'STREAMS_APPLY_Q',
queue_user => 'STRMADMIN');
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'test.testa',
streams_type => 'apply',
streams_name => 'STREAMS_APPLY',
queue_name => 'STRMADMIN.STREAMS_APPLY_Q',
include_dml => true,
include_ddl => false,
source_database => 'hora10r24.uk.oracle.com',
include_tagged_lcr => false,
inclusion_rule => true);
END;
/
connect STRMADMIN/streams@hora10r24;
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@hora10r242.uk.oracle.com(
source_object_name => 'test.testa',
source_database_name => 'hora10r24.uk.oracle.com',
instantiation_scn => iscn);
END;
/
connect STRMADMIN/streams@hora10r242;
begin
dbms_apply_adm.start_apply('STREAMS_APPLY');
end;
/
connect STRMADMIN/streams@hora10r24;
begin
dbms_capture_adm.start_capture('STREAMS_CAPTURE');
end;
/
connect test/test@hora10r24
INSERT INTO testa VALUES('A');
commit;
connect sys/oracle@hora10r24 as sysdba
exec dbms_lock.sleep(60)
connect test/test@hora10r242
select * from testa;
spool off
Add the table to the environment.
set echo on
set serveroutput on
spool add.out
connect test/test@hora10r24
CREATE TABLE TESTB ( COL1B VARCHAR(4) PRIMARY KEY);
grant select, update, delete, insert on test.testb to strmadmin;
INSERT INTO testb VALUES('B');
commit;
connect STRMADMIN/streams@hora10r242;
begin
dbms_apply_adm.stop_apply('STREAMS_APPLY');
end;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'test.testb',
streams_type => 'apply',
streams_name => 'STREAMS_APPLY',
queue_name => 'STRMADMIN.STREAMS_APPLY_Q',
include_dml => true,
include_ddl => false,
source_database => 'hora10r24.uk.oracle.com',
include_tagged_lcr => false,
inclusion_rule => true);
END;
/
connect STRMADMIN/streams@hora10r24;
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => 'test.testb',
streams_name => 'STREAMS_PROPAGATION',
source_queue_name => 'STRMADMIN.STREAMS_CAPTURE_Q',
destination_queue_name => 'STRMADMIN.STREAMS_APPLY_Q@hora10r242.uk.oracle.com',
include_dml => true,
include_ddl => true,
source_database => 'hora10r24.uk.oracle.com',
inclusion_rule => true,
queue_to_queue => true);
END;
/
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'test.testb',
streams_type => 'capture',
streams_name => 'STREAMS_CAPTURE',
queue_name => 'STRMADMIN.STREAMS_CAPTURE_Q',
include_dml => true,
include_ddl => true,
source_database => 'hora10r24.uk.oracle.com',
include_tagged_lcr => false,
inclusion_rule => true);
END;
/
--表在应用层面初始化。
--以下操作是由datapump工具所进行的。
connect test/test@hora10r242
CREATE TABLE TESTB ( COL1B VARCHAR(4) PRIMARY KEY);
grant select, update, delete, insert on test.testb to strmadmin;
INSERT INTO testb VALUES('B');
commit;
connect STRMADMIN/streams@hora10r24;
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@hora10r242.uk.oracle.com(
source_object_name => 'test.testb',
source_database_name => 'hora10r24.uk.oracle.com',
instantiation_scn => iscn);
END;
/
connect STRMADMIN/streams@hora10r242;
begin
dbms_apply_adm.start_apply('STREAMS_APPLY');
end;
/
connect test/test@hora10r24
INSERT INTO testb VALUES('B1');
commit;
connect sys/oracle@hora10r24 as sysdba
exec dbms_lock.sleep(30)
connect test/test@hora10r242
select * from testb;
spool off

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; DBMS_APPLY_ADM.STOP_APPLY; DBMS_STREAMS_ADM.ADD_TABLE_RULES; DBMS_APPLY_ADM.START_APPLY; DBMS_CAPTURE_ADM.START_CAPTURE; STREAMS; INSTANTIATION