ねら~ITエンジニア雑記

やきうのお兄ちゃんが綴るOracle Databaseメインのブログ

9iR2環境から18c環境のPDBにTTS(トランスポータブル表領域)でデータを移行してみる(Oracle Database)

表題の通り、9iR2環境から18c環境のPDBにTTS(トランスポータブル表領域)で
データを移行してみるやで彡(゚)(゚) 以下の構成で検証します。

9iR2 ⇒ (トランスポータブル表領域) ⇒ 18c(PDB)

1. プラットフォーム、キャラクタセット、ブロックサイズの確認(9iR2 and 18c)

まず制限事項をクリアするため、プラットフォーム(OS)、キャラクタセット、ブロックサイズを確認します。
※TTS の 制限事項についてはマニュアルや My Oracle Support のドキュメントもご覧下さい。

# 9iR2環境
uname -a
Linux xxxxxxxx 2.6.9-55.ELsmp #1 SMP Fri Apr 20 17:03:35 EDT 2007 i686 i686 i386 GNU/Linux
-- 9iR2環境
SET LINESIZE 170;
SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SET LINESIZE 170;
SET PAGESIZE 100;
COLUMN PARAMETER FORMAT A50;
COLUMN VALUE FORMAT A80;
SELECT * FROM NLS_DATABASE_PARAMETERS;

PARAMETER                                          VALUE
-------------------------------------------------- --------------------------------------------------------------------------------
NLS_LANGUAGE                                       AMERICAN
:
NLS_CHARACTERSET                                   JA16SJIS ★
:
NLS_NCHAR_CHARACTERSET                             AL16UTF16 ★
:

SHOW PARAMETER DB_BLOCK_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
# 18c環境
uname -a
Linux xxxxxxxx 4.1.12-112.14.10.el6uek.x86_64 #2 SMP Mon Jan 8 18:24:01 PST 2018 x86_64 x86_64 x86_64 GNU/Linux
-- 18c環境
SHOW CON_NAME;

CON_NAME
------------------------------
PDB01

SET LINESIZE 170;
SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
BANNER_FULL
-------------------------------------------------------------------------------------------
BANNER_LEGACY                                                                        CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production                    0

PARAMETER                                          VALUE
-------------------------------------------------- --------------------------------------------------------------------------------
NLS_RDBMS_VERSION                                  18.0.0.0.0
:
NLS_NCHAR_CHARACTERSET                             AL16UTF16 ★
NLS_CHARACTERSET                                   JA16SJIS ★
:

SHOW PARAMETER DB_BLOCK_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

2. 表領域を作成(9iR2) ※元々存在する表領域を移行する場合は不要

移行元の9iR2環境で表領域を作成します。
※元々存在する表領域を移行する場合は不要です彡(゚)(゚)

-- 9iR2
CREATE TABLESPACE TEST01
  DATAFILE '/u01/oracle/orcl/oradata/orcl/test01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

CREATE TABLESPACE TEST02
  DATAFILE '/u01/oracle/orcl/oradata/orcl/test02.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

表領域が作成されました。

表領域が作成されました。

3. オブジェクト作成(9iR2) ※元々存在するオブジェクトを移行する場合は不要

移行元の9iR2環境でオブジェクトを作成します。
※元々存在するオブジェクトを移行する場合は不要です彡(゚)(゚)

-- 9iR2
CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy
CREATE TABLE TBL_A (C1 NUMBER) TABLESPACE TEST01;
CREATE UNIQUE INDEX TBL_A_PK ON TBL_A(C1) TABLESPACE TEST02;
INSERT INTO TBL_A SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 1000;
COMMIT;

表が作成されました。

索引が作成されました。

1000行が作成されました。

コミットが完了しました。

4. 表領域が自己完結しているかを確認(DBMS_TTS.TRANSPORT_SET_CHECK)

表領域が自己完結しているかどうかをDBMS_TTSパッケージのTRANSPORT_SET_CHECKプロシージャと
TRANSPORT_SET_VIOLATIONSディクショナリで確認します。

下記例では、TEST02 が自己完結していない事が示されていますやね彡(゚)(゚)
TEST01表領域と一緒にトランスポートする必要があります。

# 9iR2
SET LINESIZE 100;
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TEST01', TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;

PL/SQLプロシージャが正常に完了しました。

レコードが選択されませんでした。


EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TEST02', TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;

PL/SQLプロシージャが正常に完了しました。

VIOLATIONS
----------------------------------------------------------------------------------------------------
Index AYSHIBAT.TBL_A_PK in tablespace TEST02 points to table AYSHIBAT.TBL_A in tablespace TEST01
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^★TEST02は自己完結していない。

EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TEST01,TEST02', TRUE);
SELECT * FROM TRANSPORT_SET_VIOLATIONS;

PL/SQLプロシージャが正常に完了しました。

レコードが選択されませんでした。

5. コピーする表領域を READ ONLY にする(9iR2)

移行元(9iR2)のコピー対象表領域を READ ONLY にします。

-- 9iR2
ALTER TABLESPACE TEST01 READ ONLY;
ALTER TABLESPACE TEST02 READ ONLY;

表領域が変更されました。

表領域が変更されました。

6. メタデータをエクスポート(9iR2)

移行元(9iR2)でメタデータ(定義情報)をエクスポートします。
9iR2環境なので Datapump は未実装です。従来型エクスポートを実行します。

# 9iR2
exp \'SYS/xxxxxxxx as sysdba\' TRANSPORT_TABLESPACE=y TABLESPACES=(TEST01,TEST02) FILE=test_meta.dmp LOG=exp_test_meta.log

Export: Release 9.2.0.6.0 - Production on 火 Sep 25 14:26:05 2018

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

接続先: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
JA16SJISキャラクタ・セットおよびAL16UTF16 NCHARキャラクタ・セットでエクスポートが終了しました
注意: 表データ()はエクスポートされません。
トランスポータブル表領域メタデータをエクスポートします...
表領域TEST01 ...
. クラスタ定義をエクスポート中
. 表定義をエクスポート中
. 表                          TBL_Aをエクスポート中
表領域TEST02 ...
. クラスタ定義をエクスポート中
. 表定義をエクスポート中
. 参照整合性制約をエクスポート中
. トリガーをエクスポート中
. トランスポータブル表領域メタデータのエクスポートが終了しました。
エクスポートは警告なしで正常終了しました。

7. データファイルとメタデータdmpを移行先に転送(9iR2 ⇒ 18c)

scp等でデータファイルとメタデータdmpを移行先(18c)に転送します。 下記はデータファイルとメタデータを移行した後の結果です。

# 18c環境・転送後
$ pwd
/u01/app/oracle/oradata/SJISDB/7676F6B5B0D33571E0534656860A3EB5/datafile/
$ ls -la
total 22384272
drwxr-x--- 2 oracle oinstall       4096 Sep 25 06:00 .
drwxr-x--- 3 oracle oinstall       4096 Sep 22 13:45 ..
-rw-r----- 1 oracle oinstall  408952832 Sep 25 05:57 o1_mf_sysaux_ftdkv25s_.dbf
-rw-r----- 1 oracle oinstall  283123712 Sep 25 05:57 o1_mf_system_ftdkv25l_.dbf
-rw-r----- 1 oracle oinstall   65019904 Sep 24 01:15 o1_mf_temp_ftdkv25t_.dbf
-rw-r----- 1 oracle oinstall  104865792 Sep 25 05:57 o1_mf_undotbs1_ftdkv25t_.dbf
-rw-r----- 1 oracle oinstall  104865792 Sep 25 05:51 o1_mf_users_ftdplvyt_.dbf
-rw-r--r-- 1 oracle oinstall  104865792 Sep 25  2018 test01.dbf ★データファイル
-rw-r--r-- 1 oracle oinstall  104865792 Sep 25  2018 test02.dbf ★データファイル
-rw-r--r-- 1 oracle oinstall      16384 Sep 25  2018 test_meta.dmp ★メタデータdmp

8. 移行元の表領域を READ WRITE に戻す(9iR2)

移行元(9iR2)の表領域を READ WRITE に戻します。

-- 9iR2
ALTER TABLESPACE TEST01 READ WRITE;
ALTER TABLESPACE TEST02 READ WRITE;

表領域が変更されました。

表領域が変更されました。

9. メタデータを対象のPDBにインポート(18c)

メタデータを対象のPDBにインポートします。IMP-00403 の警告が出ますが、
下記マニュアルの記述を元に、必要に応じて生成されたSQLを手動実行して下さい。
※生成されたSQLが0バイトの場合は実行不要です。

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-original-import-utility.html#GUID-83111A9F-A2FC-4DC0-83BB-EDA398BBD4E0
If the import job is run by a user with the DBA role, and not all objects can be re-created by user SYSTEM,
then the following warning message is written to the log file:
IMP-00403:
Warning: This import generated a separate SQL file "logfilename_sys" which contains DDL that failed due to a privilege issue.
The SQL file that is generated contains the failed DDL of objects that could not be re-created by user SYSTEM.
To re-create those objects, you must manually execute the failed DDL after the import finishes.

# 18c環境
imp \'SYS@xxxxxxxx as sysdba\' transport_tablespace=y \
datafiles='/u01/app/oracle/oradata/SJISDB/7676F6B5B0D33571E0534656860A3EB5/datafile/test01.dbf\
,/u01/app/oracle/oradata/SJISDB/7676F6B5B0D33571E0534656860A3EB5/datafile/test02.dbf'\
 file=test_meta.dmp log=imp_test_meta.log

Import: Release 18.0.0.0.0 - Production on Tue Sep 25 06:03:32 2018
Version 18.2.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.2.0.0.0

Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses JA16SJIS character set (possible charset conversion)
export client uses JA16SJIS character set (possible charset conversion)
IMP-00403:

Warning: This import generated a separate SQL file "imp_test_meta_sys.sql" which contains DDL that failed due to a privilege issue.

. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing AYSHIBAT's objects into AYSHIBAT
. . importing table                        "TBL_A"
Import terminated successfully with warnings.

$ ls -la imp_test_meta_sys.sql
-rw-r--r-- 1 oracle oinstall 0 Sep 25 06:03 imp_test_meta_sys.sql ★0バイト
$ cat imp_test_meta_sys.sql
$

10. インポート結果の確認(18c)

移行先(18c)のPDBに接続してインポート結果を確認します。

# 18c
CONNECT AYSHIBAT/xxxxxxxx@yyyyyyyy

SHOW CON_NAME;

CON_NAME
------------------------------
PDB01

SELECT COUNT(*) FROM TBL_A;

  COUNT(*)
----------
      1000

11. 表領域の READ WRITE化(18c)

移行先(18c)のPDBの表領域を READ WRITE にします。

-- 18c
ALTER TABLESPACE TEST01 READ ONLY;
ALTER TABLESPACE TEST02 READ ONLY;

表領域が変更されました。

表領域が変更されました。

12. まとめ

9iR2環境の表領域を18c環境にTTSで移行できたで!彡(゚)(゚)

両バージョンの年月差は実に16年(2002年 vs 2018年)、
バージョンは2倍、威力は100倍、16年の時を経て移行できるTTS最高や!彡(^)(^)

13. 参考情報

マニュアルとサポートドキュメントも参照するんやで彡(゚)(゚)

Oracle9i Database Release 2 プラットフォーム共通 ドキュメント
https://www.oracle.com/technetwork/jp/content/general-082543-ja.html#db
 
Oracle9i データベース管理者ガイド リリース2(9.2)
部品番号:J06242-01
http://otndnld.oracle.co.jp/document/oracle9i/920/generic/server/J06242-01.pdf
データベース間での表領域のトランスポート(11-34)
 
MOSドキュメント:トランスポータブル表領域移行手順(KROWN:27536) (ドキュメントID 1709003.1)
 
MOSドキュメント:IMP-00403 "Warning: This import generated a separate SQL file" Using 12.2 imp Command (ドキュメントID 2298963.1)
 
MOSドキュメント:異なるバージョンのデータベース間でExport/Importを実行する方法(KROWN:45271) (ドキュメントID 1715793.1)

セミナー「デモとディスカッションで体験するOracle DBトラブル対応・2018/8/22(水)」の開催報告と資料共有

昨日 2018/8/22(水) に、下記セミナーを開催させて頂きました。
ご参加いただいた方々、誠に有難うございました!彡(^)(^)

デモとディスカッションで体験するOracle DBトラブル対応
https://study-oracle-technology-workshop.connpass.com/event/95420/
 
デモとディスカッションで体験するOracle DBトラブル対応
https://techplay.jp/event/685958
 
f:id:gonsuke777:20180822180906j:plain  
f:id:gonsuke777:20180822181247j:plain

反省点は色々……ITエンジニアと言えど、今時はNote PCを持ち歩く人は
レアですかね。参加者の方々にデモ環境に確実に繋いでもらうなら、
ほぼ全員が持っているスマホ前提でデモを作った方がエエですやね彡(゚)(゚)

次に似たような機会が有れば、Oracle APEX辺りでちょっとした作り込みかなぁ……彡(-)(-)

セミナー資料はSlideshareに公開しています。
ご参考/ご意見下さいませ(`・ω・)ゞ

(Slideshare)デモとディスカッションで体験するOracle DBトラブル対応
https://www.slideshare.net/shibataayumu/oracle-db-111087832
f:id:gonsuke777:20180828125529j:plain

CLOB型の(文字数ではなく)バイト数を返却するファンクションを自作(Oracle Database)

昔の記事(https://gonsuke777.hatenablog.com/entry/20150129/1422535354)が間違っていたので、訂正を兼ねた記事を作成彡(゚)(゚)

CLOB型の(文字数ではなく)バイト数を返却するファンクションを自作してみます。

CONVERTTOBLOBプロシージャでCLOB型をBLOB型に変換して、 そのバイト数をDBMS_LOB.GETLENGTHを変換するやで彡(゚)(゚)

1. ファイルのバイト数と中身

下記のUTF-8エンコードのテキストファイル(2057850バイト)をOracle Databaseにロードして検証します。ASCII文字とひらがなと4バイトの文字を混ぜています。

ls -la clob_test2.txt
-rw-r--r--. 1 oracle oinstall 2057850 Aug 19 22:29 clob_test2.txt

cat clob_test2.txt

aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾…
aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾…
:
aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾…
aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾aaabbbcccあああいいいううう𡚴𡸴𣇄𣗄𣜿𣝣𣳾…

テキストファイルのロードは下記記事を参照彡(゚)(゚)

DBMS_LOB.LoadClobFromFileプロシージャを使って
テキストファイルを Oracle Database の CLOB型列 に ロードする。
https://gonsuke777.hatenablog.com/entry/20160815/1471259132

2. 自作ファンクションFNC_CLOB_LENBの作成

自作ファンクションの作成、前述した通りCONVERTTOBLOBプロシージャで CLOB型をBLOB型に変換して、そのバイト数をDBMS_LOB.GETLENGTHを変換してます。

CREATE OR REPLACE FUNCTION FNC_CLOB_LENB(
  clob_in IN CLOB
)
RETURN NUMBER
IS
  blob_tmp BLOB;
  i_d_offset INTEGER;
  i_s_offset INTEGER;
  i_l_contxt INTEGER;
  i_warning  INTEGER;
BEGIN
  -- Initialize
  DBMS_LOB.CREATETEMPORARY(blob_tmp, FALSE, DBMS_LOB.CALL);
  i_d_offset := 1;
  i_s_offset := 1;
  i_l_contxt := 0;
  -- CONVERT CLOB to BLOB
  DBMS_LOB.CONVERTTOBLOB(
      blob_tmp                   -- dest_lob
    , clob_in                    -- src_blob
    , DBMS_LOB.LOBMAXSIZE        -- amount
    , i_d_offset                 -- dest_offset
    , i_s_offset                 -- src_offset
    , NLS_CHARSET_ID('AL32UTF8') -- blob_csid
    , i_l_contxt                 -- lang_context
    , i_warning                  -- warning
  );
  RETURN DBMS_LOB.GETLENGTH(blob_tmp);
END;
/

Function created.

3. 検証結果

結果は下記の通り、自作ファンクションの方は ls の結果と一致してるやで!彡(^)(^)

SELECT DBMS_LOB.GETLENGTH(C2), FNC_CLOB_LENB(C2) FROM TEST_CLOB;

DBMS_LOB.GETLENGTH(C2) FNC_CLOB_LENB(C2)
---------------------- -----------------
               1033498           2057850

4. 参考

以下のマニュアルと記事を参考にしました彡(゚)(゚)

Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 18c
93 DBMS_LOB
https://docs.oracle.com/cd/E96517_01/arpls/DBMS_LOB.html#GUID-A35DE03B-41A6-4E55-8CDE-77737FED9306
 
oracle CLOBの項目のバイト数を計算するテクニック
https://qiita.com/wagase/items/39c29d3720901e6ef331
 
Oracle Database SQL言語リファレンス 18c
NLS_CHARSET_ID
https://docs.oracle.com/cd/E96517_01/sqlrf/NLS_CHARSET_ID.html#GUID-733B03A0-CD66-4645-A323-401A176499E3

LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP の各組み込み関数の挙動を検証してみる。(Oracle Database)

ワイの中で今サマータイムがアツい彡(゚)(゚) サマータイムに絡んで、Oracle Database の組み込み関数の LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP の挙動を検証してみる。

1. Case1. OSタイムゾーンが日本(JST)の状態で検証

まずOSのタイムゾーンが日本(JST)の状態で検証彡(゚)(゚)

date
Thu Aug 16 14:40:09 JST 2018
-- Initialize
SET LINESIZE 300;
SET PAGESIZE 100;
COLUMN SESSIONTIMEZONE   FORMAT A15;
COLUMN LOCALTIMESTAMP    FORMAT A30;
COLUMN CURRENT_TIMESTAMP FORMAT A42;
COLUMN SYSTIMESTAMP      FORMAT A42;
ALTER SESSION SET NLS_TIMESTAMP_FORMAT    = 'YYYY/MM/DD HH24:MI:SS.FF';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY/MM/DD HH24:MI:SS.FF TZR TZD';

ALTER SESSION SET TIME_ZONE = 'UCT';
SELECT SESSIONTIMEZONE, LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = 'Asia/Tokyo';
SELECT SESSIONTIMEZONE, LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = 'US/Pacific';
SELECT SESSIONTIMEZONE, LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP FROM DUAL;

SESSIONTIMEZONE LOCALTIMESTAMP                 CURRENT_TIMESTAMP                          SYSTIMESTAMP
--------------- ------------------------------ ------------------------------------------ ------------------------------------------
UCT             2018/08/16 05:43:35.927468     2018/08/16 05:43:35.927468 UCT UCT         2018/08/16 14:43:35.927370 +09:00

SESSIONTIMEZONE LOCALTIMESTAMP                 CURRENT_TIMESTAMP                          SYSTIMESTAMP
--------------- ------------------------------ ------------------------------------------ ------------------------------------------
Asia/Tokyo      2018/08/16 14:43:35.973241     2018/08/16 14:43:35.973241 ASIA/TOKYO JST  2018/08/16 14:43:35.973227 +09:00

SESSIONTIMEZONE LOCALTIMESTAMP                 CURRENT_TIMESTAMP                          SYSTIMESTAMP
--------------- ------------------------------ ------------------------------------------ ------------------------------------------
US/Pacific      2018/08/15 22:43:36.026801     2018/08/15 22:43:36.026801 US/PACIFIC PDT  2018/08/16 14:43:36.026789 +09:00

LOCALTIMESTAMP と CURRENT_TIMESTAMP は SESSION の TIME_ZONE の影響を受けてるけど、SYSTIMESTAMPは無影響やね彡(゚)(゚)。

2. Case2. OSタイムゾーンをロサンゼルスに変更して検証

次にOSタイムゾーンをロサンゼルスに変更して検証してみる。 ロスは2018/8/16現在、太平洋夏時間(PDT)やね彡(゚)(゚)

cp -p /usr/share/zoneinfo/America/Los_Angeles /etc/localtime
date
Thu Aug 16 23:04:25 PDT 2018
-- Initialize
SET LINESIZE 300;
SET PAGESIZE 100;
COLUMN SESSIONTIMEZONE   FORMAT A15;
COLUMN LOCALTIMESTAMP    FORMAT A30;
COLUMN CURRENT_TIMESTAMP FORMAT A42;
COLUMN SYSTIMESTAMP      FORMAT A42;
ALTER SESSION SET NLS_TIMESTAMP_FORMAT    = 'YYYY/MM/DD HH24:MI:SS.FF';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY/MM/DD HH24:MI:SS.FF TZR TZD';

ALTER SESSION SET TIME_ZONE = 'UCT';
SELECT SESSIONTIMEZONE, LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = 'Asia/Tokyo';
SELECT SESSIONTIMEZONE, LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP FROM DUAL;

ALTER SESSION SET TIME_ZONE = 'US/Pacific';
SELECT SESSIONTIMEZONE, LOCALTIMESTAMP, CURRENT_TIMESTAMP, SYSTIMESTAMP FROM DUAL;

SESSIONTIMEZONE LOCALTIMESTAMP                 CURRENT_TIMESTAMP                          SYSTIMESTAMP
--------------- ------------------------------ ------------------------------------------ ------------------------------------------
UCT             2018/08/16 05:45:19.484222     2018/08/16 05:45:19.484222 UCT UCT         2018/08/15 22:45:19.484217 -07:00

SESSIONTIMEZONE LOCALTIMESTAMP                 CURRENT_TIMESTAMP                          SYSTIMESTAMP
--------------- ------------------------------ ------------------------------------------ ------------------------------------------
Asia/Tokyo      2018/08/16 14:45:19.526919     2018/08/16 14:45:19.526919 ASIA/TOKYO JST  2018/08/15 22:45:19.526914 -07:00

SESSIONTIMEZONE LOCALTIMESTAMP                 CURRENT_TIMESTAMP                          SYSTIMESTAMP
--------------- ------------------------------ ------------------------------------------ ------------------------------------------
US/Pacific      2018/08/15 22:45:19.572191     2018/08/15 22:45:19.572191 US/PACIFIC PDT  2018/08/15 22:45:19.572185 -07:00

LOCALTIMESTAMP と CURRENT_TIMESTAMP は Case 1 と同じ動作やけど、SYSTIMESTAMPの値が変わってるやね彡(゚)(゚)

3. まとめ

上記の挙動をまとめると、以下の通りとなります。

組み込み関数 データ型 DB SESSIONのTIME_ZONE値 OSのtimezone
LOCALTIMESTAMP TIMESTAMP 影響する 影響しない
CURRENT_TIMESTAMP TIMESTAMP WITH TIME ZONE 影響する 影響しない
SYSTIMESTAMP TIMESTAMP WITH TIME ZONE 影響しない 影響する

「OSのtimezone」と「DB SESSIONのTIME_ZONE値」を一致させられる環境なら、 各組み込み関数で取れてくる値に違いは無いので影響はほぼ無いと思うんやけど、 それが異なるケースではその違いを意識して実装する必要が有りそうやね彡(゚)(゚)

SYSTIMESTAMPの返り値をAT TIME ZONE書式で変換する、SESSIONTIMEZONEを任意のTIME_ZONEに変更してCURRENT_TIMESTAMPを使う、など

SELECT SYSTIMESTAMP, SYSTIMESTAMP AT TIME ZONE 'Japan' FROM DUAL;

SYSTIMESTAMP                               SYSTIMESTAMPATTIMEZONE'JAPAN'
------------------------------------------ ---------------------------------------------------------------------------
2018/08/16 01:14:47.217209 -07:00          2018/08/16 17:14:47.217209 JAPAN JST
ALTER SESSION SET TIME_ZONE = 'Japan';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

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

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------------
Japan           2018/08/16 17:15:18.966717 JAPAN JST

4. マニュアル

マニュアルも参考や!彡(゚)(゚)

LOCALTIMESTAMP
https://docs.oracle.com/cd/E96517_01/sqlrf/LOCALTIMESTAMP.html#GUID-3C3D1F29-5F53-41F2-B2D6-A3767DFB22CA
LOCALTIMESTAMPは、セッションのタイムゾーンの現在の日付および時刻をTIMESTAMPデータ型の値で戻します。
 
CURRENT_TIMESTAMP
https://docs.oracle.com/cd/E96517_01/sqlrf/CURRENT_TIMESTAMP.html#GUID-CBD42B84-869D-45C7-9FFC-001DD7712097
CURRENT_TIMESTAMPは、セッション・タイムゾーンの現在の日付および時刻をTIMESTAMP WITH TIME ZONEデータ型の値で戻します。
 
SYSTIMESTAMP
https://docs.oracle.com/cd/E96517_01/sqlrf/SYSTIMESTAMP.html#GUID-FCED18CE-A875-4D5D-9178-3DE4FA956516
SYSTIMESTAMPは、データベースが存在するシステムの、秒の小数部とタイムゾーンを含む日付を戻します。戻り値の型は、TIMESTAMP WITH TIME ZONEです。
 
ALTER SESSION
https://docs.oracle.com/cd/E96517_01/sqlrf/ALTER-SESSION.html#GUID-27186B28-7EFC-4998-B1ED-2B905CC0211B
TIME_ZONEパラメータには、現行のSQLセッションのデフォルトのローカル・タイムゾーン・オフセットまたは地域名を指定します。

サマータイム(夏時間, Daylight Saving)を理解するためにTIMESTAMP WITH TIME ZONE型と戯れてみる。(Oracle Database)

サマータイムが話題ですやね彡(゚)(゚) ワイら日本人には馴染みの薄いサマータイムですが、これを理解するために Oracle Database のTIMESTAMP WITH TIME ZONE型と戯れてみます。

1. TZR書式/TZD書式に使用可能な文字列を確認して、サマータイムを使用するタイムゾーンを選択

TZR書式/TZD書式に使用可能な文字列を確認します。V$TIMEZONE_NAMESビューから確認可能です。

-- TZR書式/TZD書式に使用可能な文字列を確認
SET LINESIZE 300;
SET PAGESIZE 1000;
SELECT * FROM V$TIMEZONE_NAMES;

TZNAME                             TZABBREV        CON_ID
---------------------------------- ----------- ----------
Africa/Abidjan                     LMT                  0
Africa/Abidjan                     GMT                  0
Africa/Accra                       LMT                  0
Africa/Accra                       GMT                  0
Africa/Accra                       GHST                 0
:
US/Mountain                        MPT                  0
US/Pacific ★                      LMT                  0
US/Pacific ★                      PST ★               0
US/Pacific ★                      PDT ★               0
US/Pacific ★                      PWT                  0
US/Pacific ★                      PPT                  0
US/Pacific-New                     LMT                  0

本エントリでは、サマータイムを使用するタイムゾーンのTZR書式として'US/Pacific'、
TZD書式としてPSTとPDTを使ってを検証しますやで彡(゚)(゚)

2. サマータイム開始近辺の日付を生成

サマータイム開始近辺の日付を生成してみます。UTCと比較したいので【1.UTC(TZR)】【2.US/Pacific(TZR)】【3.US/Pacific(TZD)】【4.US/Pacific(TZR)をDATE型にキャスト】の4種類の値を生成してみます。

-- 日付書式の設定
ALTER SESSION SET NLS_DATE_FORMAT         = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT    = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZR';

-- 日付リテラルを格納する変数
VAR v_ts_char VARCHAR2(100);

-- 表示設定
SET LINESIZE 200;
SET PAGESIZE 100;
COLUMN UTC_TZR         FORMAT A26;
COLUMN US_Pacific_TZR  FORMAT A36;
COLUMN US_Pacific_TZD  FORMAT A28;
COLUMN US_Pacific_DATE FORMAT A20;

-- 日付リテラル代入(サマータイム開始近辺)
EXEC :v_ts_char := '2018-03-11 00:00:00';

-- 日付生成
WITH TS_TMP AS (
   SELECT TO_TIMESTAMP_TZ(TO_TIMESTAMP(:v_ts_char) + (LEVEL-1)/48 || ' UTC')                           AS UTC_TZR
        , TO_TIMESTAMP_TZ(TO_TIMESTAMP(:v_ts_char) + (LEVEL-1)/48 || ' UTC') AT TIME ZONE 'US/Pacific' AS US_Pacific_TZR
     FROM DUAL
  CONNECT BY LEVEL <= 30
)
SELECT TS_TMP.*
     , TO_CHAR(TS_TMP.US_Pacific_TZR, 'YYYY-MM-DD HH24:MI:SS TZD') AS US_Pacific_TZD
     , CAST(TS_TMP.US_Pacific_TZR AS DATE)                         AS US_Pacific_DATE
  FROM TS_TMP;

UTC_TZR                    US_PACIFIC_TZR                       US_PACIFIC_TZD               US_PACIFIC_DATE
-------------------------- ------------------------------------ ---------------------------- --------------------
2018-03-11 00:00:00 UTC    2018-03-10 16:00:00 US/PACIFIC       2018-03-10 16:00:00 PST      2018-03-10 16:00:00
2018-03-11 00:30:00 UTC    2018-03-10 16:30:00 US/PACIFIC       2018-03-10 16:30:00 PST      2018-03-10 16:30:00
:
2018-03-11 08:30:00 UTC    2018-03-11 00:30:00 US/PACIFIC       2018-03-11 00:30:00 PST      2018-03-11 00:30:00
2018-03-11 09:00:00 UTC    2018-03-11 01:00:00 US/PACIFIC       2018-03-11 01:00:00 PST      2018-03-11 01:00:00
2018-03-11 09:30:00 UTC    2018-03-11 01:30:00 US/PACIFIC ★    2018-03-11 01:30:00 PST ★   2018-03-11 01:30:002018-03-11 10:00:00 UTC    2018-03-11 03:00:00 US/PACIFIC ★    2018-03-11 03:00:00 PDT ★   2018-03-11 03:00:002018-03-11 10:30:00 UTC    2018-03-11 03:30:00 US/PACIFIC       2018-03-11 03:30:00 PDT      2018-03-11 03:30:00
2018-03-11 11:00:00 UTC    2018-03-11 04:00:00 US/PACIFIC       2018-03-11 04:00:00 PDT      2018-03-11 04:00:00
:

UTC の '2018-03-11 10:00:00 UTC' のタイミングで、US/Pacificの日付については1時間進んでるのが解りますやね彡(゚)(゚)

3. サマータイム終了近辺の日付を生成

同様にサマータイム終了近辺の日付を生成してみます。

-- 日付リテラル代入(サマータイム終了近辺)
EXEC :v_ts_char := '2018-11-04 00:00:00';

-- 日付生成
WITH TS_TMP AS (
   SELECT TO_TIMESTAMP_TZ(TO_TIMESTAMP(:v_ts_char) + (LEVEL-1)/48 || ' UTC')                           AS UTC_TZR
        , TO_TIMESTAMP_TZ(TO_TIMESTAMP(:v_ts_char) + (LEVEL-1)/48 || ' UTC') AT TIME ZONE 'US/Pacific' AS US_Pacific_TZR
     FROM DUAL
  CONNECT BY LEVEL <= 30
)
SELECT TS_TMP.*
     , TO_CHAR(TS_TMP.US_Pacific_TZR, 'YYYY-MM-DD HH24:MI:SS TZD') AS US_Pacific_TZD
     , CAST(TS_TMP.US_Pacific_TZR AS DATE)                         AS US_Pacific_DATE
  FROM TS_TMP;

UTC_TZR                    US_PACIFIC_TZR                       US_PACIFIC_TZD               US_PACIFIC_DATE
-------------------------- ------------------------------------ ---------------------------- --------------------
2018-11-04 00:00:00 UTC    2018-11-03 17:00:00 US/PACIFIC       2018-11-03 17:00:00 PDT      2018-11-03 17:00:00
2018-11-04 00:30:00 UTC    2018-11-03 17:30:00 US/PACIFIC       2018-11-03 17:30:00 PDT      2018-11-03 17:30:00
:
2018-11-04 07:30:00 UTC    2018-11-04 00:30:00 US/PACIFIC       2018-11-04 00:30:00 PDT      2018-11-04 00:30:00
2018-11-04 08:00:00 UTC    2018-11-04 01:00:00 US/PACIFIC ★    2018-11-04 01:00:00 PDT ★   2018-11-04 01:00:002018-11-04 08:30:00 UTC    2018-11-04 01:30:00 US/PACIFIC ★    2018-11-04 01:30:00 PDT ★   2018-11-04 01:30:002018-11-04 09:00:00 UTC    2018-11-04 01:00:00 US/PACIFIC ★    2018-11-04 01:00:00 PST ★   2018-11-04 01:00:002018-11-04 09:30:00 UTC    2018-11-04 01:30:00 US/PACIFIC ★    2018-11-04 01:30:00 PST ★   2018-11-04 01:30:002018-11-04 10:00:00 UTC    2018-11-04 02:00:00 US/PACIFIC       2018-11-04 02:00:00 PST      2018-11-04 02:00:00
:

UTC の '2018-11-04 09:00:00 UTC'から1時間については、US/Pacificの日付で同じ時間が現れているのが解りますやね彡(゚)(゚)

4. サマータイム終了日付のデータを、現地時間の時刻で絞り込むとどうなるか?

下記にUS/Pacificの現地時間で2018/11/04の日付のデータを抽出した結果を出してみる。

-- 日付リテラル代入(サマータイム終了近辺)
EXEC :v_ts_char := '2018-11-03 00:00:00';

-- 日付生成
WITH TS_TMP AS (
   SELECT TO_TIMESTAMP_TZ(TO_TIMESTAMP(:v_ts_char) + (LEVEL-1)/48 || ' UTC')                           AS UTC_TZR
        , TO_TIMESTAMP_TZ(TO_TIMESTAMP(:v_ts_char) + (LEVEL-1)/48 || ' UTC') AT TIME ZONE 'US/Pacific' AS US_Pacific_TZR
     FROM DUAL
  CONNECT BY LEVEL <= 200
)
SELECT TS_TMP.*
     , TO_CHAR(TS_TMP.US_Pacific_TZR, 'YYYY-MM-DD HH24:MI:SS TZD') AS US_Pacific_TZD
     , CAST(TS_TMP.US_Pacific_TZR AS DATE)                         AS US_Pacific_DATE
  FROM TS_TMP
 WHERE UTC_TZR >= TO_TIMESTAMP_TZ('2018-11-04 00:00:00 US/Pacific', 'YYYY-MM-DD HH24:MI:SS TZR') AT TIME ZONE 'UTC'
   AND UTC_TZR <  TO_TIMESTAMP_TZ('2018-11-05 00:00:00 US/Pacific', 'YYYY-MM-DD HH24:MI:SS TZR') AT TIME ZONE 'UTC';

UTC_TZR                    US_PACIFIC_TZR                       US_PACIFIC_TZD               US_PACIFIC_DATE
-------------------------- ------------------------------------ ---------------------------- --------------------
2018-11-04 07:00:00 UTC    2018-11-04 00:00:00 US/PACIFIC       2018-11-04 00:00:00 PDT      2018-11-04 00:00:00
2018-11-04 07:30:00 UTC    2018-11-04 00:30:00 US/PACIFIC       2018-11-04 00:30:00 PDT      2018-11-04 00:30:00
2018-11-04 08:00:00 UTC    2018-11-04 01:00:00 US/PACIFIC ★    2018-11-04 01:00:00 PDT ★   2018-11-04 01:00:00
2018-11-04 08:30:00 UTC    2018-11-04 01:30:00 US/PACIFIC ★    2018-11-04 01:30:00 PDT ★   2018-11-04 01:30:00
2018-11-04 09:00:00 UTC    2018-11-04 01:00:00 US/PACIFIC ★    2018-11-04 01:00:00 PST ★   2018-11-04 01:00:00
2018-11-04 09:30:00 UTC    2018-11-04 01:30:00 US/PACIFIC ★    2018-11-04 01:30:00 PST ★   2018-11-04 01:30:00
2018-11-04 10:00:00 UTC    2018-11-04 02:00:00 US/PACIFIC       2018-11-04 02:00:00 PST      2018-11-04 02:00:002018-11-05 07:00:00 UTC    2018-11-04 23:00:00 US/PACIFIC       2018-11-04 23:00:00 PST      2018-11-04 23:00:00
2018-11-05 07:30:00 UTC    2018-11-04 23:30:00 US/PACIFIC       2018-11-04 23:30:00 PST      2018-11-04 23:30:00

50 rows selected.

うーん、UTC違いの同じ時刻が2重に取れてくるやね彡(゚)(゚)

5. 日本式サマータイムに関する所感

Oracle Database としてのサマータイムの挙動はざっくりとは理解したので、
今話題の日本式サマータイムでの対応を考察してみる。

日本式サマータイムOracle Database できっちり対応しようとすると

  • 日付データについてはTIMESTAMP WITH TIME ZONE型の列に
    UTC形式 または TZR/TZD併用形式で記述した時刻を変換して格納
    ※最初は「UTC形式で格納」と表記したが「TZR/TZD併用形式」でも夏時間境界を判別可能なため。
  • 該当列を表示する時には【AT TIME ZONE 'Japan'】を付与して日本時間に変換
  • 絞り込み条件は日本時刻をUTC形式に変換して、UTC形式の列に絞込みを掛ける。 ただしサマータイム開始日や終了日については、1日の長さが22時間 or 26時間で変動するため、 24時間決め打ちでロジックを組むことはできない。

てな感じになるのかと彡(゚)(゚)

Oracle Database はサマータイムに対応しているものの、日付データを
TIMESTAMP WITH TIME ZONE型で夏時間を意識して格納しているシステムは、
少なくとも日本企業がスクラッチで組んだシステムでは相当レアなんじゃなかろうかと。

大半のシステムはDATE型やTIMESTAMP型で、あるいは文字列型日付データを保持していると
認識していて、これをデータの持ち方を変えて、更にプログラムまで変更して対応するのは、
まあ事実上不可能なんやろうなぁ……と考えざるを得ない彡(-)(-)

6. マニュアル、ほか追加検証

マニュアルは下記の通り彡(゚)(゚)

Oracle Database SQL言語リファレンス
18cバージョン 18.1 E93948-01
日時書式モデル
https://docs.oracle.com/cd/E96517_01/sqlrf/Format-Models.html#GUID-49B32A81-0904-433E-B7FE-51606672183A
 
TIMESTAMP WITH TIME ZONEデータ型
https://docs.oracle.com/cd/E96517_01/sqlrf/Data-Types.html#GUID-BE23545B-469A-4A57-8D13-505F2F5DB706
 
TO_TIMESTAMP_TZ
https://docs.oracle.com/cd/E96517_01/sqlrf/TO_TIMESTAMP_TZ.html#GUID-3999303B-89CA-4AA3-9817-458F36ADC9DC
 
CAST
https://docs.oracle.com/cd/E96517_01/sqlrf/CAST.html#GUID-5A70235E-1209-4281-8521-B94497AAEF75
 
Oracle Database
Databaseグローバリゼーション・サポート・ガイド 18c E94646-01
4.12 夏時間のサポート
https://docs.oracle.com/cd/E96517_01/nlspg/datetime-data-types-and-time-zone-support.html#GUID-E5171DEF-74D7-482A-B4E9-1EE3403E18BC
Oracle Databaseは、指定のタイム・ゾーンで夏時間が有効かどうかを自動的に判別し、
対応するローカル時間を返します。Oracle Databaseで夏時間が特定のタイム・ゾーンで有効かどうかを判別するには、
通常、日時値で十分です。夏時間の開始期間または終了期間は境界事例です。たとえば、米国東部では、
夏時間が有効になる時点で、時刻が午前1時59分59秒から午前3時0分0秒に変わります。
午前2時0分0秒~午前2時59分59秒の時間隔は存在しません。その時間隔に該当する値は無効です。
夏時間の終了時には、時刻が午前2時0分0秒から午前1時0分1秒に変わります。
午前1時0分1秒~午前2時0分0秒の時間隔が繰り返されます。この時間隔に含まれる値は、2回発生するため不明確となります。
 
このような境界事例を解決するために、Oracle DatabaseではTZRおよびTZD書式要素を使用します。
TZRは、日時入力文字列でのタイム・ゾーン・リージョンを表します。たとえば、'Australia/North'、'UTC'および'Singapore'などです。
TZDは、夏時間情報を含むタイム・ゾーン・リージョンの略称を表します。たとえば、米国太平洋標準時間の場合は'PST'、
米国太平洋夏時間の場合は'PDT'です。TZRおよびTZD書式要素に対して有効な値のリストを表示するには、
V$TIMEZONE_NAMES動的パフォーマンス・ビューのTZNAME列とTZABBREV列を問い合せます。
 
4.2.1.3 TIMESTAMP WITH TIME ZONEデータ型
https://docs.oracle.com/cd/E96517_01/nlspg/datetime-data-types-and-time-zone-support.html#GUID-5BC5D2C1-6506-49BE-8177-F743A46FDC09
標準時間から夏時間への切替時に境界を明確にするには、TZR書式要素および対応するTZD書式要素の両方を使用します。
TZD書式要素は、夏時間情報を含むタイム・ゾーン・リージョンの略称です。たとえば、米国太平洋標準時間の場合はPST、
米国太平洋夏時間の場合はPDTです。次のように指定すると、確実に夏時間の値が戻されます。

上記マニュアルの記述に基づいて、夏時間終了時の現地時間について 繰り返される現地時刻をUTCに変換した結果を下記に示します。

-- TZR書式の時間をUTCに変換
SELECT TIMESTAMP '2018-11-04 01:30:00 US/Pacific'     AT TIME ZONE 'UTC' AS TZR_FORMAT FROM DUAL;

-- TZR書式+TZD書式(PDT)の時間をUTCに変換
SELECT TIMESTAMP '2018-11-04 01:30:00 US/Pacific PDT' AT TIME ZONE 'UTC' AS TZR_WITH_PDT FROM DUAL;

-- TZR書式+TZD書式(PST)の時間をUTCに変換
SELECT TIMESTAMP '2018-11-04 01:30:00 US/Pacific PST' AT TIME ZONE 'UTC' AS TZR_WITH_PST FROM DUAL;

TZR_FORMAT
---------------------------------------------------------------------------
2018-11-04 09:30:00 UTC

TZR_WITH_PDT
---------------------------------------------------------------------------
2018-11-04 08:30:00 UTC

TZR_WITH_PST
---------------------------------------------------------------------------
2018-11-04 09:30:00 UTC

TZR書式とTZD書式を併用すると、同じ時刻でもUTC相当の時刻を判別できるんやね彡(゚)(゚)

TIMESTAMP WITH TIME ZONE型のデータをテーブルに格納した場合の検証結果も残しときます。

CREATE TABLE TBL_TZ (
    C1 NUMBER
  , C2 TIMESTAMP WITH TIME ZONE
);
INSERT INTO TBL_TZ VALUES(1, TO_TIMESTAMP_TZ('2018-11-04 08:30:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR'));
INSERT INTO TBL_TZ VALUES(2, TO_TIMESTAMP_TZ('2018-11-04 09:30:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR'));
INSERT INTO TBL_TZ VALUES(3, TO_TIMESTAMP_TZ('2018-11-04 01:30:00 US/Pacific PDT', 'YYYY-MM-DD HH24:MI:SS TZR TZD'));
INSERT INTO TBL_TZ VALUES(4, TO_TIMESTAMP_TZ('2018-11-04 01:30:00 US/Pacific PST', 'YYYY-MM-DD HH24:MI:SS TZR TZD'));
COMMIT;

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS TZR:TZD';
SET LINESIZE 300;
SET PAGESIZE 100;
COLUMN C1            FORMAT 999;
COLUMN C2            FORMAT A36;
COLUMN C2_UTC        FORMAT A30;
COLUMN C2_US_Pacific FORMAT A36;
COLUMN C2_Japan      FORMAT A32;
SELECT C1
     , C2
     , C2 AT TIME ZONE 'UTC'        AS C2_UTC
     , C2 AT TIME ZONE 'US/Pacific' AS C2_US_Pacific
     , C2 AT TIME ZONE 'Japan'      AS C2_Japan
  FROM TBL_TZ;

  C1 C2                                   C2_UTC                         C2_US_PACIFIC                        C2_JAPAN
---- ------------------------------------ ------------------------------ ------------------------------------ --------------------------------
   1 2018-11-04 08:30:00 UTC:GMT          2018-11-04 08:30:00 UTC:GMT    2018-11-04 01:30:00 US/PACIFIC:PDT   2018-11-04 17:30:00 JAPAN:JST
   2 2018-11-04 09:30:00 UTC:GMT          2018-11-04 09:30:00 UTC:GMT    2018-11-04 01:30:00 US/PACIFIC:PST   2018-11-04 18:30:00 JAPAN:JST
   3 2018-11-04 01:30:00 US/PACIFIC:PDT   2018-11-04 08:30:00 UTC:GMT    2018-11-04 01:30:00 US/PACIFIC:PDT   2018-11-04 17:30:00 JAPAN:JST
   4 2018-11-04 01:30:00 US/PACIFIC:PST   2018-11-04 09:30:00 UTC:GMT    2018-11-04 01:30:00 US/PACIFIC:PST   2018-11-04 18:30:00 JAPAN:JST

なるほど、同じ時刻でもPDTとPSTとでUTC時刻やJST時刻が異なる。
あまり馴染みが無かったけど TIMESTAMP WITH TIME ZONE型 は
サマータイムを処理するための色々な機能を備えてるんやね。彡(゚)(゚)

はてなブログの横幅を調整する。(デザインCSSのカスタマイズ)

テーマは「bordeaux」を使用。

デザイン ⇒ (スパナのアイコン) ⇒ デザインCSS と辿って、元の設定を残しつつ下記を貼り付ける。

/* 全体の横幅 */
#content {
width: 1000px;
}

/* 記事の横幅 */
#main {
width: 800px;
}

参考:はてなブログ 横幅などカスタマイズ
http://kachua.hatenablog.com/entry/2018/02/03/210645

SQLNET.COMPRESSIONによる圧縮がDBMS_FILE_TRANSFERパッケージのファイル転送(コピー)に効くかを確かめる。(Oracle Database)

SQLNET.COMPRESSIONは12cR1の新機能で、
Oracle Net Servicesによる通信を圧縮する効果が期待されるパラメータです。

Oracle Database Net Servicesリファレンス 12cリリース1 (12.1) B71289-04
SQLNET.COMPRESSION
https://docs.oracle.com/cd/E57425_01/121/NETRF/sqlnet.htm#CIHJIBBI
データ圧縮を有効または無効にします。サーバー、クライアントの両方とも
このパラメータをONに設定している場合、接続に圧縮が使用されます。

※2018/7/30追記:このパラメータは Advanced Compressionのオプション機能となります。

Oracle Databaseライセンス情報ユーザー・マニュアル 12cリリース1 (12.1)
Oracle Advanced Compression
https://docs.oracle.com/cd/E57425_01/121/DBLIC/options.htm#CJACCDBA
高度なネットワーク圧縮

An Oracle White Paper
Advanced Network Compression
http://www.oracle.com/technetwork/database/enterprise-edition/advancednetworkcompression-2141325.pdf

今回はDBMS_FILE_TRANSFERパッケージによるファイル転送(コピー)の通信が、
SQLNET.COMPRESSIONで圧縮されるかどうかを確認してみるやで彡(゚)(゚)

接続トポロジは以下の通り。

12cR1(データファイルのコピー元) ⇒ DBLINK ⇒ 12cR2(コピー先、DBMS_FILE_TRANSFERを実行)

下記のファイル(users.273.877103105, 約1GB)をコピーして検証してみます。

-- 12cR1
COLUMN FILE_NAME FORMAT A80
SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES;

FILE_NAME                                                                             BYTES
-------------------------------------------------------------------------------- ----------
+DATA/ORCL/13BEADB8DEA01985E0536538A8C0D6E9/DATAFILE/system.270.877103077         272629760
+DATA/ORCL/13BEADB8DEA01985E0536538A8C0D6E9/DATAFILE/sysaux.271.877103077         608174080
+DATA/ORCL/13BEADB8DEA01985E0536538A8C0D6E9/DATAFILE/users.273.877103105         1073741824
                 ★コピー対象(約1GB)^^^^^^^^^^^^^^^^^^^

1. ファイルコピー前のifconfig ethx TX/RX bytes値(SQLNET.COMPRESSION未設定)

ファイルコピー前の Network Interface の RX/TX bytes値を確認します。
TX ⇒ 送信、RX ⇒ 受信となります。

# 12cR1側(コピー元)
$ ifconfig eth0
eth0      Link encap:Ethernet  HWaddr 08:00:27:D0:22:C8
          inet addr:192.168.56.101  Bcast:192.168.56.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fed0:22c8/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:23613 errors:0 dropped:0 overruns:0 frame:0
          TX packets:747935 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:1573399 (1.5 MiB)  TX bytes:2112323918 (1.9 GiB)
                                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^★
# 12cR2側(コピー先)
$ ifconfig eth2
ifconfig eth2
eth2      Link encap:Ethernet  HWaddr 08:00:27:44:DA:94
          inet addr:192.168.56.201  Bcast:192.168.56.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fe44:da94/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:747657 errors:0 dropped:0 overruns:0 frame:0
          TX packets:23654 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:1129696230 (1.0 GiB)  TX bytes:1576027 (1.5 MiB)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^★

2. DBMS_FILE_TRANSFER実行後のifconfig ethx RX/TX bytes値(SQLNET.COMPRESSION未設定)

DBMS_FILE_TRANSFERでファイル転送(コピー)を実行して、
実行後の Network Interface の RX/TX bytes値を確認します。

-- 12cR2側でDBMS_FILE_TRANSFERを実行
CONNECT AYSHIBAT/xxxxxxxx@PDB01
BEGIN
  DBMS_FILE_TRANSFER.GET_FILE(
      'DIR_DATAFILE'        -- 12cR1側のディレクトリ・オブジェクト
    , 'users.273.877103105' -- 12cR1側のデータファイル名
    , 'DBL_PDB1_12cR1'      -- 12cR1 ⇒ 12cR2 の DBLINK名
    , 'DIR_DATAFILE_PDB01'  -- 12cR2側のディレクトリ・オブジェクト
    , 'users.273.877103105' -- 12cR2側のデータファイル名
  );
END;
/

Connected.

PL/SQL procedure successfully completed.
# 12cR1側(コピー元)
$ ifconfig eth0
eth0      Link encap:Ethernet  HWaddr 08:00:27:D0:22:C8
          inet addr:192.168.56.101  Bcast:192.168.56.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fed0:22c8/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:45875 errors:0 dropped:0 overruns:0 frame:0
          TX packets:1491835 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:3043561 (2.9 MiB)  TX bytes:4234449971 (3.9 GiB)
                                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^★
# 12cR2側(コピー先)
$ ifconfig eth2
eth2      Link encap:Ethernet  HWaddr 08:00:27:44:DA:94
          inet addr:192.168.56.201  Bcast:192.168.56.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fe44:da94/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:1491332 errors:0 dropped:0 overruns:0 frame:0
          TX packets:45916 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:2253854289 (2.0 GiB)  TX bytes:3046189 (2.9 MiB)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^★

送受信のバイト数を計算すると、以下の結果になりました。
12cR1側は約1.97GBを送信、12cR2側では約1.05GBを受信してるやね彡(゚)(゚)

# 12cR1側(コピー元)
TX bytes数 … 4,234,449,971(コピー後) - 2,112,323,918(コピー前)
= 2,122,126,053bytes(約1.97GB) を送信

# 12cR2側(コピー先)
RX bytes数 … 2,253,854,289(コピー後) - 1,129,696,230(コピー前)
= 1,124,158,059bytes(約1.05GB) を受信

3. sqlnet.ora設定(SQLNET.COMPRESSION=on)

送信側(12cR1)と受信側(12cR2)のsqlnet.oraに、
それぞれSQLNET.COMPRESSION=onを設定します。
設定後に両方のDBを再起動しています。

# 12cR1側(コピー元)
cat $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.COMPRESSION=on ★
# 12cR2側(コピー先)
cat $ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
SQLNET.COMPRESSION=on ★

#SQLNET.OUTBOUND_CONNECT_TIMEOUT=5
#SQLNET.INBOUND_CONNECT_TIMEOUT=5

4. ファイルコピー前のifconfig ethx RX/TX bytes値(SQLNET.COMPRESSION=on設定済み)

再度ファイルコピー前の Network Interface の RX/TX bytes値を確認します。

# 12cR1側(コピー元)
$ ifconfig eth0
eth0      Link encap:Ethernet  HWaddr 08:00:27:D0:22:C8
          inet addr:192.168.56.101  Bcast:192.168.56.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fed0:22c8/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:45887 errors:0 dropped:0 overruns:0 frame:0
          TX packets:1491865 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:3044569 (2.9 MiB)  TX bytes:4234451867 (3.9 GiB)
                                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^★
# 12cR2側(コピー先)
$ ifconfig eth2
eth2      Link encap:Ethernet  HWaddr 08:00:27:44:DA:94
          inet addr:192.168.56.201  Bcast:192.168.56.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fe44:da94/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:1491362 errors:0 dropped:0 overruns:0 frame:0
          TX packets:45928 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:2253856185 (2.0 GiB)  TX bytes:3047197 (2.9 MiB)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^★

5. DBMS_FILE_TRANSFER実行後のifconfig ethx RX/TX bytes値(SQLNET.COMPRESSION=on設定済)

SQLNET.COMPRESSION=onの状態で、DBMS_FILE_TRANSFERで
ファイル転送(コピー)を実行してみます。
実行後の Network Interface の RX/TX bytes値を確認します。

CONNECT AYSHIBAT/xxxxxxxx@PDB01
BEGIN
  DBMS_FILE_TRANSFER.GET_FILE(
      'DIR_DATAFILE'        -- 12cR1側のディレクトリ・オブジェクト
    , 'users.273.877103105' -- 12cR1側のデータファイル名
    , 'DBL_PDB1_12cR1'      -- 12cR1 ⇒ 12cR2 の DBLINK名
    , 'DIR_DATAFILE_PDB01'  -- 12cR2側のディレクトリ・オブジェクト
    , 'users.273.877103105' -- 12cR2側のデータファイル名
  );
END;
/

Connected.

PL/SQL procedure successfully completed.
# 12cR1側(コピー元)
$ ifconfig eth0
eth0      Link encap:Ethernet  HWaddr 08:00:27:D0:22:C8
          inet addr:192.168.56.101  Bcast:192.168.56.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fed0:22c8/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:58625 errors:0 dropped:0 overruns:0 frame:0
          TX packets:1556074 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:3888437 (3.7 MiB)  TX bytes:4251723023 (3.9 GiB)
                                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^★
# 12cR2側(コピー先)
$ ifconfig eth2
eth2      Link encap:Ethernet  HWaddr 08:00:27:44:DA:94
          inet addr:192.168.56.201  Bcast:192.168.56.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fe44:da94/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:1555359 errors:0 dropped:0 overruns:0 frame:0
          TX packets:58624 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:2268359561 (2.1 GiB)  TX bytes:3888293 (3.7 MiB)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^★
# 12cR1側(コピー元)
TX bytes数 … 4,251,723,023(コピー後) - 4,234,451,867(コピー前)
= 17,271,156bytes(約0.016GB) を送信

# 12cR2側(コピー先)
RX bytes数 … 2,268,359,561(コピー後) - 2,253,856,185(コピー前)
= 14,503,376bytes(約0.014GB) を受信

12cR1側は約0.016GBを送信、12cR2側では約0.014GBを受信しています彡(゚)(゚)
sqlnet.ora(SQLNET.COMPRESSION=on)設定前と比較して、
少なくなってますやね彡(^)(^)
#圧縮率は格納データの特性に依存する想定なので、実測してみて下さい。

まとめ

SQLNET.COMPRESSION=onによる圧縮は、DBMS_FILE_TRANSFERパッケージによる
ファイル転送(コピー)に効果が有ることが解りました。

下記の記事に書いた TTS(トランスポータブル表領域) で、ネットワーク帯域が
ボトルネックになるケースでは試してみる価値が有りそうですやね彡(^)(^)
#送信元が12cR1(12.1)以降の必要が有りますけど(゚ε゚ )

Oracle Database 11gR2(11.2.0.4)から12cR2(12.2.0.1)のPDBにTTS(トランスポータブル表領域)をしてみる。
http://d.hatena.ne.jp/gonsuke777/20180626/1530009840

ちなこの機能(SQLNET.COMPRESSION=on)は PDB の Clone や Relocate に
効果は有るんですかね?どちらも裏でデータファイルのコピーが
走っているはずなんで、隙を見て検証してみますやで彡(゚)(゚)