Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!news4.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newsfeed.iinet.net.au!newsfeed.iinet.net.au!per-qv1-newsstorage1.iinet.net.au!per-qv1-newsstorage1.iinet.net.au!per-qv1-newsreader-01.iinet.net.au!not-for-mail
Message-ID: <452B9923.E7B@yahoo.com>
Date: Tue, 10 Oct 2006 20:59:16 +0800
From: Connor McDonald <connor_mcdonald@yahoo.com>
Reply-To: connor_mcdonald@yahoo.com
X-Mailer: Mozilla 3.01 (WinNT; I)
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Re: gather_system_stats problem on 10.2
References: <ChyWg.17555$wE5.4319@trnddc02> <452ab53a$0$24412$ba620e4c@news.skynet.be> <GIyWg.17643$wE5.11425@trnddc02>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 87
NNTP-Posting-Host: 124.168.94.122
X-Trace: 1160485154 per-qv1-newsreader-01.iinet.net.au 1434 124.168.94.122
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:276912

Chuck wrote:
> 
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> Matthias Hoys wrote:
> > "Chuck" <skilover_nospam@bluebottle.com> wrote in message
> > news:ChyWg.17555$wE5.4319@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@yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------