Re: the time zone of systimestamp is not match the user_scheduler_jobs

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Wed, 12 Dec 2018 09:55:31 -0600
Message-ID: <CAP79kiS9wBOh=SpnBHrB2moLpeFJcVb0H0M8ZYd=WDKh0Nw38w_at_mail.gmail.com>

You can modify the start date attribute

BEGIN
  SYS.DBMS_SCHEDULER.DISABLE
    (name => '&owner.&job_name');
END;
/

BEGIN
  SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => '&owner.&job_name'
     ,attribute => 'START_DATE'
     ,value     => TO_TIMESTAMP_TZ('2018/12/13 08:00:00.000000

-05:00','yyyy/mm/dd hh24:mi:ss.ff tzr')); -- set it to a future date and specify TZ offset
END;
/

BEGIN
  SYS.DBMS_SCHEDULER.ENABLE
    (name => '&owner.&job_name');
END;
/

Also when you select from DBA_SCHEDULER* views, any TIMESTAMP field shows you YOUR timezone as those views are defined WITH TIMEZONE.

So, if you're in Central time and logged into the db from your workstation, you need to select the last_start_date, next_run_date etc AT TIME ZONE 'US/Eastern' to see the actual Eastern Time Zone start times/next run times.

Chris

On Wed, Dec 12, 2018 at 9:49 AM Joan Hsieh <joanhsieh08_at_gmail.com> wrote:

> SELECT dbtimezone FROM DUAL;
>
> DBTIMEZONE
> ------------------
> -05:00
>
> Thanks for the response.
> Is it possible to change the scheduler_job match the dbtimezone? we can't
> shutdown database to change the timezone.
>
> Thanks,
>
> Joan
>
> On Wed, Dec 12, 2018 at 9:55 AM Joan Hsieh <joanhsieh08_at_gmail.com> wrote:
>
>> Hi Listers,
>>
>> we have some dbms_scheduler_jobs failed due to the timestamp is not
>> correct. it throw out tons of errors of ORA-08186: invalid timestamp
>> specified .
>> The last_start_date from user_scheduler_jobs returns -4:00,
>> 12-DEC-18 10.43.17.198623 AM -04:00
>>
>> but the systimestamp from dual returns -5:00. what we should do to get it
>> sync.?
>>
>> SQL> select systimestamp from dual;
>>
>> SYSTIMESTAMP
>>
>> ---------------------------------------------------------------------------
>> 12-DEC-18 09.47.28.000075 AM -05:00
>>
>> Thank you so much.
>>
>> Joan
>>
>>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 12 2018 - 16:55:31 CET