Toad World® Forums

Comparing Schemas does not work properly


I use this constantly and I thought that the latest version will fix some of the issues here but that was not the case.

This is what I experience.

DB: DB01
Schemas: SC_A & SC_B (Target).

Click on Database, Compare, Schemas.
The only options that I remove are comments (on tables, column, views, etc) and Compare Storage Clauses.

Run the comparison.
It says Objects only in SC_B…and it lists a bunch of tables.
Get the script to drop the tables on on SC_B, run it, all is fine and dandy.

Run the comparison a second time.
Get no objects in SC_B.

Open another TOAD session on SC_B.
And run a compare against SC_A.
Same options.

First thing I get is: Objects only in SC_B…and it lists a bunch of tables.
How come?


Are you logged in as SC_A and SC_B or another user? Do you log in the same way
each time?

Do you have access to the DBA views and is “Options -> Startup ->
Check for access to DBA Views” checked?


On the first comparison I’m logged in as SC_A and on the second one from SC_B.

Yes the option is set.


And do you have privileges on dba_objects , dba_tables , etc, from both schemas?

You can go to Database -> Spool SQL to see the queries that Toad runs in both
cases, to see if it uses the DBA or ALL views.

If it’s using the ALL_ views, that would explain the problem.


No, I do not have privileges on dba_views.
But the spool shows it is using SYS.ALL_VIEWS in a couple of places.


[ Attachment(s) from John Dorlon included below]

Well, that would explain it. This is happening because SC_A does not have
privileges on SC_B’s objects, or vice versa.

When SC_A looks in ALL_OBJECTS, ALL_TABLES, etc, the objects that belong to SC_A
are there but the objects that belong to SC_B are not.

You can work around it like this. Use the SC_A connection to query
SC_A’s objects. Use the SC_B connection to query SC_B’s objects.
Then Toad will use USER_OBJECTS, USER_TABLES, etc, and privileges will not be
a factor


Hi John,
Thank you for your answer.
But I am afraid I do not understand.
My runs look like the image that you attached.

Let me restate the issue.

  1. I compare SC_A to SC_B and get as a result, tables that only exist in SC_B and not in SC_A.
    It automatically creates DROP scripts for these that need to be run in SC_B.
    I ran the scripts to drop the tables.

  2. Re-ran the comparison again (SC_A against SC_B).
    It does not show any tables that reside only in SC_B.

  3. I then reverse the comparison (SC_B against SC_A).
    And it then reports a list of objects that reside only in SC_B.
    The scripts that are generated are scripts to CREATE the tables in SC_A.

Why were those not reported on the second comparison (2) of SC_A against SC_B?


I got it now John.
Thanks for your help/


Glad to hear it!