Stream таблиц

создать пользователей STREAM_ADMIN на обоих базах

DECLARE
tables varchar2(4000);
BEGIN
tables := '"SHM1"."TABLE1",
"SHM1"."TABLE2"';
DBMS_STREAMS_ADM.MAINTAIN_TABLES(
table_names   => tables,
source_directory_object      =>      'DTPUMP',
destination_directory_object     =>      'DTPUMP',
source_database          =>      'src.domain',
destination_database         =>      target.domain',
perform_actions              =>      TRUE,
script_name                      =>      'streams_setup.sql',
script_directory_object          =>      'DTPUMP',
dump_file_name               =>      'streams_setup.dmp',
capture_queue_user           =>       '"STREAM_ADMIN"',
apply_queue_user             =>      '"STREAM_ADMIN"',
log_file                 =>      'streams_setup.log',
bi_directional               =>      false,
include_ddl                  =>      true);
END;
/


на исходной базе пронать инициализацию
BEGIN
for rec in (select distinct schema_name||'.'||object_name  as tbl from ALL_STREAMS_RULES)
loop
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION (
  table_name   => rec.tbl
, supplemental_logging => 'all'
);
end loop;
END;
/

<scn>=select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual


на таргетной тоже, scn взять с исходной

BEGIN
for rec in (select distinct schema_name||'.'||object_name  as tbl from ALL_STREAMS_RULES)
loop
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN (
  source_object_name   => rec.tbl
, source_database_name => 'target.domain'
, instantiation_scn    => <scn>
);
end loop;
END;
/

создание STREAM ADMIN

CREATE TABLESPACE STREAM_TBS DATAFILE '+ASMDATA' 
  SIZE 1G REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

CREATE USER stream_admin IDENTIFIED BY <password>
   DEFAULT TABLESPACE stream_tbs
   QUOTA UNLIMITED ON stream_tbs;

GRANT CONNECT, RESOURCE, DBA TO stream_admin;

BEGIN
  DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee          => 'stream_admin',    
    grant_privileges => true);
END;
/

COMMIT
/