A very simple example of synchronizing databases
Following are the listings of three DDL scripts that provide a simple example
of the type of synchronization script that TOYS generates.
The first script is the DDL required to create the
working database in it's "before" state.
The second script is the DDL required to create the
reference database. This is also the working database in it's "after" state.
The third script is the DDL synchronization script
generated by TOYS.
It is the DDL / DML required to transform the working database from it's "before" state
to it's "after" state.
That is, to make the working database identical to [synchronized with] the reference database.
Note that the following changes were required to be made to the original model (i.e. the working database
in it's "before" state) to create the reference database (i.e. the working database in it's "after" state).
- Added grants of SELECT and REFERENCES to the HR schema on the DEPT and EMP tables.
- The table BONUS had the ENAME column dropped and replaced by the column EMPNO of type NUMBER (4)
and with a not null constraint named BONUS_N01.
It did not make sense to use ENAME as the key.
- A foreign key called BONUS_F01 was added to reference EMP (EMPNO).
- A not null constraint named DEPT_N01 was added to DEPT.DEPNO.
- Not null constraints were added to EMP.DEPNO (named EMP_N01), EMP.EMPNO (named EMP_N02)
and EMP.ENAME (named EMP_N03).
- EMP.HIREDATE was changed from a DATE to a TIMESTAMP WITH TIME ZONE. A time zone does not really
make sense in this case but play along anyhow. This particular example was chosen because it domonstrates
how TOYS attempts to perform "sensible" conversions.
- The name of the primary key [and its index] was changed from PK_EMP to EMP_PK.
- The name of the foreign key FK_DEPTNO was changed to EMP_F01.
All in all, a simple set of changes but look at the third script to see the amount of DDL required.
You could shorten it a tad but it will be less robust!
Top of Page
Second Script
Third Script
DDL script to create the working database in its "before" state.
REMARK
REMARK Tool for Organizing Your Schemas (TOYS)
REMARK Copyright (c) Impact Systems Pty. Ltd., 2002-2005.
REMARK http://www.impacttoys.com
REMARK
REMARK DDL generated by TOYS 1.0.30.0 on 2005-08-31 08:39:04 +10:00.
REMARK
REMARK Target compatibility is Oracle 9iR2 (9.2).
REMARK
REMARK Schema meta-data sourced from:
REMARK
REMARK Archive Name: Before
REMARK Database Name: MARS
REMARK Server Type: ORACLE
REMARK Version: 9.2.0.1.0
REMARK Session: BEFORE@mars
REMARK Timestamp: 2003-10-25 16:43:10 +10:00
REMARK
SET escape \
SET escape OFF
SET define '&'
SET define OFF
SET concat .
SET concat ON
SET sqlblanklines ON
SET define ON
CONNECT BEFORE/&&BEFORE_PWD.&&AT_DSN.
SET define OFF
CREATE SEQUENCE deptno_seq
MINVALUE 1
MAXVALUE 99
INCREMENT BY 1
NOCYCLE
NOORDER
NOCACHE;
CREATE SEQUENCE empno_seq
MINVALUE 1000
MAXVALUE 9999
INCREMENT BY 1
NOCYCLE
ORDER
CACHE 50;
CREATE TABLE bonus
( ename VARCHAR2 (10 BYTE) CONSTRAINT bonus_n11 NOT NULL ENABLE,
job VARCHAR2 (9 BYTE) NULL,
sal NUMBER NULL,
comm NUMBER NULL )
LOGGING;
CREATE UNIQUE INDEX bonus_u01 ON bonus
( ename )
LOGGING;
ALTER TABLE bonus
ADD CONSTRAINT bonus_pk
PRIMARY KEY ( ename )
DISABLE;
ALTER TABLE bonus
MODIFY PRIMARY KEY
USING INDEX bonus_u01
ENABLE;
CREATE TABLE dept
( deptno NUMBER (2) NULL,
dname VARCHAR2 (14 BYTE) NULL,
loc VARCHAR2 (13 BYTE) NULL )
LOGGING;
CREATE UNIQUE INDEX pk_dept ON dept
( deptno )
LOGGING;
ALTER TABLE dept
ADD CONSTRAINT pk_dept
PRIMARY KEY ( deptno )
DISABLE;
ALTER TABLE dept
MODIFY PRIMARY KEY
USING INDEX pk_dept
ENABLE;
CREATE TABLE emp
( empno NUMBER (4) NULL,
ename VARCHAR2 (10 BYTE) NULL,
job VARCHAR2 (9 BYTE) NULL,
mgr NUMBER (4) NULL,
hiredate DATE NULL,
sal NUMBER (7,2) NULL,
comm NUMBER (7,2) NULL,
deptno NUMBER (2) NULL )
LOGGING;
CREATE UNIQUE INDEX pk_emp ON emp
( empno )
LOGGING;
ALTER TABLE emp
ADD CONSTRAINT pk_emp
PRIMARY KEY ( empno )
DISABLE;
ALTER TABLE emp
MODIFY PRIMARY KEY
USING INDEX pk_emp
ENABLE;
CREATE TABLE salgrade
( grade NUMBER NULL,
losal NUMBER NULL,
hisal NUMBER NULL )
LOGGING;
CREATE OR REPLACE FORCE VIEW emp_v
( empno,
ename,
job,
mgr,
hiredate,
deptno,
dname,
loc ) AS
SELECT empno
, ename
, job
, mgr
, hiredate
, deptno
, dname
, loc
FROM emp e, dept d
WHERE d.deptno = e.deptno
/
ALTER TABLE emp
ADD CONSTRAINT fk_deptno
FOREIGN KEY
( deptno )
REFERENCES dept
ENABLE;
REMARK #### END OF GENERATED DDL ####
Top of Page
First Script
Third Script
DDL script to create the reference database (i.e. "after" state of working database).
REMARK
REMARK Tool for Organizing Your Schemas (TOYS)
REMARK Copyright (c) Impact Systems Pty. Ltd., 2002-2005.
REMARK http://www.impacttoys.com
REMARK
REMARK DDL generated by TOYS 1.0.30.0 on 2005-08-31 08:40:07 +10:00.
REMARK
REMARK Target compatibility is Oracle 9iR2 (9.2).
REMARK
REMARK Schema meta-data sourced from:
REMARK
REMARK Archive Name: After
REMARK Database Name: MARS
REMARK Server Type: ORACLE
REMARK Version: 9.2.0.1.0
REMARK Session: AFTER@mars
REMARK Timestamp: 2003-10-25 16:45:15 +10:00
REMARK
SET escape \
SET escape OFF
SET define '&'
SET define OFF
SET concat .
SET concat ON
SET sqlblanklines ON
SET define ON
CONNECT AFTER/&&AFTER_PWD.&&AT_DSN.
SET define OFF
CREATE SEQUENCE deptno_seq
MINVALUE 1
MAXVALUE 99
INCREMENT BY 1
NOCYCLE
NOORDER
NOCACHE;
CREATE SEQUENCE empno_seq
MINVALUE 1
MAXVALUE 9999
INCREMENT BY 1
NOCYCLE
ORDER
NOCACHE;
CREATE TABLE bonus
( empno NUMBER (4) CONSTRAINT bonus_n01 NOT NULL ENABLE,
job VARCHAR2 (9 BYTE) NULL,
sal NUMBER NULL,
comm NUMBER NULL )
LOGGING;
CREATE TABLE dept
( deptno NUMBER (2) CONSTRAINT dept_n01 NOT NULL ENABLE,
dname VARCHAR2 (14 BYTE) NULL,
loc VARCHAR2 (13 BYTE) NULL )
LOGGING;
GRANT REFERENCES ON dept TO hr;
GRANT SELECT ON dept TO hr;
CREATE UNIQUE INDEX pk_dept ON dept
( deptno )
LOGGING;
ALTER TABLE dept
ADD CONSTRAINT pk_dept
PRIMARY KEY ( deptno )
DISABLE;
ALTER TABLE dept
MODIFY PRIMARY KEY
USING INDEX pk_dept
ENABLE;
CREATE TABLE emp
( empno NUMBER (4) CONSTRAINT emp_n01 NOT NULL ENABLE,
ename VARCHAR2 (10 BYTE) CONSTRAINT emp_n02 NOT NULL ENABLE,
job VARCHAR2 (9 BYTE) NULL,
mgr NUMBER (4) NULL,
hiredate TIMESTAMP (6) WITH TIME ZONE NULL,
sal NUMBER (7,2) NULL,
comm NUMBER (7,2) NULL,
deptno NUMBER (2) CONSTRAINT emp_n03 NOT NULL ENABLE )
LOGGING;
GRANT REFERENCES ON emp TO hr;
GRANT SELECT ON emp TO hr;
CREATE UNIQUE INDEX emp_pk ON emp
( empno )
LOGGING;
ALTER TABLE emp
ADD CONSTRAINT emp_pk
PRIMARY KEY ( empno )
DISABLE;
ALTER TABLE emp
MODIFY PRIMARY KEY
USING INDEX emp_pk
ENABLE;
CREATE TABLE salgrade
( grade NUMBER NULL,
losal NUMBER NULL,
hisal NUMBER NULL )
LOGGING;
CREATE OR REPLACE FORCE VIEW emp_v
( empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno,
dname,
loc ) AS
SELECT empno
, ename
, job
, mgr
, hiredate
, sal
, comm
, deptno
, dname
, loc
FROM emp e, dept d
WHERE d.deptno = e.deptno
/
ALTER TABLE bonus
ADD CONSTRAINT bonus_f01
FOREIGN KEY
( empno )
REFERENCES emp
ENABLE;
ALTER TABLE emp
ADD CONSTRAINT emp_f01
FOREIGN KEY
( deptno )
REFERENCES dept
ENABLE;
REMARK #### END OF GENERATED DDL ####
Top of Page
First Script
Second Script
DDL script to transform the working database from its "before" to its "after" state.
/*----------------------------------------------------------------------------*\
Tool for Organizing Your Schemas (TOYS)
Copyright (c) Impact Systems Pty. Ltd., 2002-2005.
http://www.impacttoys.com
DDL generated by TOYS 1.0.30.0 on 2005-08-31 08:36:26 +10:00.
Target compatibility is Oracle 9iR2 (9.2).
Working database
----------------
Archive Name: Before
Database Name: MARS
Server Type: ORACLE
Version: 9.2.0.1.0
Session: BEFORE@mars
Timestamp: 2003-10-25 16:43:10 +10:00
Reference database
------------------
Archive Name: After
Database Name: MARS
Server Type: ORACLE
Version: 9.2.0.1.0
Session: AFTER@mars
Timestamp: 2003-10-25 16:45:15 +10:00
IMPORTANT
---------
The target for this synchronization script is the working database above.
That is, it is expected that the script will be executed against the same
schemas as contained in the working database snapshot. Should the script
be executed against other databases or other schemas then it may fail or
produce erroneous results. Worse still, it could destroy that database.
Before executing this script, you should check the impact summary below
and also sanity check the script to ensure it does not cause any unwanted
changes to your target database.
+--- P R I M A R Y A N A L Y S I S S U M M A R Y ---+
Object Type Drop Create Rebuild Modify Total
-------------- ------- ------- ------- ------- -------
Sequences 0 0 1 0 1
Tables 0 0 1 2 3
Views 0 0 1 0 1
+------ Affected CLUSTERS, TABLES and SEQUENCES ------+
Type Action Object Name
-------- ------- --------------------------------------
SEQUENCE REBUILD BEFORE . EMPNO_SEQ
TABLE REBUILD BEFORE . EMP
TABLE MODIFY BEFORE . BONUS
TABLE MODIFY BEFORE . DEPT
\*----------------------------------------------------------------------------*/
SET escape '\'
SET escape OFF
SET define '&'
SET define OFF
SET concat '.'
SET concat ON
SET sqlblanklines ON
SET linesize 160
WHENEVER SQLERROR EXIT FAILURE
SET define ON
CONNECT BEFORE/&&BEFORE_PWD.&&AT_DSN.
SET define OFF
DROP VIEW emp_v;
ALTER TABLE emp DROP CONSTRAINT fk_deptno;
ALTER TABLE bonus
MODIFY PRIMARY KEY
DISABLE
CASCADE
KEEP INDEX;
ALTER TABLE bonus DROP PRIMARY KEY CASCADE;
ALTER TABLE emp
MODIFY PRIMARY KEY
DISABLE
CASCADE
KEEP INDEX;
ALTER TABLE emp DROP PRIMARY KEY CASCADE;
ALTER TABLE bonus DROP CONSTRAINT bonus_n11;
DROP INDEX bonus_u01;
DROP INDEX pk_emp;
REMARK -- Renaming tables to temporary names --
ALTER TABLE emp RENAME TO emp$1;
REMARK -- Renaming re-built sequences to temporary names --
RENAME empno_seq TO empno_seq$2;
WHENEVER SQLERROR CONTINUE
WHENEVER SQLERROR EXIT FAILURE
REMARK -- Re-building sequences --
COLUMN last_number NEW_VALUE start_with
SELECT decode(greatest("EMPNO_SEQ$2".nextval,9999),
"EMPNO_SEQ$2".currval,1,
"EMPNO_SEQ$2".currval)
AS last_number
FROM dual
/
SET define ON
CREATE SEQUENCE empno_seq
MINVALUE 1
MAXVALUE 9999
INCREMENT BY 1
START WITH &start_with.
NOCYCLE
ORDER
NOCACHE;
SET define OFF
WHENEVER SQLERROR CONTINUE
DROP SEQUENCE empno_seq$2;
REMARK -- Re-building tables (Pass #1) --
WHENEVER SQLERROR EXIT FAILURE
CREATE TABLE emp
( empno NUMBER (4) CONSTRAINT emp_n01 NOT NULL ENABLE,
ename VARCHAR2 (10 BYTE) CONSTRAINT emp_n02 NOT NULL DISABLE,
job VARCHAR2 (9 BYTE) NULL,
mgr NUMBER (4) NULL,
hiredate TIMESTAMP (6) WITH TIME ZONE NULL,
sal NUMBER (7,2) NULL,
comm NUMBER (7,2) NULL,
deptno NUMBER (2) CONSTRAINT emp_n03 NOT NULL DISABLE )
LOGGING;
DECLARE
count$ NUMBER := 0;
BEGIN
FOR row$ IN
( SELECT
empno AS empno,
ename AS ename,
job AS job,
mgr AS mgr,
to_timestamp(to_char(hiredate,'syyyy-mm-dd hh24:mi:ss'),
'syyyy-mm-dd hh24:mi:ss') at local AS hiredate,
sal AS sal,
comm AS comm,
deptno AS deptno
FROM emp$1 )
LOOP
INSERT INTO emp
( empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno )
VALUES
( row$.empno,
row$.ename,
row$.job,
row$.mgr,
row$.hiredate,
row$.sal,
row$.comm,
row$.deptno );
count$ := count$ + 1;
IF MOD(count$,100) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/
DROP TABLE emp$1 CASCADE CONSTRAINTS;
WHENEVER SQLERROR CONTINUE
ALTER TABLE emp MODIFY CONSTRAINT emp_n03 ENABLE;
ALTER TABLE emp MODIFY CONSTRAINT emp_n02 ENABLE;
WHENEVER SQLERROR EXIT FAILURE
WHENEVER SQLERROR CONTINUE
GRANT REFERENCES ON emp TO hr;
GRANT SELECT ON emp TO hr;
REMARK -- Re-building tables (Pass #2) --
CREATE UNIQUE INDEX emp_pk ON emp
( empno )
LOGGING;
ALTER TABLE emp
ADD CONSTRAINT emp_pk
PRIMARY KEY ( empno )
DISABLE;
ALTER TABLE emp
MODIFY PRIMARY KEY
USING INDEX emp_pk
ENABLE;
REMARK -- Modifying tables --
WHENEVER SQLERROR EXIT FAILURE
ALTER TABLE bonus DROP COLUMN ename CASCADE CONSTRAINTS;
ALTER TABLE bonus ADD empno NUMBER (4) CONSTRAINT bonus_n01 NOT NULL DISABLE;
WHENEVER SQLERROR CONTINUE
ALTER TABLE bonus MODIFY CONSTRAINT bonus_n01 ENABLE;
WHENEVER SQLERROR EXIT FAILURE
ALTER TABLE dept MODIFY deptno CONSTRAINT dept_n01 NOT NULL DISABLE;
WHENEVER SQLERROR CONTINUE
ALTER TABLE dept MODIFY CONSTRAINT dept_n01 ENABLE;
GRANT REFERENCES ON dept TO hr;
GRANT SELECT ON dept TO hr;
REMARK -- Creating foreign-key constraints --
ALTER TABLE bonus
ADD CONSTRAINT bonus_f01
FOREIGN KEY
( empno )
REFERENCES emp
DISABLE;
ALTER TABLE emp
ADD CONSTRAINT emp_f01
FOREIGN KEY
( deptno )
REFERENCES dept
DISABLE;
REMARK -- Enabling new foreign-key constraints --
ALTER TABLE bonus MODIFY CONSTRAINT bonus_f01 ENABLE;
ALTER TABLE emp MODIFY CONSTRAINT emp_f01 ENABLE;
REMARK -- Creating views --
CREATE VIEW emp_v
( empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno,
dname,
loc ) AS
SELECT empno
, ename
, job
, mgr
, hiredate
, sal
, comm
, deptno
, dname
, loc
FROM emp e, dept d
WHERE d.deptno = e.deptno
/
/*----------------------------------------------------------------------------*\
End of database synchronization script
\*----------------------------------------------------------------------------*/
Copyright © Impact Systems Pty. Ltd. 2008
|