Home » RDBMS Server » Server Administration » Privilege require to gather stats (Oracle 10.2.0.1.0, Windows)
Privilege require to gather stats [message #555681] Sun, 27 May 2012 04:22 Go to next message
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.
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
Re: Privilege require to gather stats [message #555684 is a reply to message #555681] Sun, 27 May 2012 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Privilege require to gather stats [message #555726 is a reply to message #555681] Mon, 28 May 2012 00:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Oracle database migration from oracle 11gR2 32 bit to oracle oracle 11gR2
Next Topic: MMNL trace genration
Goto Forum:
  


Current Time: Thu Mar 28 14:52:27 CDT 2024