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.
-
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.
-
Re-ran the comparison again (SC_A against SC_B).
It does not show any tables that reside only in SC_B.
-
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/
