※さらに簡単になった最新の対応方法はこちらの記事を参照してください。

はじめに

現在時刻の取得にsysdateを使用している既存のアプリケーションをAutonomous Databaseに移行する際に、Autonomous DatabaseがsysdateをUTCで返すことが問題となることがあります。

今回、DBのタイムゾーンを変更し、セッションでSYSDATE_AT_DBTIMEZONEというパラメータを有効にすることで、Autonomous Databaseのタイムゾーン設定に応じたsysdate、systimestampを返すことができるようになりました。

これにより、sysdateを使用している既存のアプリケーションをAutonomous Databaseに移行する際のアプリケーションの改修が不要になります。

注意：
こちらの内容はあくまで当方の環境で検証した結果ですので、ご利用に関しては自己責任でお願いします。
本番環境に適用する際は、必ず事前に充分なテストを実施してください。

1.Autonomous Databaseのタイムゾーンの変更

クライアントの環境変数NLS_DATE_FORMATを設定して、DATE型の表示フォーマットを'YYYY-MM-DD HH24:MI:SS'にしておきます。

[opc@work ~]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
[opc@work ~]$

Autonomous Databaseにadminユーザで接続し、Autonomous Databaseのタイムゾーンを変更します。

[opc@work admin]$ sqlplus admin/Demo#1Demo#1@usageatp_medium

SQL*Plus: Release 19.0.0.0.0 - Production on 水 2月 2 19:13:35 2022
Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

最終正常ログイン時間: 水 2月  02 2022 18:11:28 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.1.0
に接続されました。
SQL> ALTER DATABASE SET TIME_ZONE='+09:00';

データベースが変更されました。

SQL> exit
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.1.0との接続が切断されました。
[opc@work admin]$

タイムゾーンを変更したら、変更を反映するためにAutonomous Databaseを再起動します。

2.SYSDATE_AT_DBTIMEZONEの動作確認

SYSDATE_AT_DBTIMEZONEが無効な状態（デフォルト）と有効な状態での動作の違いを確認してみます。

クライアントのタイムゾーンを確認します。

[opc@work ~]$ date
2022年  2月  3日 木曜日 12:01:23 JST
[opc@work ~]$

adminユーザでAutonomous Databaseに接続します。

[opc@work ~]$ sqlplus admin/Demo#1Demo#1@usageatp_medium

SQL*Plus: Release 19.0.0.0.0 - Production on 木 2月 3 12:04:21 2022
Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

最終正常ログイン時間: 木 2月  03 2022 11:59:01 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.1.0
に接続されました。
SQL>

set time onでクライアントの現在時刻（JST）をプロンプトに表示します。

SQL> set time on
12:04:49 SQL>

sysdate、systimestampの値を表示してみます。

12:04:49 SQL> SELECT sysdate FROM dual;

SYSDATE
-------------------
2022-02-03 03:05:47

12:05:47 SQL> SELECT systimestamp FROM dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
22-02-03 03:06:07.246747 +00:00

12:06:07 SQL>

sysdate、systimestampがUTCで返されました。

次にALTER SESSIONでSYSDATE_AT_DBTIMEZONEを有効にして、同様にsysdateとsystimestampの値を確認してみます。

12:06:07 SQL> ALTER SESSION SET SYSDATE_AT_DBTIMEZONE=TRUE;

セッションが変更されました。

12:06:54 SQL> SELECT sysdate FROM dual;

SYSDATE
-------------------
2022-02-03 12:07:04

12:07:04 SQL> SELECT systimestamp FROM dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
22-02-03 12:07:08.428935 +09:00

12:07:08 SQL>

sysdate、systimestampともに日本時間（+09:00=JST）で返ってきました！

3.ログオン・トリガーを使用したSYSDATE_AT_DBTIMEZONEの有効化

SYSDATE_AT_DBTIMEZONEを有効にすることで、SYSDATE、SYSTIMESTAMPが日本時間を返すようにすることができたので、こちらをアプリケーションユーザのログオン・トリガーに実装してみます。

ここでは、「apuser」というアプリケーションユーザのログオン・トリガーでSYSDATE_AT_DBTIMEZONEを有効にしてみます。

まずは「apuer」というDBユーザを作成します。

SQL> GRANT CONNECT, RESOURCE to apuser IDENTIFIED BY Demo#1Demo#1;

権限付与が成功しました。

SQL>

apuserのログオン・トリガー「enable_sysdate_at_dbtimezone_trigger」を作成します。

SQL> CREATE OR REPLACE TRIGGER apuser.enable_sysdate_at_dbtimezone_trigger
  2  AFTER LOGON ON apuser.schema
  3  BEGIN
  4  execute immediate 'ALTER SESSION SET SYSDATE_AT_DBTIMEZONE=TRUE';    
  5  END;
  6  /

トリガーが作成されました。

SQL>

adminユーザからログアウトします。

12:07:08 SQL> exit
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.1.0との接続が切断されました。
[opc@work ~]$

apuserでDBにログインします。

[opc@work ~]$ sqlplus apuser/Demo#1Demo#1@usageatp_medium

SQL*Plus: Release 19.0.0.0.0 - Production on 木 2月 3 12:08:42 2022
Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

最終正常ログイン時間: 水 2月  02 2022 19:47:54 +09:00


Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.14.0.1.0
に接続されました。
SQL>

set time onでプロンプトにクライアントの現在時刻（タイムゾーンはAsia/Tokyo）を表示します。

SQL> set time on
12:09:08 SQL>

sysdate、systimestampの値を確認してみます。

12:09:08 SQL> SELECT sysdate FROM dual;

SYSDATE
-------------------
2022-02-03 12:09:55

12:09:55 SQL> SELECT systimestamp FROM dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
22-02-03 12:10:13.317553 +09:00

12:10:13 SQL>

ログオン・トリガーでセッションのSYSDATE_AT_DBTIMEZONEが有効になっているので、sysdate、systimestampともに、現在日時が日本時間で返ってきました。

まとめ

SYSDATE_AT_DBTIMEZONEを使用して、Autonomous Databaseでsysdate、systimestampが現在日時をJSTで返すようにすることができました。
また、アプリケーションユーザーのログオン・トリガーでSYSDATE_AT_DBTIMEZONEを有効にすることで、sysdate、systimestampを使用した既存のアプリケーションを修正することなく、Autonomous Databaseに移行できることがわかりました。

参考情報

SYSDATE_AT_DBTIMEZONE