Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How do you compare the data in different schemas not just table diffs

Re: How do you compare the data in different schemas not just table diffs

From: tony <ramdan_at_mailexcite.com>
Date: 15 Sep 1999 17:49:20 -0500
Message-ID: <QmVD3.19546$Dk7.461259@newscene.newscene.com>

thanks for response, but issue is that I want an automated way to check data differences. Of course i can use a not exists or a minus, but with 120 tables, I woulld need to write at least as many statements if not twice as many

the count woould usally be identical in both tables, yet the actual data might be different in a certain column

the whole idea is: is there an a way to get a scroipt that would look at all the tables and all the columns and compare it against another scema with same infor.

the issue is not esoteric, say you have two development environments, T1 - system testing, T2 Y2K testing with identical schemas. How can you be sure that the data is identical not just the structure. It is not just an issue of well just export from one to other. issue is that processes done on each one while supposly identical may have generated different data. At end of testing it is critical that we be sure that the data is identical, that the same processes on differeent schemsa genreate same results. What if a certain value in the Y2K schema is different?!

In article <37DFADF0.3E9FA251_at_edcmail.cr.usgs.gov>, Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote:
>Why don't you use the MINUS operator? For instance, if you have two
>tables, A & B and want to find what is in A, but not in B, do:
>
> SELECT * FROM A
> MINUS
> SELECT * FROM B;
>
>Then you can find out what is in B but not in A:
> SELECT * FROM B
> MINUS
> SELECT * FROM A;
>
>HTH,
>Brian
>
>Jerry Gitomer wrote:
>>
>> Hi tony,
>>
>> Based on your statement that you could use not exists I am
>> assuming (more fool I) that if the counts are different the
>> tables are different and if the counts are the same ...
>>
>> The quickest way is to write a script that generates a script
>> that prints out the name and count for each table. Run the
>> script against both schemas and then open two windows and do a
>> side by side mark one eyeball comparison. I just did this
>> yesterday and was able to resolve my differences for a 230 table
>> instance in about ten minutes.
>>
>> If it is any help the script to create the script should look
>> like:
>>
>> SELECT 'SELECT '||table_name||', count(*) FROM
>> '||table_name||';' FROM user_tables;
>>
>> (Sorry I don't have time to run it but it should be either right
>> on or close enough so you can get it working quickly.)
>>
>> Before you run it set pages 0 and spool it to a file with an
>> extension of sql. Run it and then turn spooling back on and run
>> the output file.
>>
>> regards
>> jerry gitomer
>>
>> tony wrote in message ...
>> >I have two schemas that have the same exact tables but the data
>> might be
>> >slightly different.
>> >
>> >I can compare the tables difs via TOAD, BUT is there a way to
>> compare the
>> >actual data in the tables without having to do not exists
>> statements on each
>> >table one by one.
>> >
>> >Is there some way to do this without manually doing it for every
>> tables does
>> >anybody have or know of a script?
Received on Wed Sep 15 1999 - 17:49:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US