Job to be run every 2nd Sunday of the Month

At the client’s site, a script (executing procedure)was supposed to be run every 2nd Sunday of the month, for which crontab was set.The script ran successfully for almost 5months, when suddenly this month it didn’t run.On finding out the cause, the “.sh” file permission had changed.

I personally somehow, don’t like keeping the jobs dependent on server.Especially, if its a database procedure to be run why not give a try to create/submit a job.Though,sometimes its easy to put the job in cron rather than submitting it in dbms_jobs.

As, the job had failed,i was is no mood to keep it in crontab and wanted to create/submit job within the database.I was confused, on how to submit a job that should run on every 2nd Sunday of the Month, and that is when a good friend of mine Pavan, can in to rescue me. Thanks Pavan 🙂

Here in the example i have used ‘TUESDAY’ as i was testing it today.

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
   ( job       => X 
   ,what      => 'BEGIN do_job; end;'
   ,next_date => to_date('12/10/2010 09:15:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => '(NEXT_DAY(NEXT_DAY((LAST_DAY(SYSDATE) + 1),''TUESDAY''),''TUESDAY''))'
     ,no_parse  => TRUE
    );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

The above job will look at the last day of the month, then add 1 to it and go the 1st day of the month and then check for the next day as Sunday,which would be the first Sunday of the month and then again using next_day as Sunday, it would find the next Sunday which would be the 2nd Sunday of the month.

Before implementing it on the production database (on 9.2.0.8), it was time to test it on a test database (on 10.2.0.1, only available).

08:20:46 SQL> CREATE TABLE job_table (now DATE);

Table created.

08:20:58 SQL> CREATE OR REPLACE VIEW job_view AS
SELECT TO_CHAR(now, 'DD-MON-YYYY HH:MI:SS') NOW
FROM job_table;

View created.

08:21:00 SQL> CREATE OR REPLACE PROCEDURE do_job IS
08:21:11   2  BEGIN
08:21:11   3    INSERT INTO job_table
08:21:11   4    (now)
08:21:11   5    VALUES
08:21:11   6    (SYSDATE);
08:21:11   7    COMMIT;
08:21:11   8  END do_job;
08:21:11   9  /

Procedure created.

08:21:12 SQL>
08:24:53 SQL> show parameter job

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10

08:25:44 SQL> DECLARE
08:27:04   2    X NUMBER;
08:27:04   3  BEGIN
08:27:04   4    SYS.DBMS_JOB.SUBMIT
08:27:04   5     ( job       => X
08:27:04   6     ,what      => 'BEGIN do_job; end;'
08:27:04   7     ,next_date => to_date('12/10/2010 08:30:00','dd/mm/yyyy hh24:mi:ss')
08:27:04   8     ,interval  => '(NEXT_DAY(NEXT_DAY((LAST_DAY(SYSDATE) + 1),''TUESDAY''),''TUESDAY''))'
08:27:04   9       ,no_parse  => TRUE
08:27:04  10      );
08:27:04  11    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
08:27:04  12  END;
08:27:04  13  /

PL/SQL procedure successfully completed.

08:27:06 SQL>
08:27:06 SQL>
08:27:06 SQL> @jobs

    JOB LOG_USER  THIS_DATE LAST_DATE_TIME       NEXT_DATE_TIME       INTERVAL          FAILURES WHAT
------- --------- --------- -------------------- -------------------- --------------- ---------- -------------------------------
     21 SYS                                      12-10-10 08:30:00    (NEXT_DAY(NEXT_            BEGIN do_job; end;
                                                                      DAY((LAS
                                                                      T_DAY(SYSDATE)
                                                                      + 1),'TUESDAY')
                                                                      ,'TUESDAY'))

Check the jobs LAST_DATE_TIME again at 08:31 expecting it would have run.

08:30:56 SQL> @jobs

    JOB LOG_USER  THIS_DATE LAST_DATE_TIME       NEXT_DATE_TIME       INTERVAL          FAILURES WHAT
------- --------- --------- -------------------- -------------------- --------------- ---------- -------------------------------
     21 SYS                                      12-10-10 08:30:00    (NEXT_DAY(NEXT_            BEGIN do_job; end;
                                                                      DAY((LAS
                                                                      T_DAY(SYSDATE)
                                                                      + 1),'TUESDAY')
                                                                      ,'TUESDAY'))

The above shows the job didn’t run.Lets check for the data.

08:30:58 SQL> select * from job_view;

no rows selected

08:30:59 SQL>

Hmmm, this definitely means that the job didn’t run.

removed the job, made few changed in he submit_job, check all the parameters related to job,sessions and all seemed fine, still the job wasn’t running as per the schedule.Finally, tried the below :-

09:06:54 SQL> exec dbms_job.remove(21);

PL/SQL procedure successfully completed.

09:07:05 SQL>
09:07:06 SQL> commit;

Commit complete.

09:07:07 SQL>
09:08:27 SQL> @bkgrd_process.sql

       SID    SERIAL# PROGRAM                     PID SPID         OSUSER          
---------- ---------- -------------------- ---------- ------------ --------------- -----------------------
       155          1 ORACLE.EXE (QMNC)            15 3788         SYSTEM          AQ Coordinator 
       160          1 ORACLE.EXE (MMNL)            12 3556         SYSTEM          Manageability Monitor Process 2            
       161          1 ORACLE.EXE (MMON)            11 2760         SYSTEM          Manageability Monitor Process
       162          1 ORACLE.EXE (CJQ0)            10 3168         SYSTEM          Job Queue Coordinator
       163          1 ORACLE.EXE (RECO)             9 1508         SYSTEM          distributed recovery
       164          1 ORACLE.EXE (SMON)             8 1512         SYSTEM          System Monitor Process
       165          1 ORACLE.EXE (CKPT)             7 1528         SYSTEM          checkpoint
       166          1 ORACLE.EXE (LGWR)             6 2784         SYSTEM          Redo etc.
       167          1 ORACLE.EXE (DBW0)             5 748          SYSTEM          db writer process 0 
       168          1 ORACLE.EXE (MMAN)             4 2620         SYSTEM          Memory Manager
       169          1 ORACLE.EXE (PSP0)             3 204          SYSTEM          process spawner 0
       170          1 ORACLE.EXE (PMON)             2 1684         SYSTEM          process cleanup


12 rows selected.

09:08:38 SQL> alter system kill session '162,1' immediate;

System altered.

09:07:51 SQL> alter system set job_queue_processes=0;

System altered.

09:07:53 SQL>
09:07:54 SQL> alter system set job_queue_processes=10;

System altered.

09:07:57 SQL> @bkgrd_process

       SID    SERIAL# PROGRAM                     PID SPID         OSUSER          DESCRIPTION 
---------- ---------- -------------------- ---------- ------------ --------------- ----------------------------------
       155          1 ORACLE.EXE (QMNC)            15 3788         SYSTEM          AQ Coordinator
       160          1 ORACLE.EXE (MMNL)            12 3556         SYSTEM          Manageability Monitor Process 2
       161          1 ORACLE.EXE (MMON)            11 2760         SYSTEM          Manageability Monitor Process
       162          8 ORACLE.EXE (CJQ0)            10 2972         SYSTEM          Job Queue Coordinator
       163          1 ORACLE.EXE (RECO)             9 1508         SYSTEM          distributed recovery
       164          1 ORACLE.EXE (SMON)             8 1512         SYSTEM          System Monitor Process
       165          1 ORACLE.EXE (CKPT)             7 1528         SYSTEM          checkpoint
       166          1 ORACLE.EXE (LGWR)             6 2784         SYSTEM          Redo etc.
       167          1 ORACLE.EXE (DBW0)             5 748          SYSTEM          db writer process 0
       168          1 ORACLE.EXE (MMAN)             4 2620         SYSTEM          Memory Manager
       169          1 ORACLE.EXE (PSP0)             3 204          SYSTEM          process spawner 0
       170          1 ORACLE.EXE (PMON)             2 1684         SYSTEM          process cleanup


12 rows selected.

Re-created/submitted the job again

09:14:11 SQL> @jobs

    JOB LOG_USER  THIS_DATE LAST_DATE_TIME       NEXT_DATE_TIME       INTERVAL          FAILURES WHAT
------- --------- --------- -------------------- -------------------- --------------- ---------- -------------------------------
     23 SYS                                      12-10-10 09:15:00    (NEXT_DAY(NEXT_            BEGIN do_job; end;
                                                                      DAY((LAST_DAY(S
                                                                      YSDATE) + 1),'T
                                                                      UESDAY'),'TUESD
                                                                      AY'))

Checked at 9:15

09:15:52 SQL> @jobs

    JOB LOG_USER  THIS_DATE LAST_DATE_TIME       NEXT_DATE_TIME       INTERVAL          FAILURES WHAT
------- --------- --------- -------------------- -------------------- --------------- ---------- -------------------------------
     23 SYS                 12-10-10 09:15:01    09-11-10 09:15:01    (NEXT_DAY(NEXT_          0 BEGIN do_job; end;
                                                                      DAY((LAST_DAY(S
                                                                      YSDATE) + 1),'T
                                                                      UESDAY'),'TUESD
                                                                      AY'))

09:16:35 SQL> select * from job_view;

NOW
--------------------
12-OCT-2010 09:15:01

:). The job ran, and the NEXT_DATE_TIME is 09-NOV-2010 which is the 2nd tuesday of the week.Changed the PC’s date and time to 09-NOV-2010 09:11:00 and waited to check if the job would run


09:16:46 SQL> @jobs

    JOB LOG_USER  THIS_DATE LAST_DATE_TIME       NEXT_DATE_TIME       INTERVAL          FAILURES WHAT
------- --------- --------- -------------------- -------------------- --------------- ---------- -------------------------------
     23 SYS                 09-11-10 09:15:01    14-12-10 09:15:01    (NEXT_DAY(NEXT_          0 BEGIN do_job; end;
                                                                      DAY((LAST_DAY(S
                                                                      YSDATE) + 1),'T
                                                                      UESDAY'),'TUESD
                                                                      AY'))
09:17:56 SQL> select * from job_view;

NOW
--------------------
12-OCT-2010 09:15:01
09-NOV-2010 09:15:01

Finally, implemented it on the production database (9.2.0.8 version) with NEXT_DAY as “SUNDAY”. 😉

Reference
http://psoug.org/reference/dbms_job.html

Advertisements

10 thoughts on “Job to be run every 2nd Sunday of the Month

    1. Production database version was 9.2.0.8 where it was to be implemented. If it would had been 10g, things would have been easy using dbms_scheduler 😉

      Regards,
      Anand

      1. Hi ANand,

        I went through your post & it was indeed very helpful.Can you share with me the customised script @bkgrd_process.sql which u have run above to find the SID.You can email at mfasiur@yahoo.com.LOoking forward for your reply.

        Regards,
        Fasi

  1. I have a different case.Oracle dbms_jobs are not getting triggered automotically.However,it runs fine manually when i run it using exec dbms_job.run.It updated the next_day properly after running but unfortunately doesnt get triggered on that time.Please find the screenshot below for reference.i tried your above procedure of killing the CJQ0 background process but it dint resolve.

    SQL> @scheduled_dbms_jobs.sql

    Last This Next Run
    LOG_USER Job B fail Date Date Date Interval WHAT
    ———- ——– – —- —————— —————— —————— ——— ————————————————————
    RICON 1 N 0 16-MAR-11:11:07:04 : 17-MAR-11:01:00:00 .578 EMAIL_RECEIVED_ARCHIVE_PROC;
    RICON 81 N 0 16-MAR-11:11:07:29 : 17-MAR-11:01:00:00 .578 WINBACKSERVICE_TRACKER_PROC(1000,30000);
    RICON 22 N 0 16-MAR-11:11:10:26 : 17-MAR-11:00:00:00 .534 update HOMEPAGEUSERSTATISTICS set MISSEDOPPURTUNITY=0,CALLST
    ODAY=0,TOTALLINES=0;

    RICON 101 N 0 16-MAR-11:11:11:06 : 17-MAR-11:11:11:06 1.000 WINBK_FEED_DEL();
    RICON 41 N 0 31-JAN-11:00:00:04 : 31-MAR-11:00:00:00 59.000 delete from audit_order_stats;
    RICON 42 N 0 31-JAN-11:00:00:04 : 31-MAR-11:00:00:00 59.000 delete from audit_lsr_stats;
    RICON 61 N 0 01-MAR-11:00:00:02 : 01-APR-11:00:00:00 31.000 update HOMEPAGEUSERSTATISTICS set caseclosed=0;
    RICON 62 N 0 01-MAR-11:00:00:03 : 01-APR-11:00:00:00 31.000 update HOMEPAGEOFFICESTATISTICS set caseclosed=0;

    Last This Next Run
    LOG_USER Job B fail Date Date Date Interval WHAT
    ———- ——– – —- —————— —————— —————— ——— ————————————————————
    RICON 361 N 0 16-MAR-11:11:11:22 : 17-MAR-11:04:00:00 .700 SP_TRACELOG_AUTOFOC_ARCHIVE;
    RICON 141 N 0 16-MAR-11:11:12:50 : 17-MAR-11:02:00:00 .616 RICON.SP_JOB_RELOAD_HOMEPAGE;
    RICON 442 N 0 26-FEB-11:00:00:03 : 12-MAR-11:00:00:00 14.000 RICON.SP_MARKETING_INFO_ARCHIVE;
    RICON 301 N 0 16-MAR-11:11:18:14 : 17-MAR-11:03:00:00 .654 SP_AM_JOB;
    RICON 321 N 0 16-MAR-11:11:42:51 : 17-MAR-11:04:00:00 .679 SP_CLEC_PERFORMANCE;
    RICON 461 N 0 01-MAR-11:00:00:02 : 01-APR-11:00:00:00 31.000 WAN_FEEDS_DEL;
    RICON 322 N 0 16-MAR-11:11:39:50 : 16-MAR-11:11:54:50 .010 SP_JOB_CSIRTimeOutMonitor;
    RICON 323 N 0 16-MAR-11:11:40:04 : 16-MAR-11:11:55:04 .010 SP_JOB_UNASSIGNTIMEOUT;
    RICON 341 N 0 16-MAR-11:11:40:15 : 16-MAR-11:11:40:16 .000 SP_JOB_HOMEPAGE_QUEUE_STATS;
    RICON 362 N 0 16-MAR-11:11:40:24 : 17-MAR-11:03:30:00 .659 SP_COG_OFFER_LOG_ARCHIVE;

    Last This Next Run
    LOG_USER Job B fail Date Date Date Interval WHAT
    ———- ——– – —- —————— —————— —————— ——— ————————————————————
    RICON 481 N 0 01-MAR-11:00:00:02 : 01-APR-11:00:00:00 31.000 SP_ONESOURCE_ORDER_ARCHIVE;
    RICON 401 N 0 16-MAR-11:11:40:37 : 17-MAR-11:02:00:00 .597 SP_TRACELOG_AUTOFOC_ARCHIVE;

    20 rows selected.

    SQL> conn ricon/pepper
    Connected.
    SQL> exec dbms_job.run(322);

    PL/SQL procedure successfully completed.

    SQL> exec dbms_job.run(323);

    PL/SQL procedure successfully completed.

    SQL> exec dbms_job.run(341);

    PL/SQL procedure successfully completed.

    SQL> exec dbms_job.run(362);

    PL/SQL procedure successfully completed.

    SQL> @scheduled_dbms_jobs.sql

    Last This Next Run
    LOG_USER Job B fail Date Date Date Interval WHAT
    ———- ——– – —- —————— —————— —————— ——— ————————————————————
    RICON 1 N 0 16-MAR-11:11:07:04 : 17-MAR-11:01:00:00 .578 EMAIL_RECEIVED_ARCHIVE_PROC;
    RICON 81 N 0 16-MAR-11:11:07:29 : 17-MAR-11:01:00:00 .578 WINBACKSERVICE_TRACKER_PROC(1000,30000);
    RICON 22 N 0 16-MAR-11:11:10:26 : 17-MAR-11:00:00:00 .534 update HOMEPAGEUSERSTATISTICS set MISSEDOPPURTUNITY=0,CALLST
    ODAY=0,TOTALLINES=0;

    RICON 101 N 0 16-MAR-11:11:11:06 : 17-MAR-11:11:11:06 1.000 WINBK_FEED_DEL();
    RICON 41 N 0 31-JAN-11:00:00:04 : 31-MAR-11:00:00:00 59.000 delete from audit_order_stats;
    RICON 42 N 0 31-JAN-11:00:00:04 : 31-MAR-11:00:00:00 59.000 delete from audit_lsr_stats;
    RICON 61 N 0 01-MAR-11:00:00:02 : 01-APR-11:00:00:00 31.000 update HOMEPAGEUSERSTATISTICS set caseclosed=0;
    RICON 62 N 0 01-MAR-11:00:00:03 : 01-APR-11:00:00:00 31.000 update HOMEPAGEOFFICESTATISTICS set caseclosed=0;

    Last This Next Run
    LOG_USER Job B fail Date Date Date Interval WHAT
    ———- ——– – —- —————— —————— —————— ——— ————————————————————
    RICON 361 N 0 16-MAR-11:11:11:22 : 17-MAR-11:04:00:00 .700 SP_TRACELOG_AUTOFOC_ARCHIVE;
    RICON 141 N 0 16-MAR-11:11:12:50 : 17-MAR-11:02:00:00 .616 RICON.SP_JOB_RELOAD_HOMEPAGE;
    RICON 442 N 0 26-FEB-11:00:00:03 : 12-MAR-11:00:00:00 14.000 RICON.SP_MARKETING_INFO_ARCHIVE;
    RICON 301 N 0 16-MAR-11:11:18:14 : 17-MAR-11:03:00:00 .654 SP_AM_JOB;
    RICON 321 N 0 16-MAR-11:11:42:51 : 17-MAR-11:04:00:00 .679 SP_CLEC_PERFORMANCE;
    RICON 461 N 0 01-MAR-11:00:00:02 : 01-APR-11:00:00:00 31.000 WAN_FEEDS_DEL;
    RICON 322 N 0 16-MAR-11:13:44:32 : 16-MAR-11:13:59:32 .010 SP_JOB_CSIRTimeOutMonitor;
    RICON 323 N 0 16-MAR-11:13:44:37 : 16-MAR-11:13:59:37 .010 SP_JOB_UNASSIGNTIMEOUT;
    RICON 341 N 0 16-MAR-11:13:44:49 : 16-MAR-11:13:44:50 .000 SP_JOB_HOMEPAGE_QUEUE_STATS;
    RICON 362 N 0 16-MAR-11:13:44:56 : 17-MAR-11:03:30:00 .573 SP_COG_OFFER_LOG_ARCHIVE;

    Last This Next Run
    LOG_USER Job B fail Date Date Date Interval WHAT
    ———- ——– – —- —————— —————— —————— ——— ————————————————————
    RICON 481 N 0 01-MAR-11:00:00:02 : 01-APR-11:00:00:00 31.000 SP_ONESOURCE_ORDER_ARCHIVE;
    RICON 401 N 0 16-MAR-11:11:40:37 : 17-MAR-11:02:00:00 .597 SP_TRACELOG_AUTOFOC_ARCHIVE;

    20 rows selected.

    SQL> !date
    Wed Mar 16 13:45:24 EDT 2011

    SQL> select SID,serial#,PROGRAM from v$session where program like ‘%oracle@%’;

    SID SERIAL# PROGRAM
    ———- ———- ————————————————
    1 1 oracle@v09ftwspd01 (PMON)
    2 1 oracle@v09ftwspd01 (DBW0)
    3 1 oracle@v09ftwspd01 (DBW1)
    4 1 oracle@v09ftwspd01 (DBW2)
    5 1 oracle@v09ftwspd01 (DBW3)
    6 1 oracle@v09ftwspd01 (LGWR)
    7 1 oracle@v09ftwspd01 (CKPT)
    8 1 oracle@v09ftwspd01 (SMON)
    9 1 oracle@v09ftwspd01 (RECO)
    11 1 oracle@v09ftwspd01 (ARC0)
    12 1 oracle@v09ftwspd01 (ARC1)

    SID SERIAL# PROGRAM
    ———- ———- ————————————————
    161 13051 oracle@v09ftwspd01 (CJQ0)

    12 rows selected.

    SQL> alter system kill session ‘161,13051’ immediate;
    alter system kill session ‘161,13051’ immediate
    *
    ERROR at line 1:
    ORA-01031: insufficient privileges

    SQL> conn / as sysdba
    Connected.
    SQL> alter system kill session ‘161,13051’ immediate;

    System altered.

    SQL> alter system set job_queue_processes=0;

    System altered.

    SQL> SQL>
    SQL> alter system set job_queue_processes=10;

    System altered.

    SQL> SQL>
    SQL> show parameter job

    NAME TYPE VALUE
    ———————————— ———– ——————————
    job_queue_processes integer 10
    SQL> select SID,serial#,PROGRAM from v$session where program like ‘%oracle@%’;

    SID SERIAL# PROGRAM
    ———- ———- ————————————————
    1 1 oracle@v09ftwspd01 (PMON)
    2 1 oracle@v09ftwspd01 (DBW0)
    3 1 oracle@v09ftwspd01 (DBW1)
    4 1 oracle@v09ftwspd01 (DBW2)
    5 1 oracle@v09ftwspd01 (DBW3)
    6 1 oracle@v09ftwspd01 (LGWR)
    7 1 oracle@v09ftwspd01 (CKPT)
    8 1 oracle@v09ftwspd01 (SMON)
    9 1 oracle@v09ftwspd01 (RECO)
    11 1 oracle@v09ftwspd01 (ARC0)
    12 1 oracle@v09ftwspd01 (ARC1)

    11 rows selected.

    SQL> select SID,serial#,PROGRAM from v$session where program like ‘%oracle@%’;

    SID SERIAL# PROGRAM
    ———- ———- ————————————————
    1 1 oracle@v09ftwspd01 (PMON)
    2 1 oracle@v09ftwspd01 (DBW0)
    3 1 oracle@v09ftwspd01 (DBW1)
    4 1 oracle@v09ftwspd01 (DBW2)
    5 1 oracle@v09ftwspd01 (DBW3)
    6 1 oracle@v09ftwspd01 (LGWR)
    7 1 oracle@v09ftwspd01 (CKPT)
    8 1 oracle@v09ftwspd01 (SMON)
    9 1 oracle@v09ftwspd01 (RECO)
    11 1 oracle@v09ftwspd01 (ARC0)
    12 1 oracle@v09ftwspd01 (ARC1)

    11 rows selected.

    SQL> select SID,serial#,PROGRAM from v$session where program like ‘%oracle@%’;

    SID SERIAL# PROGRAM
    ———- ———- ————————————————
    1 1 oracle@v09ftwspd01 (PMON)
    2 1 oracle@v09ftwspd01 (DBW0)
    3 1 oracle@v09ftwspd01 (DBW1)
    4 1 oracle@v09ftwspd01 (DBW2)
    5 1 oracle@v09ftwspd01 (DBW3)
    6 1 oracle@v09ftwspd01 (LGWR)
    7 1 oracle@v09ftwspd01 (CKPT)
    8 1 oracle@v09ftwspd01 (SMON)
    9 1 oracle@v09ftwspd01 (RECO)
    11 1 oracle@v09ftwspd01 (ARC0)
    12 1 oracle@v09ftwspd01 (ARC1)

    11 rows selected.

    SQL> select SID,serial#,PROGRAM from v$session where program like ‘%oracle@%’;

    SID SERIAL# PROGRAM
    ———- ———- ————————————————
    1 1 oracle@v09ftwspd01 (PMON)
    2 1 oracle@v09ftwspd01 (DBW0)
    3 1 oracle@v09ftwspd01 (DBW1)
    4 1 oracle@v09ftwspd01 (DBW2)
    5 1 oracle@v09ftwspd01 (DBW3)
    6 1 oracle@v09ftwspd01 (LGWR)
    7 1 oracle@v09ftwspd01 (CKPT)
    8 1 oracle@v09ftwspd01 (SMON)
    9 1 oracle@v09ftwspd01 (RECO)
    11 1 oracle@v09ftwspd01 (ARC0)
    12 1 oracle@v09ftwspd01 (ARC1)

    11 rows selected.

    SQL> !ls
    1.sql blocking.sql output.lst session_event_users.sql spreport.sql
    Blockers.sql cursor_usage.sql part.sql session_jobs.sql status.sql
    Tablespace_Info.sql findtbls.sql running_jobs.sql show_session_sql.sql status_sid.sql
    active_session_waits.log fk_withoutindex.sql s_tabsp.log showactive.sql table_list.out
    active_session_waits.sql free.sql scheduled_dbms_jobs.sql sizeit.sql tnsnames.ora
    add_idx.sql gscounts.sql script.log spacerep.sql total.sql
    afiedt.buf ikram.sql script.sql spacereport.sql txrbs.sql
    all_sql.sql machine.sql script2.log specific_session_waits.sql users.sql
    bigtables.sql maxsize.sql script2.sql spid.sql what.sql
    bkgrd_process.sql orders.sql session_event_users.log sprepins.sql

    SQL> @scheduled_dbms_jobs.sql

    Last This Next Run
    LOG_USER Job B fail Date Date Date Interval WHAT
    ———- ——– – —- —————— —————— —————— ——— ————————————————————
    RICON 1 N 0 16-MAR-11:11:07:04 : 17-MAR-11:01:00:00 .578 EMAIL_RECEIVED_ARCHIVE_PROC;
    RICON 81 N 0 16-MAR-11:11:07:29 : 17-MAR-11:01:00:00 .578 WINBACKSERVICE_TRACKER_PROC(1000,30000);
    RICON 22 N 0 16-MAR-11:11:10:26 : 17-MAR-11:00:00:00 .534 update HOMEPAGEUSERSTATISTICS set MISSEDOPPURTUNITY=0,CALLST
    ODAY=0,TOTALLINES=0;

    RICON 101 N 0 16-MAR-11:11:11:06 : 17-MAR-11:11:11:06 1.000 WINBK_FEED_DEL();
    RICON 41 N 0 31-JAN-11:00:00:04 : 31-MAR-11:00:00:00 59.000 delete from audit_order_stats;
    RICON 42 N 0 31-JAN-11:00:00:04 : 31-MAR-11:00:00:00 59.000 delete from audit_lsr_stats;
    RICON 61 N 0 01-MAR-11:00:00:02 : 01-APR-11:00:00:00 31.000 update HOMEPAGEUSERSTATISTICS set caseclosed=0;
    RICON 62 N 0 01-MAR-11:00:00:03 : 01-APR-11:00:00:00 31.000 update HOMEPAGEOFFICESTATISTICS set caseclosed=0;

    Last This Next Run
    LOG_USER Job B fail Date Date Date Interval WHAT
    ———- ——– – —- —————— —————— —————— ——— ————————————————————
    RICON 361 N 0 16-MAR-11:11:11:22 : 17-MAR-11:04:00:00 .700 SP_TRACELOG_AUTOFOC_ARCHIVE;
    RICON 141 N 0 16-MAR-11:11:12:50 : 17-MAR-11:02:00:00 .616 RICON.SP_JOB_RELOAD_HOMEPAGE;
    RICON 442 N 0 26-FEB-11:00:00:03 : 12-MAR-11:00:00:00 14.000 RICON.SP_MARKETING_INFO_ARCHIVE;
    RICON 301 N 0 16-MAR-11:11:18:14 : 17-MAR-11:03:00:00 .654 SP_AM_JOB;
    RICON 321 N 0 16-MAR-11:11:42:51 : 17-MAR-11:04:00:00 .679 SP_CLEC_PERFORMANCE;
    RICON 461 N 0 01-MAR-11:00:00:02 : 01-APR-11:00:00:00 31.000 WAN_FEEDS_DEL;
    RICON 322 N 0 16-MAR-11:13:44:32 : 16-MAR-11:13:59:32 .010 SP_JOB_CSIRTimeOutMonitor;
    RICON 323 N 0 16-MAR-11:13:44:37 : 16-MAR-11:13:59:37 .010 SP_JOB_UNASSIGNTIMEOUT;
    RICON 341 N 0 16-MAR-11:13:44:49 : 16-MAR-11:13:44:50 .000 SP_JOB_HOMEPAGE_QUEUE_STATS;
    RICON 362 N 0 16-MAR-11:13:44:56 : 17-MAR-11:03:30:00 .573 SP_COG_OFFER_LOG_ARCHIVE;

    Last This Next Run
    LOG_USER Job B fail Date Date Date Interval WHAT
    ———- ——– – —- —————— —————— —————— ——— ————————————————————
    RICON 481 N 0 01-MAR-11:00:00:02 : 01-APR-11:00:00:00 31.000 SP_ONESOURCE_ORDER_ARCHIVE;
    RICON 401 N 0 16-MAR-11:11:40:37 : 17-MAR-11:02:00:00 .597 SP_TRACELOG_AUTOFOC_ARCHIVE;

    20 rows selected.

    SQL> !date
    Wed Mar 16 14:06:56 EDT 2011

    SQL>

  2. That was really interesting. I googled for Oracle job to be run on 2nd sunday of every month and got here. It is a very good insight, but I think that

    NEXT_DAY(NEXT_DAY((LAST_DAY(SYSDATE) + 1),’TUESDAY’),’TUESDAY’)

    Would not work if the coming month starts on a Tuesday, and we would need something:

    DECODE (trim(to_char (LAST_DAY(SYSDATE) + 1, ‘DAY’)), ‘TUESDAY’, NEXT_DAY((LAST_DAY(SYSDATE) + 1),’TUESDAY’), NEXT_DAY(NEXT_DAY((LAST_DAY(SYSDATE) + 1),’TUESDAY’),’TUESDAY’))

    Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s