Chuck wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Matthias Hoys wrote:
> > "Chuck" <skilover_nospam_at_bluebottle.com> wrote in message
> > news:ChyWg.17555$wE5.4319_at_trnddc02...
> >> -----BEGIN PGP SIGNED MESSAGE-----
> >> Hash: SHA1
> >>
> >> Solaris 10, Oracle 10.2.0.2.0
> >>
> >> I'm trying to run dbms_stats.gather_system_stats through the dbms_job
> >> package. The job simply runs this once a week...
> >>
> >> dbms_stats.gather_system_stats( gathering_mode => 'INTERVAL',
> >> interval=> 60);
> >>
> >> It's failing with the following error.
> >>
> >> Mon Oct 9 16:11:43 2006
> >> Errors in file /oracle/app/oracle/admin/ADMIN00/udump/admin00_ora_918.trc:
> >> ORA-12012: error on auto execute of job 62
> >> ORA-27486: insufficient privileges
> >> ORA-06512: at "SYS.DBMS_ISCHED", line 99
> >> ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
> >> ORA-06512: at "SYS.DBMS_STATS", line 15456
> >> ORA-06512: at line 1
> >>
> >> The trace file just contains the same errors as above (copied from the
> >> alert log). If I run it through sqlplus it works.
> >>
> >> I have already granted insert,update,delete,select on sys.aux_stats$ to
> >> the owner of the job as well as "select any table" and "execute any
> >> procedure". On 9i these were the only undocumented grants needed to run
> >> gather_system_stats through the oracle job queue. Is there something
> >> else needed to do this on 10g? If not why is it failing? The owner of
> >> the job also has the DBA role in addition to the privileges already
> >> mentioned. There is nothing on metalink about this.
> >>
> >
> > Try granting ANALYZE ANY to the owner of the job ...
> >
> > Matthias
> >
> >
>
> It already has ANALYZE ANY. Here's all the sys privs the user already has.
>
> SQL> SELECT PRIVILEGE FROM user_sys_privs
>
> PRIVILEGE
> - ----------------------------------------
> CREATE SESSION
> SELECT ANY TABLE
> EXECUTE ANY PROCEDURE
> ANALYZE ANY
> SELECT ANY DICTIONARY
> UNLIMITED TABLESPACE
> EXEMPT ACCESS POLICY
>
> 7 rows selected.
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.5 (MingW32)
>
> iEYEARECAAYFAkUqt6UACgkQzIf+rZpn0oQ4WACghqv1OZp4wGu1JZXpvhf15Pr9
> +5wAnjxLarZvjadVahPhwIV1aGiVAIWf
> =jTGA
> -----END PGP SIGNATURE-----
There is a role/priv specifically for system stats - can't remember its
name off hand, but you'll need it, or you'll need to run as sysdba
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"Semper in excremento, sole profundum qui variat."
------------------------------------------------------------
Received on Tue Oct 10 2006 - 07:59:16 CDT
