| 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.
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 #555726 is a reply to message #555681] |
Mon, 28 May 2012 00:24  |
 |
Barbara Boehmer
Messages: 9100
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 #555764 is a reply to message #555761] |
Mon, 28 May 2012 07:38 |
 |
Michel Cadot
Messages: 68716
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
