Define source table at DB1 machine
CREATE TABLE t_alper_source
AS
SELECT * FROM employees;
ALTER TABLE t_alper_source ADD CONSTRAINT pk_alper_source PRIMARY KEY (employee_id);
CREATE SNAPSHOT LOG ON t_alper_source;
Define snapshot at DB2 machine
CREATE SNAPSHOT "SS_ALPER_SOURCE"
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX TABLESPACE "USER_INDEX"
REFRESH FAST
AS
SELECT * FROM T_ALPER_SOURCE@DB1;
-- There should be two objects that are "Table" and "Materialized View" after executes.
Define synonym at DB2 machine
CREATE PUBLIC SYNONYM ALPER_TABLE FOR SS_ALPER_SOURCE;
Dynamic SQL for snapshots
SQL>
select 'create snapshot '||owner||'.ss_new_employees as select * from '||owner||'.new_employees@'||owner||'db;
create synonym '||owner||'.new_employees for '||owner||'.ss_new_employees;' from dba_snapshots where name='SS_EMPLOYEES';
CREATE TABLE t_alper_source
AS
SELECT * FROM employees;
ALTER TABLE t_alper_source ADD CONSTRAINT pk_alper_source PRIMARY KEY (employee_id);
CREATE SNAPSHOT LOG ON t_alper_source;
Define snapshot at DB2 machine
CREATE SNAPSHOT "SS_ALPER_SOURCE"
TABLESPACE "USERS"
BUILD IMMEDIATE
USING INDEX TABLESPACE "USER_INDEX"
REFRESH FAST
AS
SELECT * FROM T_ALPER_SOURCE@DB1;
-- There should be two objects that are "Table" and "Materialized View" after executes.
Define synonym at DB2 machine
CREATE PUBLIC SYNONYM ALPER_TABLE FOR SS_ALPER_SOURCE;
Dynamic SQL for snapshots
SQL>
select 'create snapshot '||owner||'.ss_new_employees as select * from '||owner||'.new_employees@'||owner||'db;
create synonym '||owner||'.new_employees for '||owner||'.ss_new_employees;' from dba_snapshots where name='SS_EMPLOYEES';