running a JOB in queue

[dba_admin]

I submit a job by doing this:

VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_STATS.GATHER_DATABASE_STATS;',
sysdate, 'SYSDATE + 1',
TRUE, 1, FALSE);
commit;
END;
/

Now, I am getting this:

ORA-12012: error on auto execute of job 22
ORA-20000: Insufficient privileges to analyze an object in Database
ORA-06512: at "SYS.DBMS_STATS", line 4621
ORA-06512: at "SYS.DBMS_STATS", line 4756
ORA-06512: at "SYS.DBMS_STATS", line 4741

Please note: I am logging in as a user with dba role. I can run "exec 'DBMS_STATS.GATHER_DATABASE_STATS;" without any problem. Why cannot I run it in a job?

[jmodic]

Originally posted by dba_admin
Please note: I am logging in as a user with dba role. I can run "exec 'DBMS_STATS.GATHER_DATABASE_STATS;" without any problem. Why cannot I run it in a job?

Because inside PL/SQL your roles are disabled. In SQL*Plus do the following:
- Log in as that DBA user
- Isue: SET ROLE NONE;
- Isue: exec 'DBMS_STATS.GATHER_DATABASE_STATS;
This is exactly what happens when you switch your executing environment into PL/SQL. And you'll see you'll get the same "insufficient privileges" error message.

You need an explicit priviledge on that package, not the one obtained through role. Either let SYS grant you "EXECUTE ON DBMS_STATS" or let some DBA user grant you "EXECUTE ANY PROCEDURE" and your job will run with no problems.

[dba_admin]

Simply

Grant analyze any to the_user;

will do it.

Thank you, anyway.