This thing just popped up yesterday. I'm using Oracle 9i Rel 2 Ver. 184.108.40.206 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.