Link to home
Start Free TrialLog in
Avatar of koolsyed
koolsyed

asked on

Oracle DBMS job doesnt run as scheduled

Hey Pals.
I created a DBMS job in Oracle. This is the first job that is being created on this database.
When i look at all_jobs view, next_date and interval..etc looks fine. But the job doesnt run at the time expected.
Suppose next date shows 8-MAR-08 2 AM. But the job doesnt run at all.
When i force run it..it doesnt give me any error..
Any thoughts?..Any Params that need to be set?
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image

Can u post the job creation script here ? Also, did u check that the timezone of the server is the same as you're expecting it to be ?
Avatar of koolsyed
koolsyed

ASKER

yes, i am comparing the next run time and sysdate from the same server..
Job script is something like this.
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => 'PACKAGE.PROCEDURE(PARAMETERS);',
next_date => TRUNC(SYSDATE) + 1/24,
interval => 'TRUNC(SYSDATE) + 1/24'
);
COMMIT;
END;
Try this :
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT
(
job => jobno,
what => 'BEGIN PACKAGE.PROCEDURE(PARAMETERS); END;',
next_date => TRUNC(SYSDATE) + 1/24,
interval => 'TRUNC(SYSDATE) + 1/24'
);
COMMIT;
END;
/

Open in new window

nope,tried with begin end block already no luck...i feel, we need to check for some job parameters..I am nt sure wht exactly should be set so that the jobs are in queue to execute.
Can u confirm that you have at least 1 job queue running ?
This is the first job that is created...Can you help me with which view to chk on the queue you are referring?
You should declare the arguments passed to the proc. I did for you but VARCHAR2(100).

DECLARE
  jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT
(
job => jobno,
what => 'DECLARE PARAMETERS VARCHAR2(100); BEGIN PACKAGE.PROCEDURE(PARAMETERS); END;',
next_date => TRUNC(SYSDATE) + 1/24,
interval => 'TRUNC(SYSDATE) + 1/24'
);
COMMIT;
END;
/
Yeah, it would help if you show us the actual call to the stored procedure.
Also, post the output of this query : SELECT * FROM all_jobs WHERE what LIKE '%PACKAGE%';
Avatar of schwertner
This will schedule the procedure to run once weekly.
Right down the job number in order to manipulate the Job.


set serveroutput on
set linesize 10000
variable x number;
begin
   DBMS_OUTPUT.enable(100000);  
   dbms_job.submit(:x,package_name.procedure_name(parameters_list;',trunc(sysdate),'trunc(sysdate+7)');
   commit;
   dbms_output.put_line(TO_char(:x));
end;
Can you post the output of this query?  (NOTE:  it has to be run as a privileged account)  Since the job_queue_interval parameter has become a hidden parameter, this is the only way to get the value.  That parameter is what controls how often the job queue process wakes up and processes requests.

select a.ksppinm, b.ksppstvl
from x$ksppi a, x$ksppsv b
where a.indx = b.indx and
a.ksppinm in ('job_queue_processes','_job_queue_interval');

Open in new window

Sorry, I did not notice your version was Oracle8.

job_queue_interval is not a hidden parameter in Oracle8.  Can you post the value of that parameter?
ASKER CERTIFIED SOLUTION
Avatar of Naveen Kumar
Naveen Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial