We help IT Professionals succeed at work.

ORA-20000 Insufficient Privileges Error From DBMS_STATS.GATHER_SCHEMA_STATS

DonFreeman
DonFreeman asked
on
5,868 Views
Last Modified: 2010-08-05
This thing just popped up yesterday.  I'm using Oracle 9i Rel 2  Ver. 9.2.0.4 on Windows 2000 Advanced  Server

This code has been running inside a package for some time and I've never seen a failure. The following is failing.

DBMS_STATS.GATHER_SCHEMA_STATS ( OWNNAME=>'SCHEMA_NAME', CASCADE=>TRUE );

It fails the same place each time on the same table:  VACCINE_TYPE_CODE which is one of 300 materialized views.  The view is valid.

The job is running under sys user from OEM.  The above code cannot be run by sys or the owner from sqlplus either.  The table, individually, can be analyzed by either the owner or the sys user.  I did some research and found a reference to a similiar problem, the solution being to grant 'analyze any' directly to sys user.  We manually ran the above statement from sqlplus as sys user and it worked.

We ran the job on schedule last night from oem and it failed again on a different table!

The synonym on the view is valid.  What am I missing?  As far as I can tell no one changed anything yesterday but obviously something changed.


Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I found these remarks on metalink:

>>If you already have the ANALYZE ANY privilege and it the procedure is still failing, check for any objects created with mixed case names.

>>I have the answer (found in "Ask Tom"): SYS has indeed not enough privilege to analyze tables of any user; this privilege is part of a ROLE granted to SYS. In SQL*Plus, the roles are active -> OK for "exec dbms_stats.gather_schema_stats( 'ONEUSER'..." but in the procedure "analyze_all", roles are inactive -> error.

Solution: grant explicitely the privilege; I tried with another user (I don't like to play with SYS):
grant analyze any to XXX;
then I have created the procedure in XXX schema and I can run it, without error!

Hope one of the above helps

CHeers
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.