Home » SQL & PL/SQL » SQL & PL/SQL » insufficient privileges For EXECUTE IMMEDIATEEXPLAIN PLAN...,
insufficient privileges For EXECUTE IMMEDIATEEXPLAIN PLAN..., [message #212027] Wed, 03 January 2007 09:38 Go to next message
SnigRO
Messages: 2
Registered: January 2007
Junior Member
Hi and Happy new years to everybody

I got a problem. Anybody know how to come over this?

I can run this code in SYS user with my XE in win xp

v_sql_text := 'explain plan set statement_id ''xyz'' for select * from t1 where object_id = 100' ;
EXECUTE IMMEDIATE v_sql_text;

however when I connect to HR user.. I cannot run this.
I have insufficient privileges error..

ORA-01039: insufficient privileges on underlying objects of the view
ORA-06512: at "HR.PROCEDURE2", line xx
ORA-06512: at line 2


Pls help?
thanks
Re: insufficient privileges For EXECUTE IMMEDIATEEXPLAIN PLAN..., [message #212030 is a reply to message #212027] Wed, 03 January 2007 09:48 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
ORA-06512: at "HR.PROCEDURE2", line xx

As Error says procedure2, I had to assume that the execute immediate is in that procedure.
Quote:
ORA-01039: insufficient privileges on underlying objects of the view

Seems it is seeking permissions to t1 or plan_table.
Grant the required to HR schema.

Have you tried google?
one
two

By
Vamsi

[Updated on: Wed, 03 January 2007 09:48]

Report message to a moderator

Re: insufficient privileges For EXECUTE IMMEDIATEEXPLAIN PLAN..., [message #212032 is a reply to message #212027] Wed, 03 January 2007 09:54 Go to previous messageGo to next message
SnigRO
Messages: 2
Registered: January 2007
Junior Member
I know what the error.. But I do not know how to i fix it.. please.. can somebody show the grant process with the codes?
Re: insufficient privileges For EXECUTE IMMEDIATEEXPLAIN PLAN..., [message #212033 is a reply to message #212032] Wed, 03 January 2007 09:58 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Usually, you would give HR it's own plan_table, how to do this, is in the performance and tuning guide. You don't mention the version you're working with, but for example in the 9i guide:
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96533/ex_plan.htm#21502
Previous Topic: Date Format
Next Topic: Difference between Table and Materialized View
Goto Forum:
  


Current Time: Tue Dec 06 08:50:52 CST 2016

Total time taken to generate the page: 0.08182 seconds