Help with ORA-01039
- From: Debaditya Chatterjee <debaditya.chatterjee@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 29 Oct 2009 15:14:01 -0500
All,
I have a setup where some users query a set of tables owned by another user
using views. As they do not have access to the base tables they cannot do an
explain of their queries. I was hoping to create a procedure that does the
explain plan through another user (IT_PERF which has select any table
privilege) and grant execute on that procedure to these users. However I am
running into a problem as described below
As you can see below the anonymous block executes the explain plan without
any issues but when I put the code in a procedure it fails with ORA-01039.
What is so special about the procedure that it runs into the error ? Any
known tricks to workaround this issue ?
Appreciate your response.
Thanks
Deba
SQL> begin
2 execute immediate 'explain plan set statement_id =
'||''''||'test1'||''''||' for select * from USER1.SOME_VIEW';
3 end;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.65
SQL> create procedure test
2 as
3 begin
4 execute immediate 'explain plan set statement_id =
'||''''||'test1'||''''||' for select * from USER1.SOME_VIEW';
5 end;
6 /
Procedure created.
Elapsed: 00:00:00.55
SQL> exec test;
BEGIN test; END;
*
ERROR at line 1:
ORA-01039: insufficient privileges on underlying objects of the view
ORA-06512: at "IT_PERF.TEST", line 4
ORA-06512: at line 1
Other related posts:
