Document ID:        34548.1
Subject:            Sample setup scripts - Setting up Replication
Author:             toscroft
Last Revision Date: 13 May       1996


-- Connect on REP

connect repadmin/repadmin

--
-- Suspend any replication activity on this schema. 
--

--execute dbms_repcat.suspend_master_activity('REPUSER');

--
-- Drop any existing replication support for objects in schema.
--

execute dbms_repcat.drop_master_repschema(              -
                                sname => 'REPUSER',     -
                                drop_contents => TRUE,  -
                                all_sites => TRUE);

--
-- Wait 5 minutes for any changes made to be propagated to other sites.
--
--execute dbms_lock.sleep(300);

-- 
-- Create objects for replication
--

connect repuser/repuser

DROP TABLE EMP;
DROP TABLE DEPT;

CREATE TABLE EMP
       (EMPNO NUMBER(4),
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7,2),
        COMM NUMBER(7,2),
        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP VALUES
        (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP VALUES
        (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP VALUES
        (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO EMP VALUES
        (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP VALUES
        (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO EMP VALUES
        (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO EMP VALUES
        (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP VALUES
        (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP VALUES
        (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO EMP VALUES
        (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO EMP VALUES
        (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO EMP VALUES
        (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO EMP VALUES
        (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);

CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

INSERT INTO DEPT VALUES
        (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
        (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
        (40,'OPERATIONS','BOSTON');

--
-- Create a primary key as this, or a user-defined unique replication key, is
-- necessary for replication.
--

alter table emp add constraint pk_emp primary key (empno)
/
alter table dept add constraint pk_dept primary key (deptno)
/

--
-- Register these tables with the dictionary table, repcat, as repadmin.
-- The copy_rows = true parameter in dbms_repcat.create_master_repobject
-- means that a remote copy of the table will be created.
--

connect repadmin/repadmin

execute dbms_repcat.create_master_repschema(sname => 'REPUSER');

execute dbms_repcat.create_master_repobject(                    -
                                        sname => 'REPUSER',     -
                                        oname => 'EMP',         -
                                        type =>  'TABLE',       -
                                        use_existing_object => FALSE, -
                                        copy_rows => TRUE);

execute dbms_repcat.create_master_repobject(                    -
                                        sname => 'REPUSER',     -
                                        oname => 'DEPT',        -
                                        type =>  'TABLE',       -
                                        use_existing_object => FALSE, -
                                        copy_rows => TRUE); 

execute dbms_repcat.generate_replication_support(               -
                                        sname => 'REPUSER',     -
                                        oname => 'EMP',         -
                                        type =>  'TABLE');
                                         
execute dbms_repcat.generate_replication_support(               -
                                        sname => 'REPUSER',     -
                                        oname => 'DEPT',        -
                                        type =>  'TABLE');

--
-- Create the tables and populate them at the remote site.
--
execute dbms_repcat.add_master_database ('REPUSER', 'REP2.WORLD');

--
-- Wait until dba_repcatlog is empty then execute:
-- 
-- dbms_repcat.resume_master_activity('REPUSER');

--
-- Schedule execution by adding job to the job queue (dba_jobs).
-- The interval is set in the same way as for snapshots ie: as a fraction
-- of a day. The example below schedules replication for once every minute.
--

execute dbms_defer_sys.schedule_execution(dblink => 'REP2.WORLD',
                                          interval => 'sysdate + 1/1440',
                                          next_date => sysdate);