Toad World® Forums

Compare table values across two different schemas on two different servers in Toad for Oracle 12.5.0.99 xpert Edition.

Have an Oracle Installation on (for example) 10.30.1.108 (Schema A)

and another on Oracle Installation on 10.30.1.110. (Schema B).

Need to compare some tables BY VALUES from Schema A and Schema B

using the Query Browser ONLY.

Can connect to Schema A and Schema B, as soon as I drag one table from

Schema A, select one table with the fields I need to compare to Schema B, when

I connect to Schema B, a new Object Pallet opens and I can’t drag and drop

tables across Query Browsers.

Using Xpert Edition 12.5.0.99.

The Compare function does not work - four hours to compare the contents of one field in a tiny 1,000 row table.

Need an SQL based solution as I am very familiar with SQL, but (clearly) not TOAD.

Our Gov. Agency just purchased two licensed Oracle Xpert TOAD versions, so we are not getting any more TOAD products any time soon.

Thank you.

Not sure what you mean by Query Browsers. Do you mean Query Builder or Schema Browser? I’m guessing Query Builder. That is not the right/best place to compare tables. It sounds to me like you got a Cartesian Join occurring - which is what one should expect when you drag two unrelated tables into the Query Builder. Suggest instead using the Schema Browser and choosing the table, do right hand mouse and select compare data, and then specify the other tables as the one to compare to. It should takes just seconds for a table that tiny …

Bert,

Thank you for your reply.

Using the compare method took 7 minutes on a 144,000 record table comparaing six fields. We kind of gave up on this method, however, if you say this is the ONLY way,

we will go with it. Some of our tables have 1M+ records and over 100 fields/columns.

On the other hand for example, using the Query Builder to compare values in the same tables on two different schemas (but on the same server) takes less than 33 mics, which is kind of the throughput we were looking for. Oh well.

Thanx again.

P.S. Bert:

What would be the best way to export the differences to Excel?

Thanx again.

P.P.S. Bert:

Back to Query Builder as most of the tables don’t have a unique PK.

Would be joining on (example) SSN+Birth+SYSID for all records that

have not matching fields.

I just created two tables with well over 700,000 rows each and did a compare data - taking note that there is an option in the wizard that says compare row by row (slow for big tables) and via minus (good for big tables). I chose minus of course since the tables are big. The compare took about 3 seconds - there were 48,000 differences. Here is the script I used to create my test data. If i used the compare row by row method it took minutes vs. seconds, so try again making sure to pay attention to all the options and picking accordingly …

connect bert/bert@orli11

drop table junk purge;

create table junk as select 1 batch_id, dba_extents.* from dba_extents where 1=0;

grant select on junk to bert1, bert2;

begin

for i in 1 … 100 loop

insert into junk select i, dba_extents.* from dba_extents;

end loop;

commit;

end;

/

connect bert1/bert1@orli11

drop table junk1 purge;

create table junk1 as select * from bert.junk;

delete from junk1 where mod(batch_id,20)=0;

commit;

update junk1 set partition_name=‘AAAAA’ where mod(batch_id,20)=0;

commit;

select count(*) from junk1;

connect bert2/bert2@orli11

drop table junk2 purge;

create table junk2 as select * from bert.junk;

delete from junk2 where mod(batch_id,30)=0;

commit;

update junk2 set partition_name=‘BBBBB’ where mod(batch_id,30)=0;

commit;

select count(*) from junk2;

Thank you for your reply.

In the compare module, the minus radio button is greyed out.

How can I enable it?

Sorry for the delay. The Minus option is enabled when the tables are in the same connection (you can use a DB Link if necessary)