5

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

upog
  • 4,965
  • 8
  • 42
  • 81

3 Answers3

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.

OldProgrammer
  • 12,050
  • 4
  • 24
  • 45
  • My Issue was in 1 st sql statement Getting explain plan for the view, I have updated the question, will check the docs – upog Feb 19 '14 at 15:05
  • 1
    Please note that V$SQL_PLAN, V$SESSION and V$SQL_PLAN_STATISTICS_ALL are synonyms. The underlying objects (if you do not want to grant SELECT_CATALOG) are V_$SQL_PLAN, V_$SESSION and V_$SQL_PLAN_STATISTICS_ALL (with an underscore). This is important as you can't GRANT SELECT on a synonym, but only the underlying object. – Blama Apr 10 '15 at 09:13
  • Ready to execute grant statements can be found in [Getting Actual Execution Plan in Oracle 11g](https://stackoverflow.com/questions/14596088/getting-actual-execution-plan-in-oracle-11g) – Vadzim Dec 11 '18 at 19:47
1

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

Bren
  • 2,148
  • 1
  • 27
  • 45
0

Make sure you have the UPDATE privilege on the underlying tables. As I pointed out on stackoverflow, the SELECT privilege may not be enough

https://stackoverflow.com/a/74706625/12884507

Christian
  • 31
  • 3