How do you compare the data in different schemas not just table diffs 
Author Message
 How do you compare the data in different schemas not just table diffs
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?



Wed, 18 Jun 1902 08:00:00 GMT
 How do you compare the data in different schemas not just table diffs

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

Quote:

>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?



Wed, 18 Jun 1902 08:00:00 GMT
 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

Quote:

> 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


> >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?



Wed, 18 Jun 1902 08:00:00 GMT
 How do you compare the data in different schemas not just table diffs

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?!


Quote:

>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


>> 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


>> >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?



Wed, 18 Jun 1902 08:00:00 GMT
 How do you compare the data in different schemas not just table diffs
you can do a lot with the minus operator and it is suprisingly
efficient.  if you're talking massive amounts of data, there is no
quick way to do what you want.  here is an example with minus.

select * from user1.table1
MINUS  
select * from user2.table1;

i don't believe minus will support lobs or longs but you can run it on
all the other columns.  you will probably want to run the comparison
in both directions.

gene hubert
skillspoint.com

--------------------


Quote:
>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?



Wed, 18 Jun 1902 08:00:00 GMT
 How do you compare the data in different schemas not just table diffs

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

Quote:

> 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?!



> >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


> >> 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


> >> >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?

  psharman.vcf
< 1K Download


Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Compare the Fields in the 2 different TABLES in the 2 different DATABASE

2. comparing data in two different tables

3. Tools for comparing DB schemas and data

4. compare 2 table schemas

5. compare schemas between two tables

6. Insert into table from different schemas

7. Foreign key references to tables in different schemas ??

8. Same table name, different schemas

9. Creating Views joining tables in two different schemas

10. Designer 2.1 (Tables in different schemas)

11. Stored Procs not doing the same thing on different servers


 
Powered by phpBB® Forum Software