Re: gather_system_stats problem on 10.2

"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 Received on Mon Oct 09 2006 - 15:46:49 CDT