Compare objects with only select_catalog_role on target user

Hi,

This just maybe to be a limitiation of V8. Here is my problem. I want to compare the objects in a schema in dev with prod. The prod target user, not a schema, has select_catalog_role so I can see all DDL for any user on the prod instance when logged in as target user through the data dictionary dba views. I can not select on any views or tables however. This is to be expected as target user does not have select permission on any schema objects. However, compare from dev does not see the DDL on prod. A blank file is returned when I bring up ExamDiff. Any ideas why compare does not work under the above situtation.

Thanks

I'm not sure I understand. Can you provide an example?

Here goes.

A user , let's say DDLUSER, on our production server is created with role SELECT_CATALOG_ROLE so this user can select all data dictionary view. Log into a schema, let's say APP1, on development server. Run compare tool which brings up a list of all objects owned by APP1. Then login into target session DDLUSER on production server and set Target Schema to APP1 on Options tab. Select an object on the list and compare. The compare results in differences whether differences exist or not since the object is not found in the target session DDLUSER on the production server. However, when logged into DDLUSER on production the PLSQL dev browser shows all objects on the instance and can view all DDL for these objects. This is Oracle 10.2.0.4.

Hope this helps.

Okay, I understand. Have you enabled the "Use DBA views if available" option (Tools > Preferences > Oracle / Options)?

Yes it is set to Use DBA Views. Remember the user APP1 which is running the compare does not have access to the DBA views but DDLUSER on the production schema does.

Okay. We will check it out.

Any update on this

Not sure if this is related, but it looks to me like it can be.
When I go to "Export User Objects" and select some schema - I can see all the objects and successfully export them.
When I go to "Compare User Objects" and select the same schema - I see only a limited subset of objects. To me these sets should be identical. Am I missing something?

I think the problem is that compare objects is using the SYS.ALL_... views and not the SYS.DBA_.... views even if "Ues DBA views if available" option is checked. If you have permission to an object in another schema it will appear in the compare list since it is in the SYS.ALL_... views.

--jeff

I think this is exactly right.
Marco, PLEASE, PLEASE, PLEASE fix it :-) It can simplify greatly our life!

Sorry to ask again - but I just checked and figured out that I requested this feature (compare objects in different schema in production environment with what we have in dev/QC) for the first time back in December, 2004. And I believe I was not alone with this request.
Though there was some improvement in this area with latest releases, we can still not do what we need. Is there any REAL problem in fixing it so it takes more than 5 years? Sorry again - but it doesn't look like our requests are counted...

Any chance to get any response?

We're looking into a solution.

Any chance to have an estimate? Wait 5 more years?

Can we expect this issue to be fixed soon?

Our organization would also greatly benefit from a solution to this compare object limitation. If it helps at all to raise the urgency of this case - we have an active 100 user support contract.

Would it be possible to get an update with respect to when we might be able to see a fix? ie. Will it make it into an 8.0 patch level?

We are working on a fix. At the moment you need to have access to the dba views for both the source and target connection.

Hi Marco,

Thanks for your help on this.

I tried compare from a source account with select_catalog_role to a target account with select_catalog_role so both accounts had access to the dba_views and they owned no objects. The compare window did not display any objects when I selected an object owning schema. The objects for the schema are, however, displayed in the object browser when "all users" or the schema is specified. It looks like the compare is not using the dba views even when "Use DBA view if available" option is checked.

[quote=djkucera]Our organization would also greatly benefit from a solution to this compare object limitation. If it helps at all to raise the urgency of this case - we have an active 100 user support contract.

Would it be possible to get an update with respect to when we might be able to see a fix? ie. Will it make it into an 8.0 patch level?
[/quote]
We are even bigger - unlimited user licence. And EVERY user in our organization is dreaming about this option...

I just wanted to check on this, please update.
Thanks

Any update?

While we're at it, I notice that the 'Use DBA views if available' option doesn't work well with Compare User Objects for production vs dev/test environments at my current site, because even though my local account can access the DBA views, my read-only production account can't. I normally have the option checked, but when I want to compare something with production I have to go to preferences and uncheck it first to avoid a 'ORA-00942: table or view does not exist' error from the Target Schema dropdown when it tries to query dba_users. (Or, I've just discovered, select the target user from the list locally before connecting to the target session.) Maybe there could be a preference panel for Compare User Objects with this sort of thing on it.

Any update?

I will post here when there is an update.

I have been asking for this for the last 5 years.
Guys who started this thread and were asking about this fix in the others - may be if you add your opinion here it will reduce waitng time from the next 5 years to something more reasonable.

I was asked today by our IT Asset Management team if we are planning to prolong our existing service contract. Based on your responses (actually - lack of responses to pretty much all of the requests that came from our company. 5 years to implement "compare user objects" in production schema looks too long, sorry) I'm not sure we need to do this. Pretty much all new bells and wistles that were implemented in 8.0 version are either useless (at least for our company, and we are very heavy on daily Oracle development) or are not working as expected.
Version 8.0.2.1505
01.60233 - Unlimited user license
Service contract 11/1/2010

Hi Marco,

Do you think that this issue will be resolved a near future release. The ability to compare objects from a user that owns no objects and only has access to dba view to a target user that also owns no objects and only has access to dba view would be a great help to any developer that works in a "best practice" environment.

Other then this enhancement, we have been very happy with with 8.0.2

Thanks,

--jeff

Hi Marco,

I see the above was not fixed in 8.0.3. Any chance in next update.

Regards,

--jeff

What I really don't understand - why is it so hard? It really looks like the problem is only in not using dba views while performing this operation. Are we missing something really complex? There are dozens of people in our company who are crying for this feature (and it looks like not in our company only). What is the real problem behind this request???

Hi Marco,

Have have looked at debug.txt file for compare user objects and the select that generates the compare list uses sys.all_objects and not a dba view. I hve emailed you the debug.txt file. I can not say what is being uses on the target since I need a list of objects first to run the compare. I do know that if I log into an object owning schema and the target user only has select_catalog_role the target object is not found.

Is there any chance at some point this problem is resolved?

any update on this issue.
I'm also waiting for a solution

Would be relay helpfull!!

Just curious - hom many posts from different users can move this problem from freezing point?

So far the answers have been "We're looking into a solution.", "We are working on a fix." and "I will post here when there is an update." What more do you want?

I want to see some progress. Do you really think it's OK to have such answers for the last 6 (!!!) years? This feature (compare objects in prod environment under different schema) was originally requested in 2004. Now - in 2010 - we still cannot do it.

Any chance to hear anything new on this problem?

Hi!

So far, in the rare situations I needed to perform this task, I would generate source files and "Compare Source" instead of "Compare Objects". But I've been assigned to a new customer and this activity has become more and more frequent. So I'd like to see this in 9, or in a patch after the major release.

Regards,
Gustavo

Version 9 should fix this.

Great! Thx!