This discussion is archived
4 Replies Latest reply: Oct 6, 2012 11:47 AM by spajdy RSS

DBMS scheduler jobs running twice

892489 Newbie
Currently Being Moderated
Hi,

I have 4 DBMS scheduler jobs , which checks for a specific job status in DB and sends an email , when i started the schedule for the first week the jobs executed fine from next week I am getting two emails from each job , when i check the logs USER_SCHEDULER_JOB_RUN_DETAILS I see only one run , which seems weird to me so i disabled one job and left the three jobs in schedule , next time i got two emails from 3 jobs and one from disabled job . After checking logs i see that there is no entry of the disabled job execution . I am not sure where is the problem i can't find any log from where the disabled job executing. Please help me

Job schedule is to run every Saturday

Interval setup :

start_date => trunc(SYSDATE)+ 8.5/24,
repeat_interval => 'TRUNC(LEAST(NEXT_DAY(SYSDATE,''SATURDAY'') )) + 8.5/24'

Suresh
  • 1. Re: DBMS scheduler jobs running twice
    892489 Newbie
    Currently Being Moderated
    Hi,

    I tried to schedule the same jobs using DBMS_JOB but i still get the same problem , I created the procedure with all code in and scheduled it using dbms job , first day it run once second day it run twice ( sending two emails) Inow i am not sure if issue is with my code or scheduler

    Procedure

    Declare
    v_count number;
    v_Recipient VARCHAR2(400) := 'sender@company.com';
    v_Subject VARCHAR2(80) := 'TEST_Email';
    v_Mail_Host VARCHAR2(30) := 'localhost';
    v_Mail_Conn utl_smtp.Connection;
    crlf VARCHAR2(2) := chr(13)||chr(10);
    BEGIN
    select count(*) into v_count from TEC_CODERETURN@RPRD where interface like 'FOR002B' and trunc(rundate) =trunc(sysdate);
    if v_count = 0
    then
    v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
    utl_smtp.Rcpt(v_Mail_Conn, 'receiver@company.com');
    UTL_SMTP.OPEN_DATA(v_Mail_Conn);
    utl_smtp.WRITE_RAW_DATA(v_Mail_Conn, UTL_RAW.CAST_TO_RAW(
    'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || UTL_TCP.CRLF ||
    'From: ' || 'sender@company.com' || UTL_TCP.CRLF ||
    'Subject: '|| v_Subject || UTL_TCP.CRLF ||
    'To: ' || v_Recipient || UTL_TCP.CRLF ||
    'This is a test Alert'|| UTL_TCP.CRLF
    ));
    UTL_SMTP.CLOSE_DATA(v_mail_conn);
    utl_smtp.Quit(v_mail_conn);
    end if;
    EXCEPTION
    WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
    raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
    END;
    /

    DBMS job creation

    DECLARE
    jobno NUMBER;
    BEGIN
    DBMS_JOB.submit
    (job => jobno,
    what => 'TEST_ALERT;',
    next_date => trunc(sysdate)+0.1/24,
    interval => 'SYSDATE + 1',
    no_parse => TRUE );

    DBMS_OUTPUT.put_line ('Created Job - the job number is:' || TO_CHAR (jobno));
    COMMIT;
    END;
    /

    Suresh
  • 2. Re: DBMS scheduler jobs running twice
    spajdy Pro
    Currently Being Moderated
    It's really ver curious.
    When you run you code (procedure) by hand how many email do you receive ?

    Are you sure that you have this job defined only once ?
    Run job sucessfuly (dba_jobs.failures=0 for this job)?
  • 3. Re: DBMS scheduler jobs running twice
    892489 Newbie
    Currently Being Moderated
    Hi,

    I tested these jobs with minutes interval close to 30 40 times , it runs fine with one email. once the day passes it starts sending second email. one time i disabled the job for a day but i still got email form the job and i don't see any log of the job run. Seems Oracle bug.

    We opened SR but no reply yet so I had to switch to UNIX using Cron and sendmail .

    Suresh
  • 4. Re: DBMS scheduler jobs running twice
    spajdy Pro
    Currently Being Moderated
    Are you sure that you don't have two instances of the job ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points