Home » RDBMS Server » Performance Tuning » Privilege to run explain plan (Oracle 11.2.0.1.0, Windows)
Privilege to run explain plan [message #555494] Thu, 24 May 2012 11:30 Go to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi,

Can you tell me what privilege is require for a user to execute explain plan?

I get below error while try to execute explain plan.

SQL> explain plan for SELECT /*+ FULL(t) */ COUNT(*) FROM "DREAM"."CONSUMER.TAB" t WHERE ROWNUM <= 1000000;
explain plan for SELECT /*+ FULL(t) */ COUNT(*) FROM "DREAM"."CONSUMER.TAB" t WHERE ROWNUM <= 1000000
                                                             *
ERROR at line 1:
ORA-01031: insufficient privileges

Regards,
Jack
Re: Privilege to run explain plan [message #555495 is a reply to message #555494] Thu, 24 May 2012 11:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
unwilling or incapable to simply Read The Fine Manual yourself?

http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9010.htm#i2061796
Re: Privilege to run explain plan [message #555498 is a reply to message #555495] Thu, 24 May 2012 11:40 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Swan,

I found the steps and privileges require to run explain plan from the below link.

http://dbataj.blogspot.in/search?q=explain+plan

Regards,
Re: Privilege to run explain plan [message #555518 is a reply to message #555494] Thu, 24 May 2012 12:38 Go to previous messageGo to next message
vijenderkdba
Messages: 28
Registered: May 2012
Location: mumbai
Junior Member
hi

first you need to follow these steps

-- Creating a shared PLAN_TABLE prior to 11g
SQL> CONN sys/password AS SYSDBA
Connected
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL> GRANT ALL ON sys.plan_table TO public;
SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;

then
SQL>EXPLAIN PLAN SET STATEMENT_ID = <statement_id> FOR your-sql-statement; it will genareate explain plan along with you statement id and plan information stored in plan table(plan_table).
SQL>select * from plan_table where STATEMENT_ID = <statement_id>;

Thanks,
Vij


[Updated on: Thu, 24 May 2012 12:39]

Report message to a moderator

Re: Privilege to run explain plan [message #555521 is a reply to message #555518] Thu, 24 May 2012 12:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ This does not explain the error.
2/ Create the PLAN_TABLE in your ownr schema, do NOT use SYS one for this.
3/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

4/ Feedback to those that answer to your questions.

Regards
Michel

[Updated on: Thu, 24 May 2012 12:44]

Report message to a moderator

Re: Privilege to run explain plan [message #555549 is a reply to message #555521] Fri, 25 May 2012 01:05 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Vij,
Thank you for the steps.

Michel,
Quote:
Create the PLAN_TABLE in your ownr schema, do NOT use SYS one for this.

1.When i try to create plan_table in my own schema using below steps,i get error.Can you tell me what privilege is require to create plan_table?
SQL>
SQL> @?/rdbms/admin/utlxplan.sql
create table PLAN_TABLE (
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL>

2.How to verify PLAN_TABLE is created in database before running utlxplan.sql script ?
Database version is 11.2.0.1.0.

Regards

[Updated on: Fri, 25 May 2012 01:11]

Report message to a moderator

Re: Privilege to run explain plan [message #555557 is a reply to message #555549] Fri, 25 May 2012 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
When i try to create plan_table in my own schema using below steps,i get error.Can you tell me what privilege is require to create plan_table?


Make it create by a DBA.

2. Query DBA_TABLES. But you can always run the script it will drop the previous table (and then you are sure to have the latest one).

Regards
Michel
Re: Privilege to run explain plan [message #555568 is a reply to message #555557] Fri, 25 May 2012 02:33 Go to previous messageGo to next message
vijenderkdba
Messages: 28
Registered: May 2012
Location: mumbai
Junior Member
you need to run these steps as SYS user

-- Creating a shared PLAN_TABLE prior to 11g
SQL> CONN sys/password AS SYSDBA
Connected
SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql
SQL> GRANT ALL ON sys.plan_table TO public;
SQL> CREATE PUBLIC SYNONYM plan_table FOR sys.plan_table;


then run the explain plan statement in normal user mode.
no need to create the plan_table in normal user mode.

Thanks,
Vij
Re: Privilege to run explain plan [message #555573 is a reply to message #555568] Fri, 25 May 2012 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 24 May 2012 19:44
1/ This does not explain the error.
2/ Create the PLAN_TABLE in your ownr schema, do NOT use SYS one for this.
3/ Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

4/ Feedback to those that answer to your questions.

Regards
Michel

Re: Privilege to run explain plan [message #555582 is a reply to message #555573] Fri, 25 May 2012 04:22 Go to previous message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Michel/Vij,

Thank you for answers
Previous Topic: BULK COLLECT issue
Next Topic: Verify statspack
Goto Forum:
  


Current Time: Fri Mar 29 10:42:35 CDT 2024