-
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?
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
Simply
Grant analyze any to the_user;
will do it.
Thank you, anyway.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|