DBMS_JOBS DOES NOT WORK

lest we forget the user should be using dbms_stats if available on their particular db version

steve

Quote:

---

Originally posted by mike2000
Pando,
thanks for your prompt reply.

I did grant the users who owned this job with EXECUTE to SYS.DBMS_UTILITY already so that when I run it manually it work.

when you mean I have to direct grant to execute on DBMS_UTILITY, you mean I have to log in as SYS and run this query again:
GRANT EXECUTE ON SYS.DBMS_UTILITY TO TEST.

is that what you mean???

thanks Pando

---

yes, privs through roles wont work for pl/sql stored procedures

also as someone suggested you should be using dbms_stats if you are using 8.1.x upwards

I am not sure.. I am just asking.. Does this have anything to do with execution time and interval??? Say like, execution time for this particular analyze is 2 mins and interval is 90 secs. Does this cause the job to be terminated?? Juts a doubt..

no it wont

thanks all for all quick response.

I tried to search in here for DBMS_STAT. but I didn't find anything that was usefull.

Could you please give me the instruction of how to use SYS.DBMS_STAT ???

thanks

dbms_stats

http://tahiti.oracle.com
..supplied pl/sql packages guide and of course
you can desc dbms_stats to see what procedures and functions are available in that package.

regards

steve

Sorry to come back to this topic again.

I know I can use dbms_stat but I really like to make the dbms_job work as well but for some reason DBMS_JOB still have problem.


Below is the script of the user who own the package, but it kept saying unsuffiecient privilege on DBMS_UTILITY in the log file.

I don't get this


CREATE USER DB_ADMIN IDENTIFIED BY VALUES '910A6DB7DA7AC6FB'
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK
/
GRANT CONNECT TO DB_ADMIN WITH ADMIN OPTION
/
GRANT DBA TO DB_ADMIN WITH ADMIN OPTION
/
GRANT RESOURCE TO DB_ADMIN WITH ADMIN OPTION
/
ALTER USER DB_ADMIN DEFAULT ROLE CONNECT,
DBA,
RESOURCE
/
GRANT UNLIMITED TABLESPACE TO DB_ADMIN WITH ADMIN OPTION
/
GRANT EXECUTE ON SYS.DBMS_JOB TO DB_ADMIN
/
GRANT EXECUTE ON SYS.DBMS_UTILITY TO DB_ADMIN
/

Connecting as DB_ADMIN and isue the following:

BEGIN
DBMS_UTILITY.ANALYZE_SCHEMA('TEST','COMPUTE');
END;
/

Does it raise any errors? Like "ORA-20000: Insufficient privileges..."? If so, you'll need to grant ANALYZE ANY system privilege to your user DB_ADMIN.

If this runs succesfully in an anonymous block there is no reason why it shouldn't run inside a job.

[Edited by jmodic on 10-08-2002 at 03:44 PM]

var j number
begin
dbms_job.submit
(
:j,
'SYS.DBMS_UTILITY.ANALYZE_SCHEMA(''TEST'',''COMPUTE'');
',sysdate,'sysdate+(90/(24*60*60))'
);
COMMIT;
end;

Mike, sorry for the confusion. After asking the question to which you just answered, I realised you've specified your submit command in your initial post - that's why I have rewritten my question, but while I was doing that, you have allready answered to the question that is not visible any more. Oh, oh, oh, this must be confusing to anyone except you and me, I guess ;).

Anyway, can you run your WHAT parameter inside annonymous block as user DB_ADMIN?