Home » SQL & PL/SQL » SQL & PL/SQL » dbms_scheduler
dbms_scheduler [message #252666] Thu, 19 July 2007 10:38 Go to next message
ozzy80
Messages: 55
Registered: June 2005
Member
Friends,

@Oracle 10g

Can someone help me with dbms_scheduler problem... the issue is that a job created in the oracle scheduler does not execute at the given time. It will run if you execute the dbms_scheduler.run_job() command.

There might be some background process that does the checking and it is missing somehow... not sure what is that and where and how to look at it.

Plz help!
~O
Re: dbms_scheduler [message #252667 is a reply to message #252666] Thu, 19 July 2007 10:42 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How did you create a job? Could you post the command you used to do that?
Re: dbms_scheduler [message #252672 is a reply to message #252667] Thu, 19 July 2007 10:52 Go to previous messageGo to next message
ozzy80
Messages: 55
Registered: June 2005
Member
Littlefoot wrote on Thu, 19 July 2007 10:42
How did you create a job? Could you post the command you used to do that?


sorry about that...

begin
  dbms_scheduler.create_job(
      job_name => 'TEST_BATCH'
     ,job_type => 'executable'
     ,auto_drop => FALSE
     ,job_action => 'D:\batch\script\test.bat'
     ,start_date => to_date('06/27/2007 10:28 AM', 'MM/DD/YYYY HH:MI AM')
     ,repeat_interval => 'FREQ=DAILY'
     ,enabled => TRUE
     ,comments => 'DEV TEST BATCH RUN.');
end;
/
Re: dbms_scheduler [message #252683 is a reply to message #252666] Thu, 19 July 2007 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Does issuing a COMMIT; after dbms_scheduler.create_job statement change behavior?
Re: dbms_scheduler [message #252688 is a reply to message #252683] Thu, 19 July 2007 12:05 Go to previous messageGo to next message
ozzy80
Messages: 55
Registered: June 2005
Member
The job still does not run at the time and frequency mentioned, even after the "commit;"
Re: dbms_scheduler [message #252696 is a reply to message #252688] Thu, 19 July 2007 12:38 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the result of the following statement?
SELECT last_date, last_sec,
       next_date, next_sec,
       interval
FROM dba_jobs
WHERE job = <number of the test_batch job>;
Re: dbms_scheduler [message #252702 is a reply to message #252696] Thu, 19 July 2007 13:16 Go to previous messageGo to next message
ozzy80
Messages: 55
Registered: June 2005
Member
I think you mean dba_scheduler_jobs....

Query I ran...

SELECT job_name, job_type, repeat_interval, enabled, restartable, state, 
        last_start_date, next_run_date, schedule_limit
FROM dba_scheduler_jobs
where job_name = 'TEST_BATCH';

Result:

# JOB_NAME   JOB_TYPE   REPEAT_INTERVAL           ENABLED RESTARTABLE STATE     LAST_START_DATE NEXT_RUN_DATE SCHEDULE_LIMIT
= ========== ========== ========================= ======= =========== ========= =============== ============= ==============
1 TEST_BATCH EXECUTABLE FREQ=SECONDLY;INTERVAL=10 TRUE    FALSE       SCHEDULED {null}          19-JUL-07 10. {null}        
Re: dbms_scheduler [message #252709 is a reply to message #252666] Thu, 19 July 2007 13:47 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Why would you think he meant a different table than he said ?
Re: dbms_scheduler [message #252719 is a reply to message #252709] Thu, 19 July 2007 14:36 Go to previous messageGo to next message
ozzy80
Messages: 55
Registered: June 2005
Member
MarcL wrote on Thu, 19 July 2007 13:47
Why would you think he meant a different table than he said ?


Because I think dbms_jobs will create records in dba_jobs and dbms_scheduler creates records in dba_scheduler_jobs...

In any case... my dba_jobs table is empty...
Re: dbms_scheduler [message #252730 is a reply to message #252666] Thu, 19 July 2007 16:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post back here the CUT & PASTE results from
SELECT STATUS , ERROR#, ACTUAL_START_DATE, RUN_DURATION 
from DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'TEST_BATCH';
Re: dbms_scheduler [message #252731 is a reply to message #252730] Thu, 19 July 2007 16:15 Go to previous messageGo to next message
ozzy80
Messages: 55
Registered: June 2005
Member
# STATUS    ERROR# ACTUAL_START_DATE RUN_DURATION
= ========= ====== ================= ============
1 SUCCEEDED 0      19-JUL-07 10.     +000 00:00: 
2 SUCCEEDED 0      19-JUL-07 10.     +000 00:00: 
3 SUCCEEDED 0      19-JUL-07 10.     +000 00:00: 
4 SUCCEEDED 0      19-JUL-07 10.     +000 00:00: 

Re: dbms_scheduler [message #252746 is a reply to message #252666] Thu, 19 July 2007 19:27 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
DBMS_SCHEDULER works for me & for most other folks.
Based upon the "SUCCEEDED" status, it worked for you too.
You're On Your Own (YOYO)!
Previous Topic: ORA-00904: "ISNULL":invalid Identifier
Next Topic: Max 30 date
Goto Forum:
  


Current Time: Sun Dec 08 06:02:15 CST 2024