0

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.

Improve this question
3
  • Which Oracle version? Could you post your query against ALL_OBJECTS?
    – Emyl
    Commented Oct 1, 2013 at 15:26
  • @Emyl - the query was a simple SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','VIEW'); I did find the issue, though. Thanks.
    – MacAnthony
    Commented Oct 1, 2013 at 15:30
  • Try this: Use User_objects, give the objects for the current logged-in user.
    – narendra solanki
    Commented Oct 1, 2013 at 20:17

2 Answers 2

Reset to default
0

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.

Improve this answer
1
  • 1
    Oops! Almost as nasty as creating a private dual with 2 rows :-)
    – Colin 't Hart
    Commented Oct 1, 2013 at 15:44
0

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).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.