Thursday, May 10, 2012

DBMS_SCHEDULER copy job

Here is a nice dbms_scheduler feature I found the other day.
DBMS_SCHEDULER.COPY_JOB (
   old_job                IN VARCHAR2,
   new_job                IN VARCHAR2);
SQL> BEGIN
  2     DBMS_SCHEDULER.create_job (
  3        job_name          => 'JOB_NAME',
  4        job_type          => 'PLSQL_BLOCK',
  5        job_action        => 'begin NULL; end;',
  6        repeat_interval   => 'freq=daily;byhour=6,7,8,9,10,11,12,13,14,15,16,17;byminute=0,30;bysecond=0',
  7        enabled           => FALSE
  8     );
  9  END;
/ 10

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.enable('JOB_NAME')

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.copy_job('JOB_NAME','job2')

PL/SQL procedure successfully completed.

OWNER           JOB_NAME                       JOB_ACTION                               REPEAT_INTERVAL                ENABL STATE
--------------- ------------------------------ ---------------------------------------- ------------------------------ ----- ---------------
NEXT_RUN_DATE
---------------------------------------------------------------------------
OPS$fjkds JOB2                           begin NULL; end;                         freq=daily;byhour=6,7,8,9,10,1 FALSE DISABLED
                                                                                        1,12,13,14,15,16,17;byminute=0
                                                                                        ,30;bysecond=0
10-MAY-12 12.00.00.100000 PM US/EASTERN

OPS$ JOB_NAME                       begin NULL; end;                         freq=daily;byhour=6,7,8,9,10,1 TRUE  SCHEDULED
                                                                                        1,12,13,14,15,16,17;byminute=0
                                                                                        ,30;bysecond=0
10-MAY-12 12.00.00.100000 PM US/EASTERN




Neat things to note here.  The job2 is disabled, excellent.  This will come in handy, this one thinks.

No comments:

Post a Comment