DBMS_JOBS DOES NOT WORK

[mike2000]

Hi all,

I have the follow job will be executed as follow:

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


THIS Job suppose to start every 90 sec but for some reason, it doesn't start at all.

I did check the DBA_JOBS and the job is there. Any suggestions

[vinit]

what abt the job_queue_processes and job_queue_interval parameters. Have u set them up ?
Secondly, is the job broken ?. Check out broken, failures for the job and the next_sec, next_date from dba_jobs.
Broken should be 'N' and failures should be 0.

[vinit]

Hold on..why would you want to analyze - compute ur schema every 90 seconds ??

[nagarjuna]

Probably he is experimenting with a homemade database

[mike2000]

this is only a test so that I set it to run every 90 second to be sure it it's working.


ANY WAY, IT'S NOT WORKING YET.

I HAVE SET JOB_QUEUE_PROCESS=10 AND JOB_QUEUE_INTERVAL=60, When I look into DBA_JOBS, I got the next time it will be executed however, on FAILURES=2 , what does it mean???

any advises???



[Edited by mike2000 on 10-01-2002 at 03:48 PM]

[pando]

look trace files in bdump

[jmodic]

Originally posted by mike2000
When I look into DBA_JOBS, I got the next time it will be executed however, on FAILURES=2 , what does it mean???

It means your job has allready run for two times and both times it was terminated because of some unhandled error. It will continue to resubmit itself after each failure (up to 16 times), each time with larger pause periode.

I'm almost certain the cause of the error has something to do with privileges of the owner of the job. Try the following, connected as the same user as the one who ownes the job:

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

If this reports any ORA-error, just fix it and your job will run fine.

My wild guess is that your user does not have direct execute priviledge to run that package. If that is so, you'll need to grant him:

GRANT EXECUTE ON sys.dbms_utility TO that_user;

[mike2000]

Mate,

thanks all, this is what I have :

I did have the proper grant to the user who run the job to SYS.DBMS_UTILITY and when I execute the below it ran fine:

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


but when I put it in the DBMS_JOB to run, it failed and I log into the log UNSUFFICIENT PRIVILEGE on execute SYS.DBMS_UTILITY.

I am getting closer but I couldn't figure it out what's going on.

any ideas???

[pando]

you have to do direct grant of execute priv on dbms_utility to the user if you want to use the package inside PL/SQL (stored procedure)

[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