SQL Error: ORA-01039: insufficient privileges on underlying objects of the view

Question

I am trying to get explain plan for a view using below query

explain plan for select * from SCHEMA1.VIEW1;

But i'm getting

Error report -

SQL Error: ORA-01039: insufficient privileges on underlying objects of the view

01039. 00000 -  "insufficient privileges on underlying objects of the view"


*Cause:    Attempting to explain plan on other people's view without
           the necessary privileges on the underlying objects of the view.


*Action:   Get necessary privileges or do not perform the offending operation.

Need help in getting SQL grant statement

Answer 1

Clearly stated in the Oracle Docs :

Security Model

This package runs with the privileges of the calling user, not the package owner (SYS). The table function DISPLAY_CURSOR requires to have select privileges on the following fixed views: V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL.

Using the DISPLAY_AWR Function requires the user to have SELECT privileges on DBA_HIST_SQL_PLAN, DBA_HIST_SQLTEXT, and V$DATABASE.

Using the DISPLAY_SQLSET Functionrequires the user to have the SELECT privilege on ALL_SQLSET_STATEMENTS and ALL_SQLSET_PLANS.

Using DISPLAY_SQL_PLAN_BASELINE Function the user requires the user to have the SELECT privilege on DBA_SQL_PLAN_BASELINES.

All these privileges are automatically granted as part of the SELECT_CATALOG role.

---

Answer 2

I think you do not have select right granted on some tables or execute right on some functions that may be used in view's query. Check the query of the view "SCHEMA1"."VIEW1"

My guess is there are some functions that you need to be granted execute right on them.

Another guess ( which is more probable ) is that, some tables used in the view are granted to some role and you have that role. As far as i know, in packages, procedures etc. Oracle needs your user explicitly granted to select on those objects, not through a role. Otherwise you won't be able to execute those packages