From oracle-l-bounce@freelists.org Mon Jun 28 15:44:00 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i5SKhY825387 for ; Mon, 28 Jun 2004 15:43:44 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i5SKhO625371 for ; Mon, 28 Jun 2004 15:43:34 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 48CF172C88C; Mon, 28 Jun 2004 15:26:08 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 06274-20; Mon, 28 Jun 2004 15:26:08 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9337C72C870; Mon, 28 Jun 2004 15:26:07 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 28 Jun 2004 15:24:46 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9F08572C254 for ; Mon, 28 Jun 2004 15:24:45 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 04393-88 for ; Mon, 28 Jun 2004 15:24:45 -0500 (EST) Received: from mwinf0601.wanadoo.fr (smtp6.wanadoo.fr [193.252.22.25]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1F2FC72C0BF for ; Mon, 28 Jun 2004 15:24:45 -0500 (EST) Received: from roughsea.com (AVelizy-152-1-23-209.w82-120.abo.wanadoo.fr [82.120.13.209]) by mwinf0601.wanadoo.fr (SMTP Server) with ESMTP id 9FC5334001C4 for ; Mon, 28 Jun 2004 22:47:08 +0200 (CEST) Message-ID: <40E08393.9040001@roughsea.com> Date: Mon, 28 Jun 2004 22:46:11 +0200 From: Stephane Faroult User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.1) Gecko/20020823 Netscape/7.0 X-Accept-Language: fr-fr, en-us, en MIME-Version: 1.0 To: oracle-l@freelists.org Subject: Re: script or utility to generate dml comparing two schemas References: <40E0792C.00000B.01184@CACHITOSS> Content-type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 3882 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: sfaroult@roughsea.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@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@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 -----------------------------------------------------------------