OraFAQ Forum: Performance Tuning » Privilege to run explain plan

Home » RDBMS Server » Performance Tuning » Privilege to run explain plan (Oracle 11.2.0.1.0, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Privilege to run explain plan [message #555494]

Thu, 24 May 2012 11:30

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

Report message to a moderator

Re: Privilege to run explain plan [message #555495 is a reply to message #555494]

Thu, 24 May 2012 11:35

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

Report message to a moderator

Re: Privilege to run explain plan [message #555498 is a reply to message #555495]

Thu, 24 May 2012 11:40

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,

Report message to a moderator

Re: Privilege to run explain plan [message #555518 is a reply to message #555494]

Thu, 24 May 2012 12:38

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Privilege to run explain plan [message #555568 is a reply to message #555557]

Fri, 25 May 2012 02:33

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

Report message to a moderator

Re: Privilege to run explain plan [message #555573 is a reply to message #555568]

Fri, 25 May 2012 03:02

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Privilege to run explain plan [message #555582 is a reply to message #555573]

Fri, 25 May 2012 04:22

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Michel/Vij,

Thank you for answers

Report message to a moderator

Previous Topic:	BULK COLLECT issue
Next Topic:	Verify statspack

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 19 14:30:43 CST 2025