Re: Merging 2 "almost" identical databases.

From: sybrandb <>
Date: Mon, 29 Sep 2008 03:41:07 -0700 (PDT)
Message-ID: <>

On 29 sep, 11:55, Neomusashi <> wrote:
> Hi,
> So my work for the next few weeks is sorting out how to merge 2 almost
> identical databases used by 2 almost identical applications into one
> application with one database. These applications were developed for a
> client.
> Why 2 DB' s and 2 Applications? Well it’s a legacy decision. My
> predecessors decided it should be that way and now it ' s up to me to
> fix this. They needed small application/functional differences for
> other distribution channels and made a clone of the original app. .
> Now several years later they would like to merge the two apps into
> one, but of course they grew apart in functionality and structure. No
> big differences, but enough to make this a nice project ... .
> Short description:
> Each application runs on a sun solaris 10 server in a websphere 6.0
> application server (J2EE apps of course) and an orcale 10 database is
> used. Database queries are done using Stored Procedures. Both have 2
> schemas. One schema has the core data in about 20 tables and the other
> contains about 9 generic servers and about tables. The tables purposes
> don’ t really differ from each other, but columns,  DDL and data load
> do.
> What I need to come up with is a merging process we can deliver to our
> client so they can easily merge the two databases.  The process has
> two deliver a clear report.
> I was thinking of three possible ways to go:
> 1.      Shell scripts with logging:
> pros: basic, quickest?
> Cons: Debugging, testing, complex, report/logging not so fancy/clear,
> inhouse expertise
> 2.      Write a mini J2EE app:
> pros: easy to debug, easier to make?, better report/logging
> Cons: very slow to develop, work to throw away.
> 3.      Write a java app:
> pros: easy to debug, easy to make, better report/logging
> cons: work to throw away, slow to develop
> I’ m feeling most for the 3th solution. How would you approach this?

I would make sure I do everything *in* the database, and do NOTHING at the client side.
So I would use database links and SQL and PL/SQL, using the MERGE statement available since 9i.
I would also try to find out whether simply using export/import, or expdp/impdp is not an option.
This will be definitely the fastest solution. I would also try to find out what can be done using dbms_rectifier_diff.

In short, I would use Oracle as much as possible, instead of various Java crap.

Sybrand Bakker
Senior Oracle DBA
Received on Mon Sep 29 2008 - 05:41:07 CDT

Original text of this message