Thanks. Any suggestion on whether having schema owner gather its own stats or having a central user
in charge of gathering all other schemas' stats.
"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1072795557.900646_at_yasure...
> Jack Wang wrote:
>
> > 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
>
> Granting EXECUTE ANY PROCEDURE is not a solution ... it is a nightmare.
> You have just gutted the system's security. Revoke that privilege and do
> the following:
>
> GRANT execute ON dbms_stats TO <schema_name>;
>
> While effective: One should not use a 2000 pound bomb as a way to kill
> mosquitos.
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Tue Dec 30 2003 - 13:54:21 CST
