Re: script or utility to generate dml comparing two schemas
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 28 Jun 2004 22:46:11 +0200
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@xxxxxxxxxxxxx
>put 'unsubscribe' in the subject line.
>--
>Archives are at //www.freelists.org/archives/oracle-l/
>FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
