OraFAQ Forum: Server Administration » Privilege require to gather stats

Home » RDBMS Server » Server Administration » Privilege require to gather stats (Oracle 10.2.0.1.0, Windows)

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

Privilege require to gather stats [message #555681]

Sun, 27 May 2012 04:22

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Hi,
I am confuse as what privilege is required to run dbms_stats package for gathering table and schema statistics.
I tested in my local database but could not find the solution.

Code: [Select all] [Show/ hide]

SQL> show user
USER is "JACK"
SQL>
SQL> select * from user_sys_privs;

no rows selected

SQL> select * from user_role_privs;

no rows selected

SQL> select * from role_sys_privs;

no rows selected

SQL> exec dbms_stats.gather_table_stats('JACK','EN1')

PL/SQL procedure successfully completed.

I revoke all the privileges from JACK user but still i am able to gather stats for a table.
Can you tell me what privilege is require to gather stats.

Regards,
Jack

Report message to a moderator

Re: Privilege require to gather stats [message #555684 is a reply to message #555681]

Sun, 27 May 2012 05:15

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

Senior Member
Account Moderator

Quote:

what privilege is required to run dbms_stats package for gathering table and schema statistics

The ones that are mentioned in the documentation.

Regards
Michel

Report message to a moderator

Re: Privilege require to gather stats [message #555726 is a reply to message #555681]

Mon, 28 May 2012 00:24

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

If you have sufficient privileges to create a table in your own schema (create session, create table, quota on tablespace), then you can gather statistics on it. You do not need any additional privileges to gather statistics on a table in your own schema. There is a PUBLIC role that is automatically granted to all users and not listed in dba_roles or session_roles and execute on dbms_stats is granted to PUBLIC. However, you need additional privileges (analyze any) to gather statistics for tables in other schemas.

[Updated on: Mon, 28 May 2012 00:43]

Report message to a moderator

Re: Privilege require to gather stats [message #555761 is a reply to message #555726]

Mon, 28 May 2012 06:41

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Thank you Barbara for your detailed answer.This is what i was looking for.

[Updated on: Mon, 28 May 2012 06:41]

Report message to a moderator

Re: Privilege require to gather stats [message #555764 is a reply to message #555761]

Mon, 28 May 2012 07:38

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

Senior Member
Account Moderator

To prevent from another such question: you have ALL privileges on your object.
And in addition, this is described in the documentation of the package, so as a general duty for you: BEFORE using any package read the documentation, the WHOLE page of it.

Regards
Michel

Report message to a moderator

Previous Topic:	Oracle database migration from oracle 11gR2 32 bit to oracle oracle 11gR2
Next Topic:	MMNL trace genration

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 10:32:24 CDT 2025