Home » RDBMS Server » Security » Execution plan privileges (Oracle 10g (10.0.2.0))
Execution plan privileges [message #342620] Sat, 23 August 2008 04:50 Go to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

can anybody tell me what are the privileges required to create the execution plan?

Regards,
Balaji.C

Re: Execution plan privileges [message #342640 is a reply to message #342620] Sat, 23 August 2008 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Re: Execution plan privileges [message #342650 is a reply to message #342620] Sat, 23 August 2008 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The same privilege than to execute the statement plus the direct read privilege on underlying objects.

Regards
Michel
Re: Execution plan privileges [message #342654 is a reply to message #342650] Sat, 23 August 2008 11:20 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

i got the solution for my issue.

The reason y the user (SCOTT) was not able to create a execution plan for the query is that the user had only 'select' privilege on PLAN TABLE.

use DBA_TAB_PRIVS view to find out users having privileges on tables.


it seems the user must have both the 'select' as well as 'insert' privilege on that table in order to examine execution plan for the query.

The solution for this issue is that:

I granted the 'INSERT' privilege for the same (PLAN TABLE') and thus it worked.

'grant insert on plan_table to user(SCOTT)'

Regards,
Balaji

[Updated on: Sat, 23 August 2008 11:22]

Report message to a moderator

Re: Execution plan privileges [message #342655 is a reply to message #342654] Sat, 23 August 2008 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The best way is to create a private plan_table (?/rdbms/admin/utlxplan.sql).

Regards
Michel
Re: Execution plan privileges [message #342685 is a reply to message #342655] Sun, 24 August 2008 01:52 Go to previous messageGo to next message
balaji1482
Messages: 312
Registered: July 2007
Location: INDIA
Senior Member

i know how to create a plan table.anyway thanks for the info.
Re: Execution plan privileges [message #342688 is a reply to message #342685] Sun, 24 August 2008 02:34 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you create a private plan_table you can't have a privilege problem this is what I said and I precise the script, even if you know, because this may be useful for readers (and you are not the lone one, anyway).

Regards
Michel
Previous Topic: license information
Next Topic: Ora-01031: insufficient privileges on 10.2.0.4 version for single Instance DB
Goto Forum:
  


Current Time: Fri Dec 02 23:17:17 CST 2016

Total time taken to generate the page: 0.07025 seconds