We want to move our automated statistics gathering from an external script into Oracle 9i's job scheduler. It's a very simple job, and the code basically looks like this:
DBMS_JOB.SUBMIT(
JOB => <output variable>,
WHAT => 'DBMS_STATS.GATHER_DATABASE_STATS(
cascade => TRUE, options => ''GATHER AUTO'');',
NEXT_DATE => <start date>,
INTERVAL => 'SYSDATE + 7');
The job gets created successfully and runs, but fails with the error:
ORA-12012: error on auto execute of job 25
ORA-20000: Insufficient privileges to analyze an object in Database
ORA-06512: at "SYS.DBMS_STATS", line 11015
...
The part I don't get is that the user I submitted the job under has the right permissions to gather those database statistics -- if I run the command manually it works. I was curious if Oracle was ignoring any role-based privileges the user had like it does with creating procedures so I directly granted the user ANALYZE ANY, but still no dice.
Are there some other permissions I'd have to directly grant the user to make this work? I'd rather not have to make a separate job for each schema (which does work if I submit the job under the schema's owner).