0

I got the below error message once I want to make explain plan based on select query from view.

explain plan 
for 
select id 
from my_view 
where id is not null;

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.

I have also checked all tables based on view and all these ones were granted for my scheme (select) so it should work. Not sure where is the root problem.

Luke
  • 1,163
  • 2
  • 11
  • 19
  • 4
    Are you sure the view isn't built on top of other views, and you don't have privileged on the tables *those* are based on? – Alex Poole Jun 25 '18 at 10:38
  • You are right. The view is built on the top of not only tables but there is also one view with non-granted table. Thanks. – Luke Jun 25 '18 at 11:13

1 Answers1

0

Had the same issue with a view that I created with my own user. The view is a join view on tables a and b. Table a is in a different schema and I have been granted the SELECT privilege. Table b is in my own schema. My query does an update on this view (all columns are updatable). It copies data from some columns from table a to table b. So no update on table a. The query itself works fine because I do no update on table a. However, explain plan does not work for this query. The issue was fixed for me by also granting the UPDATE privilege on table a even though the query does not update tables on that underlying table.

Christian
  • 31
  • 3