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

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 - 09:32:16 CDT