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

Tony

Not sure if this works without replication being used, though I can't see why not. Have a look at the DIFFERENCES and RECTIFY procedures in the DBMS_RECTIFIER_DIFF package. It may not be created unless you run catrep.sql though.

Pete

tony wrote:

> 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 Thu Sep 16 1999 - 10:44:08 CDT