Oracle: Tables and Views missing from the all_objects dictionary

Question

I have a couple of database instances that are having the curious issue where tables and views do not show up in the all_objects dictionary which is causing some issue with certain schema viewing applications. Not all are missing, but there is only like a dozen of the 130 or so tables/views that are in the schema. If you query all_tables or all_views, they all show up just fine. If you select/update/delete with those tables, it works fine.

Any one know of what types of things I should be looking at that would cause this?

BTW, I am not the DB administrator to this DB, I'm just trying to figure this out for our client. I don't have full access to the DB.

• Emyl: Which Oracle version? Could you post your query against ALL_OBJECTS?
• MacAnthony: @Emyl - the query was a simple SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','VIEW'); I did find the issue, though. Thanks.
• narendra solanki: Try this: Use User_objects, give the objects for the current logged-in user.

Answer 1

I tracked it down to an SCHEMA.ALL_OBJECTS view attached to the schema that was restricting the tables rather than using the SYS.ALL_OBJECTS view.

• Colin 't Hart: Oops! Almost as nasty as creating a private dual with 2 rows :-)

Answer 2

Yes, all_objects (oracle-10g+), restricts data retrieval from views (even with grant-select-to-view privilege to user/role); Can be fixed by granting 'select any table' privilege to user/role. Also to get views, packages, functions and procedures you may need to grant 'select_catalog_role' privilege to user/role. Other privileges like 'resource', 'create session', 'create view' will not fix the problem for read/list views/package/functions etc....In short to fix select/list views/packages/functions/procedures provide user/role with 'select any table' and 'select_catalog_role' privileges (if site/dba policy/securities permit).