Return-Path: X-Original-To: oracle-l@orafaq.com Delivered-To: oracle-l@orafaq.com Received: from turing.freelists.org (turing.freelists.org [206.53.239.180]) by malta2546.startdedicated.com (Postfix) with ESMTPS id EC6C310031013A for ; Wed, 12 Dec 2018 16:59:07 +0100 (CET) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7597020CA9; Wed, 12 Dec 2018 10:57:15 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1544630235; bh=mizO9QAmRmXE0+dr1FNnqQk0kH2HmqSUPzM+GeJmu9I=; h=References:In-Reply-To:From:Date:Subject:To:Cc:Reply-To:List-help: List-unsubscribe:List-Id:List-subscribe:List-owner:List-post: List-archive; b=rxQ8sTlUdxEXcqNHRqVautM41yulsbe/tCuWR1EIYkujbg4M2+Ok7Q7VI1BB9QmMT IYiBUmMj3qK6ng1/B0rlBW/NNQBgKfVR838VD5OqM6L5r8F4PE2rhUuORyhwKmCPbU XPsbmrdc85uaxbkG/uO9TquFiwM+Xy3g7bDvE3F4= X-Virus-Scanned: Debian amavisd-new at turing.freelists.org Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 4d8H94fUGZzk; Wed, 12 Dec 2018 10:57:15 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4D16120B32; Wed, 12 Dec 2018 10:57:00 -0500 (EST) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org; s=turing; t=1544630232; bh=mizO9QAmRmXE0+dr1FNnqQk0kH2HmqSUPzM+GeJmu9I=; h=References:In-Reply-To:From:Date:Subject:To:Cc:Reply-To:List-help: List-unsubscribe:List-Id:List-subscribe:List-owner:List-post: List-archive; b=h7DfjzN+Dz1KPCc20gS3lAgcnbQ0wx50FS5kcpfMBUUEtJFkVRfUZAt97e/NkgXIx y/ORghm+6iRP0LepQFuvF2zeNQn5iy3/t9Y2prbpAQwmf6taYFS1EMTUSLZEWD7ut0 nghC9iVMTqKGV1WPNl+2EibNZv6LiskbPbMu6/lA= Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 12 Dec 2018 10:55:38 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5462721E2A for ; Wed, 12 Dec 2018 10:55:38 -0500 (EST) Authentication-Results: turing.freelists.org; dkim=pass reason="2048-bit key; unprotected key" header.d=pythian-com.20150623.gappssmtp.com header.i=@pythian-com.20150623.gappssmtp.com header.b=AbafOOM0; dkim-adsp=none (unprotected policy); dkim-atps=neutral Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 85i_fojhq7ru for ; Wed, 12 Dec 2018 10:55:38 -0500 (EST) Received: from mail-wr1-f66.google.com (mail-wr1-f66.google.com [209.85.221.66]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id E602A21DCC for ; Wed, 12 Dec 2018 10:55:37 -0500 (EST) Received: by mail-wr1-f66.google.com with SMTP id r10so18188347wrs.10 for ; Wed, 12 Dec 2018 07:55:37 -0800 (PST) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=lXMmC+SwtnmovRhkiN3Tn6HZIRekDNCQZOklV/3Mv2c=; b=XWK1NVLUJO3DYd2f5w35LY7Tpgl4F0Z7qIOdxMxNgKnzgNlyrDvxkaNCZg4EFpJPoS GMDkUleEjP2x4Baa6tAaSDKboCnw5vyJf8gmqRWu3qnAjVKNyfkEC0lVL9oXsVSEkwEe GOlCfbti7rF8FwdKj/5wWFVMBNAcGAD6GQ475G569vQBd/PJ4xFpfbGimY34+no/upf5 XDxFS2KO2ospdOaKIaIQEjBcbDXV5wvOiOUyBHy2aPipmkR4L82FiwpD+LeGaFg11pCb wk2I11+1PsJL5eI0Aue6ne4bnGoNj8/V+TjpL7nXBAtdAjtG1PGMwVTCsGxJrzvYTPc/ +x0A== X-Gm-Message-State: AA+aEWZcRIcLNI2FUbhSMy3frdUpU3Gx9yu5yw/hhAGNO2rA6mWnZMDj 8993NGYeZvXPkj9mcHLLgH11u22XBzzyI6ib3qd1+dxxsZRP6YfcFlYNYAgF3eRUSwsKT4waKpp IXs0KtzYlIdGU5pw3U0EanJ7F5bU01NCm X-Google-Smtp-Source: AFSGD/WP31sSq+MT3UeCXVAjRCIMpzPgFdAvUuVqR0RTMoCUYQ9zth3v61Obr1JJvp8UHzNkEuBSovJIJKhmv6lmgcA= X-Received: by 2002:adf:e509:: with SMTP id j9mr18994903wrm.76.1544630136664; Wed, 12 Dec 2018 07:55:36 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Jose Rodriguez Date: Wed, 12 Dec 2018 16:55:00 +0100 Message-ID: Subject: Re: the time zone of systimestamp is not match the user_scheduler_jobs To: joanhsieh08@gmail.com Cc: ORACLE-L Content-Type: multipart/alternative; boundary="0000000000009b1270057cd53a19" X-archive-position: 72875 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: jrodriguez2@pythian.com Precedence: normal Reply-To: jrodriguez2@pythian.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --0000000000009b1270057cd53a19 Content-Type: text/plain; charset="UTF-8" This is a simple PL/SQL code I use when I want to fit a TZ into a scheduled job. There may be better options, of course, but this one just works for me: DECLARE v_start_date timestamp with time zone; BEGIN select localtimestamp at time zone 'US/Eastern' into v_start_date from dual; DBMS_SCHEDULER.CREATE_JOB ( job_name => 'OWNER.SCHEDULED_JOB_NAME', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN NULL; END; ', start_date => v_start_date, repeat_interval => 'freq=weekly;byday=Wed;byhour=10;byminute=00', enabled => TRUE); END; / [image: Pythian] *Jose Rodriguez* | Oracle Project Engineer | [image: LinkedIn] *t* +1 613 565 8696 <+1+613+565+8696> *ext.* 1393 *m* +34 607 55 49 91 <+34+607+55+49+91> jrodriguez2@pythian.com *www.pythian.com* [image: Pythian] On Wed, 12 Dec 2018 at 16:49, Joan Hsieh 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 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 >> >> -- -- --0000000000009b1270057cd53a19 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
This is a simple PL/SQL code I use when I= want to fit a TZ into a scheduled job. There may be better options, of cou= rse, but this one just works for me:


<= div>DECLARE<= /div>
=C2=A0 v_start_date timestamp with time zone;
BEGIN
=C2=A0=C2=A0
=C2=A0 select localtimestamp at time zone 'US/Eastern'<= /span> into v_start_date from dual;

=C2=A0 DBMS_SC= HEDULER.CREATE_JOB (
=C2=A0 =C2=A0 job_name=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =3D> = 9;OWNER.SCHEDULED_JOB_NAME',
=C2=A0 =C2=A0 job_type=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =3D> 'PLSQL_BLOCK',
=C2=A0 =C2=A0 job_ac= tion=C2=A0 =C2=A0 =C2=A0 =3D> 'BEGIN NULL; END; ',
=C2=A0 =C2=A0 = start_date=C2=A0 =C2=A0 =C2=A0 =3D> v_start_date,
=C2=A0 =C2= =A0 repeat_interval =3D> 'freq=3Dweekly;byday=3DWed;byhour=3D10;byminute=3D00',
=C2=A0 =C2=A0 enabled=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=3D&g= t; TRUE);
END;
/=C2=A0


3D"Pyt=
Jose Rodriguez=C2=A0|=C2=A0Oracle Project Engineer=C2=A0|=C2=A03D"LinkedIn"=C2=A0
t=C2=A0+1 613 565 8696=C2=A0= ext.=C2=A01393=C2=A0
m=C2=A0+34 607 55 49 91= =C2=A0
jrodrig= uez2@pythian.com=C2=A0
www.pythian.com
3D"Pythian"
<= /div>


On Wed, 12 Dec 2018 at 16:49, Joan Hsieh <joanhsieh08@gmail.com> wrote:
SELECT dbtimezone FROM=
 DUAL;
DBTIMEZONE
------------------
-05:00
Thanks for the response.=C2=A0
Is it possible t= o change the scheduler_job match the dbtimezone? we can't shutdown data= base to change the timezone.

Thanks,
Joan

On Wed, Dec 12, 2018 at 9:55 AM Joan Hsieh <joanhsieh08@gmail.com> wrote:
Hi Listers,

we have some db= ms_scheduler_jobs failed due to the timestamp is not correct. it throw out = tons of errors of=C2=A0ORA-08186: invalid timestamp specified=C2=A0.=C2=A0
The last_start_date from user_sched= uler_jobs returns -4:00,
12-DEC-18 10.43.17.198623 AM -04:00=

but the systimestamp from dual returns -5:0= 0. 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


--



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