Re: Privileges required for dbms_stats

I have isolated the issue. I had to issue 'execute any procedure' to the user who runs dbms_stats, because the schema to be analyzed involves a FBI which is associated with a procedure.

It was said that, 'select any table' is no longer needed for 9202 above to gather index stats. It is funny that I still needed with 9203.

Another question arises though, which is the practical approach?

1. Let one user gather all other schema stats periodically. I could wrap it in a procedure and schdule it thru dbms_job.
2. Let each schema gather its own stats, in which case I don't have to bother with 'select any table' and 'execute any procedure' etc.

Thanks.

• Jack

"Jack Wang" <nospam_at_nospam.com> wrote in message news:Nr8Ib.96007$ss5.72702_at_clgrps13...
> Oracle 9203 on W2K with SP4
>
> I wonder what are privileges required for
> dbms_stats.gather_schema_stats(ownname=>'someone else', cascade=>true)?
>
> A user with the privileges of 'create session' and 'analyze any' was able
to
> dbms_stats.gather_schema_stats(ownname=>'someone else'). What is required
> for the cascade=>true bit?
>
> Given 'select any table' still produces
>
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_STATS", line 9357
> ORA-06512: at "SYS.DBMS_STATS", line 9839
> ORA-06512: at "SYS.DBMS_STATS", line 10023
> ORA-06512: at "SYS.DBMS_STATS", line 10077
> ORA-06512: at "SYS.DBMS_STATS", line 10054
> ORA-06512: at line 1
>
> Any help is appreciated.
>
> - Jack
>
>
>
>
Received on Tue Dec 30 2003 - 00:27:23 CST