Thinking Out Loud

May 4, 2013

It’s a dirty job

Filed under: oracle — mdinh @ 9:50 pm

Before, I worked for organization that frowned upon database jobs and only uses cron job.
Now, it’s just the opposite and I get to learn about dbms_scheduler job.

Create job to run PLSQL_BLOCK every 5 minutes at: 21,22,23 (24 hour format)

LAX:(MDINH@db01)> @createjob

SYSDATE
-------------------
2013-05-04 12:29:55

LAX:(MDINH@db01)> begin
  2   dbms_scheduler.create_job (
  3    job_name => 'DIRTY_JOB',
  4    job_type => 'PLSQL_BLOCK',
  5    job_action => 'BEGIN NULL; END;',
  6    start_date => trunc(sysdate)+21/24,
  7    repeat_interval => 'FREQ=minutely; BYHOUR=21,22,23; INTERVAL=5',
  8    end_date => NULL,
  9    enabled => TRUE);
 10  end;
 11  /

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> @@checkjob dirty
LAX:(MDINH@db01)> set lines 200 veri off echo off
-- dba_scheduler_jobs

JOB_NAME                       REPEAT_INTERVAL                                              NEXT_RUN_DATE
------------------------------ ------------------------------------------------------------ ----------------------------------------
DIRTY_JOB                      FREQ=minutely; BYHOUR=21,22,23; INTERVAL=5                   04-MAY-13 09.00.00.000000 PM -07:00

-- dba_scheduler_job_run_details

no rows selected

-- FORCE RUN_JOB
LAX:(MDINH@db01)> exec dbms_scheduler.RUN_JOB('DIRTY_JOB');

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> @@checkjob dirty
LAX:(MDINH@db01)> set lines 200 veri off echo off
-- dba_scheduler_jobs

JOB_NAME                       REPEAT_INTERVAL                                              NEXT_RUN_DATE
------------------------------ ------------------------------------------------------------ ----------------------------------------
DIRTY_JOB                      FREQ=minutely; BYHOUR=21,22,23; INTERVAL=5                   04-MAY-13 09.00.00.000000 PM -07:00

-- dba_scheduler_job_run_details

LOG_DATE                            JOB_NAME                       STATUS     ACTUAL_START_DATE                   RUN_DURATION
----------------------------------- ------------------------------ ---------- ----------------------------------- ---------------
04-MAY-13 12.29.55.112836 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.29.55.111618 PM -07:00 +000 00:00:00

!!! Shoot – do not want to wail until 21:00 to know results !!!
— CHANGE START_DATE & REPEAT_INTERVAL

LAX:(MDINH@db01)> exec dbms_scheduler.SET_ATTRIBUTE(name=>'DIRTY_JOB',attribute=>'start_date',value=>SYSDATE);

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> exec dbms_scheduler.SET_ATTRIBUTE(name=>'DIRTY_JOB',attribute=>'repeat_interval',value=>'FREQ=minutely;BYHOUR=12,13;INTERVAL=5');

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> @@checkjob dirty
LAX:(MDINH@db01)> set lines 200 veri off echo off
-- dba_scheduler_jobs

JOB_NAME                       REPEAT_INTERVAL                                              NEXT_RUN_DATE
------------------------------ ------------------------------------------------------------ ----------------------------------------
DIRTY_JOB                      FREQ=minutely;BYHOUR=12,13;INTERVAL=5                        04-MAY-13 12.34.55.000000 PM -07:00

-- dba_scheduler_job_run_details

LOG_DATE                            JOB_NAME                       STATUS     ACTUAL_START_DATE                   RUN_DURATION
----------------------------------- ------------------------------ ---------- ----------------------------------- ---------------
04-MAY-13 12.29.55.112836 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.29.55.111618 PM -07:00 +000 00:00:00

Back from lunch and looks good.

Job did not run after 14:00 as expected.

LAX:(MDINH@db01)> select sysdate from dual;
-------------------
2013-05-04 14:10:43

LAX:(MDINH@db01)> @checkjob dirty
-- dba_scheduler_jobs

JOB_NAME                       REPEAT_INTERVAL                                              NEXT_RUN_DATE
------------------------------ ------------------------------------------------------------ ----------------------------------------
DIRTY_JOB                      FREQ=minutely;BYHOUR=12,13;INTERVAL=5                        05-MAY-13 12.04.55.000000 PM -07:00

-- dba_scheduler_job_run_details

LOG_DATE                            JOB_NAME                       STATUS     ACTUAL_START_DATE                   RUN_DURATION
----------------------------------- ------------------------------ ---------- ----------------------------------- ---------------
04-MAY-13 12.29.55.112836 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.29.55.111618 PM -07:00 +000 00:00:00
04-MAY-13 12.34.55.045811 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.34.55.038072 PM -07:00 +000 00:00:00
04-MAY-13 12.39.55.050034 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.39.55.040566 PM -07:00 +000 00:00:00
04-MAY-13 12.44.55.079379 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.44.55.057040 PM -07:00 +000 00:00:00
04-MAY-13 12.49.55.132232 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.49.55.124111 PM -07:00 +000 00:00:00
04-MAY-13 12.54.55.238525 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.54.55.230027 PM -07:00 +000 00:00:00
04-MAY-13 12.59.55.046174 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.59.55.037725 PM -07:00 +000 00:00:00
04-MAY-13 01.04.55.057574 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.04.55.049089 PM -07:00 +000 00:00:00
04-MAY-13 01.09.55.043001 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.09.55.038407 PM -07:00 +000 00:00:00
04-MAY-13 01.14.55.058771 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.14.55.053925 PM -07:00 +000 00:00:00
04-MAY-13 01.19.55.048791 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.19.55.039703 PM -07:00 +000 00:00:00
04-MAY-13 01.24.55.038104 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.24.55.029655 PM -07:00 +000 00:00:00
04-MAY-13 01.29.55.046963 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.29.55.041092 PM -07:00 +000 00:00:00
04-MAY-13 01.34.55.063285 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.34.55.055247 PM -07:00 +000 00:00:00
04-MAY-13 01.39.55.050528 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.39.55.041886 PM -07:00 +000 00:00:00
04-MAY-13 01.44.55.042718 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.44.55.023271 PM -07:00 +000 00:00:00
04-MAY-13 01.49.55.056224 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.49.55.051302 PM -07:00 +000 00:00:00
04-MAY-13 01.54.55.046269 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.54.55.037763 PM -07:00 +000 00:00:00
04-MAY-13 01.59.55.059423 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.59.55.051539 PM -07:00 +000 00:00:00

19 rows selected.

LAX:(MDINH@db01)> @dropjob

LAX:(MDINH@db01)> exec dbms_scheduler.DISABLE ('DIRTY_JOB',TRUE);

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> exec dbms_scheduler.STOP_JOB('DIRTY_JOB',TRUE);
BEGIN dbms_scheduler.STOP_JOB('DIRTY_JOB',TRUE); END;

*
ERROR at line 1:
ORA-27366: job "MDINH.DIRTY_JOB" is not running
ORA-06512: at "SYS.DBMS_ISCHED", line 199
ORA-06512: at "SYS.DBMS_SCHEDULER", line 557
ORA-06512: at line 1

LAX:(MDINH@db01)> exec dbms_scheduler.DROP_JOB('DIRTY_JOB',TRUE);

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> @@checkjob dirty
LAX:(MDINH@db01)> set lines 200 veri off echo off
-- dba_scheduler_jobs

no rows selected

-- dba_scheduler_job_run_details

LOG_DATE                            JOB_NAME                       STATUS     ACTUAL_START_DATE                   RUN_DURATION
----------------------------------- ------------------------------ ---------- ----------------------------------- ---------------
04-MAY-13 12.29.55.112836 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.29.55.111618 PM -07:00 +000 00:00:00
04-MAY-13 12.34.55.045811 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.34.55.038072 PM -07:00 +000 00:00:00
04-MAY-13 12.39.55.050034 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.39.55.040566 PM -07:00 +000 00:00:00
04-MAY-13 12.44.55.079379 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.44.55.057040 PM -07:00 +000 00:00:00
04-MAY-13 12.49.55.132232 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.49.55.124111 PM -07:00 +000 00:00:00
04-MAY-13 12.54.55.238525 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.54.55.230027 PM -07:00 +000 00:00:00
04-MAY-13 12.59.55.046174 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 12.59.55.037725 PM -07:00 +000 00:00:00
04-MAY-13 01.04.55.057574 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.04.55.049089 PM -07:00 +000 00:00:00
04-MAY-13 01.09.55.043001 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.09.55.038407 PM -07:00 +000 00:00:00
04-MAY-13 01.14.55.058771 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.14.55.053925 PM -07:00 +000 00:00:00
04-MAY-13 01.19.55.048791 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.19.55.039703 PM -07:00 +000 00:00:00
04-MAY-13 01.24.55.038104 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.24.55.029655 PM -07:00 +000 00:00:00
04-MAY-13 01.29.55.046963 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.29.55.041092 PM -07:00 +000 00:00:00
04-MAY-13 01.34.55.063285 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.34.55.055247 PM -07:00 +000 00:00:00
04-MAY-13 01.39.55.050528 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.39.55.041886 PM -07:00 +000 00:00:00
04-MAY-13 01.44.55.042718 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.44.55.023271 PM -07:00 +000 00:00:00
04-MAY-13 01.49.55.056224 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.49.55.051302 PM -07:00 +000 00:00:00
04-MAY-13 01.54.55.046269 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.54.55.037763 PM -07:00 +000 00:00:00
04-MAY-13 01.59.55.059423 PM -07:00 DIRTY_JOB                      SUCCEEDED  04-MAY-13 01.59.55.051539 PM -07:00 +000 00:00:00

19 rows selected.

LAX:(MDINH@db01)> exec dbms_scheduler.PURGE_LOG();

PL/SQL procedure successfully completed.

LAX:(MDINH@db01)> @checkjob dirty
-- dba_scheduler_jobs

no rows selected

-- dba_scheduler_job_run_details

no rows selected

LAX:(MDINH@db01)>

 

SCRIPTS Used:

$ cat checkjob.sql

set lines 200 veri off echo off
set pages 1000
col JOB_NAME for a30
col NEXT_RUN_DATE for a40
col REPEAT_INTERVAL for a60
col ACTUAL_START_DATE for a35
col LOG_DATE for a35
col RUN_DURATION for a15
col STATUS for a10
pro -- dba_scheduler_jobs
select job_name,repeat_interval,next_run_date from dba_scheduler_jobs where regexp_like(job_name,'&1','i');
pro -- dba_scheduler_job_run_details
select log_date,job_name,status,actual_start_date,run_duration
from dba_scheduler_job_run_details
where regexp_like(job_name,'&1','i')
order by actual_start_date asc;

$ cat createjob.sql

select sysdate from dual;
set echo on
begin
 dbms_scheduler.create_job (
  job_name => 'DIRTY_JOB',
  job_type => 'PLSQL_BLOCK',
  job_action => 'BEGIN NULL; END;',
  start_date => trunc(sysdate)+21/24,
  repeat_interval => 'FREQ=minutely; BYHOUR=21,22,23; INTERVAL=5',
  end_date => NULL,
  enabled => TRUE);
end;
/
@@checkjob dirty
pro -- FORCE RUN_JOB
set echo on
exec dbms_scheduler.RUN_JOB('DIRTY_JOB');
@@checkjob dirty
pro !!! Shoot - do not want to wail until 21:00 to know results !!!
pro -- CHANGE START_DATE REPEAT_INTERVAL
set echo on
exec dbms_scheduler.SET_ATTRIBUTE(name=>'DIRTY_JOB',attribute=>'start_date',value=>SYSDATE);
exec dbms_scheduler.SET_ATTRIBUTE(name=>'DIRTY_JOB',attribute=>'repeat_interval',value=>'FREQ=minutely;BYHOUR=12,13;INTERVAL=5');
@@checkjob dirty

$ cat dropjob.sql

set echo on
exec dbms_scheduler.DISABLE ('DIRTY_JOB',TRUE);
exec dbms_scheduler.STOP_JOB('DIRTY_JOB',TRUE);
exec dbms_scheduler.DROP_JOB('DIRTY_JOB',TRUE);
@@checkjob dirty

exec dbms_scheduler.PURGE_LOG();

What kind of jobs are you doing or using?

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 294 other followers

%d bloggers like this: