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
10 thoughts on “Job to be run every 2nd Sunday of the Month”
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!
Thanks for sharing the info 🙂
thanks for the very useful post! it really helps me solve my prob =D
Glad it helped you 🙂
Anand
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 ‘%[email protected]%’;
SID SERIAL# PROGRAM
———- ———- ————————————————
1 1 [email protected] (PMON)
2 1 [email protected] (DBW0)
3 1 [email protected] (DBW1)
4 1 [email protected] (DBW2)
5 1 [email protected] (DBW3)
6 1 [email protected] (LGWR)
7 1 [email protected] (CKPT)
8 1 [email protected] (SMON)
9 1 [email protected] (RECO)
11 1 [email protected] (ARC0)
12 1 [email protected] (ARC1)
SID SERIAL# PROGRAM
———- ———- ————————————————
161 13051 [email protected] (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 ‘%[email protected]%’;
SID SERIAL# PROGRAM
———- ———- ————————————————
1 1 [email protected] (PMON)
2 1 [email protected] (DBW0)
3 1 [email protected] (DBW1)
4 1 [email protected] (DBW2)
5 1 [email protected] (DBW3)
6 1 [email protected] (LGWR)
7 1 [email protected] (CKPT)
8 1 [email protected] (SMON)
9 1 [email protected] (RECO)
11 1 [email protected] (ARC0)
12 1 [email protected] (ARC1)
11 rows selected.
SQL> select SID,serial#,PROGRAM from v$session where program like ‘%[email protected]%’;
SID SERIAL# PROGRAM
———- ———- ————————————————
1 1 [email protected] (PMON)
2 1 [email protected] (DBW0)
3 1 [email protected] (DBW1)
4 1 [email protected] (DBW2)
5 1 [email protected] (DBW3)
6 1 [email protected] (LGWR)
7 1 [email protected] (CKPT)
8 1 [email protected] (SMON)
9 1 [email protected] (RECO)
11 1 [email protected] (ARC0)
12 1 [email protected] (ARC1)
11 rows selected.
SQL> select SID,serial#,PROGRAM from v$session where program like ‘%[email protected]%’;
SID SERIAL# PROGRAM
———- ———- ————————————————
1 1 [email protected] (PMON)
2 1 [email protected] (DBW0)
3 1 [email protected] (DBW1)
4 1 [email protected] (DBW2)
5 1 [email protected] (DBW3)
6 1 [email protected] (LGWR)
7 1 [email protected] (CKPT)
8 1 [email protected] (SMON)
9 1 [email protected] (RECO)
11 1 [email protected] (ARC0)
12 1 [email protected] (ARC1)
11 rows selected.
SQL> select SID,serial#,PROGRAM from v$session where program like ‘%[email protected]%’;
SID SERIAL# PROGRAM
———- ———- ————————————————
1 1 [email protected] (PMON)
2 1 [email protected] (DBW0)
3 1 [email protected] (DBW1)
4 1 [email protected] (DBW2)
5 1 [email protected] (DBW3)
6 1 [email protected] (LGWR)
7 1 [email protected] (CKPT)
8 1 [email protected] (SMON)
9 1 [email protected] (RECO)
11 1 [email protected] (ARC0)
12 1 [email protected] (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>
Hi,
What is the value set for the parameter “job_queue_processes”?
Anand
Hi anand,
Nice that it worked a bit, other wise i would be in problem….. !! 🙂
Nice posting Anand..!!
FREQ=WEEKLY; INTERVAL=2; BYDAY=SUN;
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
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 [email protected] forward for your reply.
Regards,
Fasi