Thursday 7 May 2015

Creating a Scheduler job with a specific country Time Zone

Let us see how to create a Scheduler job with a specific country Start Time with its respective time zone, Lets say i want to create a scheduler job in the oracle database where its start time should be tiem zone for country PERU because if you specify systimestamp it will take the database time zone which cab be lets say India and to create a job which has a start time of local time of country PERU will be created as below :-


BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'MY_STATS_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2015/05/05 06:00:00.000000  America/Lima','yyyy/mm/dd hh24:mi:ss.ff tzr')
      ,repeat_interval => 'FREQ=DAILY;BYHOUR=06;'
      ,end_date        => NULL
      ,job_class       => 'SOME_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => 'begin DBMS_STATS.gather_schema_stats(ownname=>''SCHEMA_NAME'',cascade => TRUE,degree=>4); end;'
      ,comments        => NULL
    );
end;
/

Please note that we used America/Lima because its a time of Lima which is the captial of PERU.

begin
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'MY_STATS_JOB'
     ,attribute => 'RESTARTABLE'
     ,value     => FALSE);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'MY_STATS_JOB'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_RUNS);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'MY_STATS_JOB'
     ,attribute => 'MAX_FAILURES');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'MY_STATS_JOB'
     ,attribute => 'MAX_RUNS');
  BEGIN
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
      ( name      => 'MY_STATS_JOB'
       ,attribute => 'STOP_ON_WINDOW_CLOSE'
       ,value     => FALSE);
  EXCEPTION
    -- could fail if program is of type EXECUTABLE...
    WHEN OTHERS THEN
      NULL;
  END;
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'MY_STATS_JOB'
     ,attribute => 'JOB_PRIORITY'
     ,value     => 3);
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE_NULL
    ( name      => 'MY_STATS_JOB'
     ,attribute => 'SCHEDULE_LIMIT');
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'MY_STATS_JOB'
     ,attribute => 'AUTO_DROP'
     ,value     => FALSE);

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'MY_STATS_JOB');
END;
/

No comments:

Post a Comment