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

upog picture upog · Feb 19, 2014 · Viewed 10.8k times · Source

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

OldProgrammer picture OldProgrammer · Feb 19, 2014

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.