ORA-20000 Insufficient Privileges Error From DBMS_STATS.GATHER_SCHEMA_STATS
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_S
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.
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_S
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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_s
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