RE: Comparing data between two tables in two schema

Rao,

I have a simple WinNT command line tool that I wrote and I have the source code for it.

It generates a txt based file of one schema or of a subset of it. Then you can let it compare to an other schema or part of it. The structure of data must be the same, though the tables and columns in on schema can have different names than in the others. The result of the comparison is a SQL file that contains insert, update and delete commands to bring the second schema in sync with the data in the 'txt based export' file. It works for Oracle 8 and Oracle 8i, support data, number, char, varchar type date but no objects. You can also generate SQL*Loader files with for the schema.

I use it for keeping offline user sites schemas in sync with the master database, where I can not offerd to drop the schema and recreate it by importing it, since the schema is in use 24x7 the time.

Tamas Szecsy

-----Original Message-----
Sent: Tuesday, October 02, 2001 6:21 PM
To: Multiple recipients of list ORACLE-L

Why not try the time tested brute force method?

Select * from <owner1>.<table1>
intersect select * from <owner2>.<table2>;

If you get the same number of rows as there are in the tables individually then
they are equal. BTW: it's cheap too.

Dick Goulet

____________________Reply Separator____________________
Author: "Gogala; Mladen" <MGogala_at_oxhp.com>
Date:       10/2/2001 7:35 AM

The freware "tora" tool also has some "compare" capabilities. I've tested if with whe users "scott" and "scott1" (both populated throuh the "demobld" script and, therefore containing vast masses of data) and it worked well. I haven't tested it on a real life database, mostly because the word "freeware"
is considered rude in production environment.

> -----Original Message-----
> From: Alejandra Pazos Freire [mailto:alepazosfreire2_at_hotmail.com]
> Sent: Monday, October 01, 2001 11:15 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Comparing data between two tables in two schema
>
>
> I think that the Change Management Pack of the Oracle
> Enterprise Manager may
> be useful to you.
>
>
>
> >From: "Rao, Maheswara" <Maheswara.Rao_at_Sungardp3.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: Comparing data between two tables in two schema
> >Date: Mon, 01 Oct 2001 12:29:41 -0800
> >
> >List,
> >
> >I have two schema. The tables in both schema are having
> same name and
> >structures.
> >
> >Is there any tool to compare the data between two schema tabels?
> >
> >Thanks,
> >
> >Rao
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Rao, Maheswara
> > INET: Maheswara.Rao_at_Sungardp3.com
> >
> >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> >San Diego, California -- Public Internet access /
> Mailing Lists
> >--------------------------------------------------------------------
> >To REMOVE yourself from this mailing list, send an E-Mail message
> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
> >(or the name of mailing list you want to be removed from). You may
> >also send the HELP command for other information (like subscribing).
>
>
> _________________________________________________________________
> Descargue GRATUITAMENTE MSN Explorer en
> http://explorer.msn.es/intl.asp
>
> --
> Please see the official
> ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Alejandra Pazos Freire
> INET: alepazosfreire2_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gogala, Mladen
  INET: MGogala_at_oxhp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Szecsy Tamas
  INET: tszecsy_at_GEOMETRIA.hu

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).