Re: script or utility to generate dml comparing two schemas

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Mon, 28 Jun 2004 22:46:11 +0200
Message-ID: <40E08393.9040001@roughsea.com>

Juan,

   Hmm, currently working on something of that ilk. Let's say there are two approaches, the basic one and the not-so-basic one. First of all I forget about the generation of DML for the time being. The basic approach is to have a database link (let's say REMOTE) to the other schema.
Run

      select '(select * from ' || table_name || chr(10) ||
                'minus' || chr(10) ||
                'select * from ' || table_name || '@remote)' || chr(10) ||
                'union'
             (blah blah - same thing in reverse order)
     from user_tables;

  and this will generate some mega-script of death which will find the differences.

Now, in the case I am interested in, one database is in NJ, the other one in France, without being enormous some tables are in the million-row range and I can't decently make them cross the Atlantic twice ... So? The idea is to create stored procedures running on each side, make them compute MD5 checksums (routine for that in dbms_obfuscation_toolkit, if undocumented) on packets of 32K (the biggest I can have in PL/SQL), and just have the primary key of the fisrt row in the packet and checksums cross the Atlantic till I get a difference ... Multithreading required, of course, if you want computations to run in parallel. This is the not-so-basic approach.

HTH S Faroult

Juan Carlos Reyes Pacheco wrote:

>Hi list
>does any one has an script or know of a utility comparing rows between two
>databaes or schemas and generating DML.
>NOT STRUCTURE,( DDL),
>please.
>
>
>
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
>
>

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jun 28 2004 - 15:44:00 CDT