Toad World® Forums

Database Compare Data

Hi

I have two instances of the same database which started off being identical. I ran a different version of the same code in each instance. I now want to compare particular database tables in each instance to check for different values. This is the first time I’ve tried this since installing Toad For Oracle Base 12.1.0.22

I’m having 2 issues:

One of the tables I want to compare has c. 3.5 million rows. When I try to compare these I’m getting an error in Toad saying it is out of memory. Any way around that?

Because of the above I just wanted to see if I could run it for a small subset. I was able to include a where clause so that only 35 records would be returned on each side and there should be differences. This ran and I can see on the Differences tab of the Compare Results it is showing a figure of 2. However when I select the tab I’m just seeing empty box. No data displayed. Have gone through setttings to see if anything missing but couldn’t find anything so any help would be greatly appreciated.

Thanks

Chris

On like 3rd page of the wizard did you choose the minux option - which says better for large tables?

When I try to compare these I’m getting an error in Toad saying it is out of memory. Any way around that?

I can say that some of previous problems in Toad (trace file generation) was having this kind of problems but in recent version, this has only with your Windows OS memory ability. In mine case when I run action on 16 GB workstation i get several gigs file trace processing without problem.

So I think this is in your case also a problem.

Advise is to enlarge OS memory.

How much, it is hard to tell exactly (tables may gave few or more columns) and few or more differences…so maybe again we need to get Toad tech team final verdict.

P.S.

But exactly when you want to compare two several mil tables, I would never do that in Toad.

Couldn’t you do something like:

select * from t1

minus

select * from t2;

or if you suspect in some columns then (much faster):

select c1, c3, c4, c5 from t1

minus

select c1, c3, c4, c5 from t2;

Brg

Damir

That’s exactly what toad does if you choose the minux option - which I think this user should choose.

Bert,

Exactly.

But when this topic took your time …

If you have time ans will to tell us some numbers/treshholds about Toad.

For instance, how much should Toad need for table with cca 1 mil records (8 colums, average row length 256 bytes).

This will be really helpful…

Brg

Damir

Thanks for that Bert.

Would like to use the minus option but for some reason it’s not available.

Row by Row selected and Minus is greyed out?

Can’t see anywhere to make this available.

Have you seen that before?

Thanks,

Chris

To use the Minus option, your table needs to have a primary key, OR, you can manually select a column (or multiple columns) that can be used as a primary key (where column/columns can define distinct rows for the table)

oops, I misspoke about the minus option enablement. It requires that the tables are in the same database, OR that you can use a DB Link from one database to the other, so that Toad can connect to one database to see both tables.

As for your 2nd question, about the empty box after seeing a (2), can you post a screen shot of that? I’m not quite sure that I’m following you.

Hi John

I was trying to use DB link from one database to the other but no joy. Think I may have to follow up on that with DBA.

I've attached screenshot of what I'm seeing. I know there are 2 differences for the records I'm looking at but they're just not appearing on screen?
Toad Diffs Issue.bmp

I wonder if that grid just got out of whack. If you rt-click on it and choose “restore columns”, does that fix it?

Sorry, I meant “Reset Columns”, not restore columns.

As for the DB Link thing, if one server doesn’t have the tnsnames info for the other DB, you won’t be able to connect just by specifying the tnsnames entry name in the DB Link.

You can get around it by specifying the full tns info within the db link like this:

create database link “link_name”

connect to user_name

identified by “password”

using ‘(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = TEST_DB)))’

On 21/02/14 17:02, John Dorlon wrote:


You can get around it by specifying the full tns info within the db link
like this:

create database link “link_name”
connect to user_name
identified by “password”
using ‘(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host_name)(PORT
= 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = TEST_DB)))’
If you don;t like typing, this might be easier and doesn’t need an entry in tnsnames.ora either:
create database link “link_name”
connect to user_name
identified by “password”
using ‘host:port/DATABASE_SID_or_SERVICE’
– Cheers,
Norm. [TeamT]