Useful shortcuts for vi editor

Wednesday 26 October 2016

How to define snapshot and synonym?

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';