shell の while read で -r を付与して \（バックスラッシュ）によるクォートを防止する。

珍しくOracle Database以外のネタ、shell の while read に -r を付与して、
\（バックスラッシュ）によるクォートを防止してみるやで。彡(ﾟ)(ﾟ)

1. テスト用 の shell script

data.txt の内容を1行ずつ出力する単純なシェルです。

while_read.sh

#!/bin/bash

CNT=0;
cat data.txt | while read line
do
  CNT=`expr ${CNT} + 1`;
  echo "${CNT}:${line}";
done;

2. テストデータ（data.txt）

テストデータの中身です。
1行目の最終文字が\（バックスラッシュ）です。

cat data.txt

iii\
jjj
kkk

3. テスト用 shell script の実行結果

この状態での実行結果は下記の通りです。1行目の最終文字の
\（バックスラッシュ）で改行がクォートされてしまうため、
1行目と2行目が繋がって出力されてしまいますやね彡(ﾟ)(ﾟ)

./while_read.sh

1:iiijjj
2:kkk

4. read に -r を付与して実行

read に -r を付与して shell script を実行してみます。
改行がクォートされず、3行出力されましたやね彡(^)(^)

cat while_read.sh

#!/bin/bash

CNT=0;
cat data.txt | while read -r line
do
  CNT=`expr ${CNT} + 1`;
  echo "${CNT}:${line}";
done;

./while_read.sh

1:iii\
2:jjj
3:kkk

5. 参考

read コマンドの使い方 - 拡張 POSIX
シェルスクリプト Advent Calendar 2013 - ダメ出し Blog
https://fumiyas.github.io/2013/12/14/read.sh-advent-calendar.html

表のオンライン再定義(DBMS_REDEFINITION)で、データを更新しながら RANGE PARTITION＆グローバル索引 -> HASH PARTITION＆ローカル索引への定義変更をやってみる。(Oracle Database)

タイトルが長いですが、表のオンライン再定義はDMLによるデータ更新を 制限せずに、表定義(論理構造や物理構造)を変更する機能です。

Oracle Database管理者ガイド 12cリリース1 (12.1)
20.7 表のオンライン再定義
https://docs.oracle.com/cd/E57425_01/121/ADMIN/tables.htm#GUID-92361F74-4796-407D-A3B9-569C6E544E34

今回は下記のパーティション構造とグローバル索引⇒ローカル索引への 変更を検証してみますやで彡(ﾟ)(ﾟ) バージョンは訳あって 12cR1(12.1.0.2) です。

RANGE PARTITION＆グローバル索引の表 ⇒ HASH PARTITION＆ローカル索引の表

1. テスト表の作成とテストデータのINSERT

テスト表(対象表／仮表)を作成して、対象表にテストデータをINSERTします。 1000件を初期セットした後、1秒に1件ずつINSERTしていくやで彡(ﾟ)(ﾟ) ＃最終的には対象表に1180件をINSERTしていきます。

-- Old Table
CREATE TABLE TBL_A (
    C1  NUMBER
  , C2  NUMBER
)
PARTITION BY RANGE (C1) (
    PARTITION P1   VALUES LESS THAN (100),
    PARTITION P2   VALUES LESS THAN (200),
    PARTITION PMAX VALUES LESS THAN (MAXVALUE)
);
ALTER TABLE TBL_A ADD CONSTRAINT TBL_A_PK PRIMARY KEY (C1) USING INDEX GLOBAL;
CREATE INDEX TBL_A_I1 on TBL_A (C2) GLOBAL;

-- New Table
CREATE TABLE NEW_TBL_A (
    C1  NUMBER
  , C2  NUMBER
)
PARTITION BY HASH (C1) (
    PARTITION P1
  , PARTITION P2
  , PARTITION P3
  , PARTITION P4
);
--※仮表の索引と主キーは後で作成

BEGIN
  -- Test Data 
  INSERT INTO TBL_A SELECT LEVEL, LEVEL FROM DUAL CONNECT BY LEVEL <= 1000;
  COMMIT;
  FOR i IN 1001..1180
  LOOP
    INSERT INTO TBL_A VALUES(i, i);
    COMMIT;
    DBMS_LOCK.SLEEP(1);
  END LOOP;
END;
/

Table created.

Table altered.

Index created.

Table created.

:
（PL/SQLはしばらく動作する）

（※ここから別セッション）この時点でのディクショナリ情報は下記の通りです。

10:26:24 SQL> -- Part_tables
10:26:24 SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, STATUS
10:26:24   2  FROM USER_PART_TABLES WHERE TABLE_NAME LIKE '%TBL_A%';

TABLE_NAME      PARTITION STATUS
--------------- --------- --------
NEW_TBL_A       HASH      VALID    ★仮表はHASHパーティション
TBL_A           RANGE     VALID    ★対象表はRANGEパーティション

10:26:24 SQL> -- Indexes
10:26:24 SQL> SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS, STATUS, GLOBAL_STATS
10:26:24   2  FROM USER_INDEXES WHERE TABLE_NAME LIKE '%TBL_A%';

TABLE_OWNER     TABLE_NAME      INDEX_NAME      UNIQUENES STATUS   GLO
--------------- --------------- --------------- --------- -------- ---
AYSHIBAT        TBL_A           TBL_A_PK        UNIQUE    VALID    YES ★対象表のグローバル索引(主キー)
AYSHIBAT        TBL_A           TBL_A_I1        NONUNIQUE VALID    YES ★対象表のグローバル索引

10:26:24 SQL> -- Constraints
10:26:24 SQL> SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
10:26:24   2  FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE '%TBL_A%';

OWNER           TABLE_NAME      CONSTRAINT_NAME C
--------------- --------------- --------------- -
AYSHIBAT        TBL_A           TBL_A_PK        P

10:26:26 SQL>
10:26:26 SQL> -- Data Check
10:26:26 SQL> SELECT COUNT(*) FROM TBL_A;

  COUNT(*)
----------
      1021

10:26:27 SQL> SELECT COUNT(*) FROM NEW_TBL_A;

  COUNT(*)
----------
         0

2. DBMS_REDEFINITION.CAN_REDEF_TABLEによる事前チェック

DBMS_REDEFINITION.CAN_REDEF_TABLEプロシージャで表がオンライン再定義可能かチェックします。

-- Check redefinition possibility.
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'TBL_A', DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed.

オンライン表再定義が不可能な場合は、下記のようなエラーが出力されます彡(ﾟ)(ﾟ)

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'TBL_B', DBMS_REDEFINITION.CONS_USE_PK);

BEGIN DBMS_REDEFINITION.CAN_REDEF_TABLE(USER, 'TBL_B', DBMS_REDEFINITION.CONS_USE_PK); END;

*
ERROR at line 1:
ORA-12088: cannot online redefine table "AYSHIBAT"."TBL_B" with unsupported datatype
ORA-06512: at "SYS.DBMS_REDEFINITION", line 173
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3664
ORA-06512: at line 1

3. DBMS_REDEFINITION.START_REDEF_TABLEプロシージャでオンライン再定義を開始

DBMS_REDEFINITION.START_REDEF_TABLEプロシージャでオンライン再定義を開始します。 対象表⇒仮表への初期データ移行が、この時点でのデータ断面で実行されます。

EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'TBL_A', 'NEW_TBL_A');

SELECT COUNT(*) FROM TBL_A;

SELECT COUNT(*) FROM NEW_TBL_A;



10:26:27 SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(USER, 'TBL_A', 'NEW_TBL_A');

PL/SQL procedure successfully completed.

10:26:29 SQL> SELECT COUNT(*) FROM TBL_A;

  COUNT(*)
----------
      1023

10:26:29 SQL> SELECT COUNT(*) FROM NEW_TBL_A;

  COUNT(*)
----------
      1022 ★仮表にデータが挿入、件数差異は裏で対象表のデータINSERTを実行しているため。

4. 仮表にローカル索引(主キー、非ユニーク索引)を作成

仮表に主キーと非ユニークのローカル索引を作成します。

CREATE TABLE時に作らないのは、上記 3. の初期データ移行の 負荷(処理量)を減らすため。索引無い方がINSERTは速いんで彡(ﾟ)(ﾟ)

ALTER TABLE NEW_TBL_A ADD CONSTRAINT NEW_TBL_A_PK PRIMARY KEY (C1) USING INDEX LOCAL;

CREATE INDEX NEW_TBL_A_I1 on NEW_TBL_A (C2) LOCAL;

Table altered.

Index created.

この時点でのディクショナリ情報は下記の通りです。

10:26:29 SQL> -- Part_tables
10:26:29 SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, STATUS
10:26:29   2  FROM USER_PART_TABLES WHERE TABLE_NAME LIKE '%TBL_A%';

TABLE_NAME      PARTITION STATUS
--------------- --------- --------
NEW_TBL_A       HASH      VALID
TBL_A           RANGE     VALID

10:26:29 SQL> -- Indexes
10:26:29 SQL> SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS, STATUS, GLOBAL_STATS
10:26:29   2  FROM USER_INDEXES WHERE TABLE_NAME LIKE '%TBL_A%';

TABLE_OWNER     TABLE_NAME      INDEX_NAME      UNIQUENES STATUS   GLO
--------------- --------------- --------------- --------- -------- ---
AYSHIBAT        NEW_TBL_A       NEW_TBL_A_I1    NONUNIQUE N/A      NO  ★仮表のローカル索引
AYSHIBAT        NEW_TBL_A       NEW_TBL_A_PK    UNIQUE    N/A      NO  ★仮表のローカル索引
AYSHIBAT        TBL_A           TBL_A_I1        NONUNIQUE VALID    YES
AYSHIBAT        TBL_A           TBL_A_PK        UNIQUE    VALID    YES
AYSHIBAT        MLOG$_TBL_A     I_MLOG$_TBL_A   NONUNIQUE VALID    YES

10:26:29 SQL> -- Constraints
10:26:29 SQL> SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
10:26:29   2  FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE '%TBL_A%';

OWNER           TABLE_NAME      CONSTRAINT_NAME C
--------------- --------------- --------------- -
AYSHIBAT        NEW_TBL_A       NEW_TBL_A_PK    P ★仮表の主キー制約
AYSHIBAT        TBL_A           TBL_A_PK        P

5. DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECTプロシージャによる索引＆主キー制約の関連付け

DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECTプロシージャで 対象表と仮表の索引と主キーを関連付けます。

BEGIN
  DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
      USER                         -- uname
    , 'TBL_A'                      -- orig_table
    , 'NEW_TBL_A'                  -- int_table
    , DBMS_REDEFINITION.CONS_INDEX -- dep_type
    , USER                         -- dep_owner
    , 'TBL_A_PK'                   -- dep_orig_name
    , 'NEW_TBL_A_PK'               -- dep_int_name
  );
END;
/

BEGIN
  DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
      USER                              -- uname
    , 'TBL_A'                           -- orig_table
    , 'NEW_TBL_A'                       -- int_table
    , DBMS_REDEFINITION.CONS_CONSTRAINT -- dep_type
    , USER                              -- dep_owner
    , 'TBL_A_PK'                        -- dep_orig_name
    , 'NEW_TBL_A_PK'                    -- dep_int_name
  );
END;
/

BEGIN
  DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
      USER                         -- uname
    , 'TBL_A'                      -- orig_table
    , 'NEW_TBL_A'                  -- int_table
    , DBMS_REDEFINITION.CONS_INDEX -- dep_type
    , USER                         -- dep_owner
    , 'TBL_A_I1'                   -- dep_orig_name
    , 'NEW_TBL_A_I1'               -- dep_int_name
  );
END;
/

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTSプロシージャで 対象表の関連オブジェクトも仮表にコピーします。

ただし今回のように、REGISTER_DEPENDENT_OBJECT で明示的に 関連付けを行うケースだと、この手順は不要かも……彡(-)(-)

SET SERVEROUTPU ON SIZE 1000000;
DECLARE
  error_count pls_integer := 0;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
      USER        -- uname
    , 'TBL_A'     -- orig_table
    , 'NEW_TBL_A' -- int_table
    , 0           -- copy_indexes 0...NO_COPY
    , FALSE       -- copy_triggers
    , FALSE       -- copy_constraints
    , FALSE       -- copy_privileges
    , FALSE       -- ignore_errors
    , error_count -- num_errors
    , FALSE       -- copy_statistics
    , FALSE       -- copy_mlogs
  );
  DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
END;
/

errors := 0

PL/SQL procedure successfully completed.

6. DBMS_REDEFINITION.FINISH_REDEF_TABLEプロシージャでオンライン再定義を完了する。

DBMS_REDEFINITION.FINISH_REDEF_TABLEプロシージャでオンライン再定義を完了します。

EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(USER, 'TBL_A', 'NEW_TBL_A');

PL/SQL procedure successfully completed.

完了後のディクショナリとデータ件数は以下の通りです。

10:26:34 SQL> -- Part_tables
10:26:34 SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, STATUS
10:26:34   2  FROM USER_PART_TABLES WHERE TABLE_NAME LIKE '%TBL_A%';

TABLE_NAME      PARTITION STATUS
--------------- --------- --------
NEW_TBL_A       RANGE     VALID
TBL_A           HASH      VALID   ★HASHパーティションに変わっている。

10:26:34 SQL> -- Indexes
10:26:34 SQL> SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, UNIQUENESS, STATUS, GLOBAL_STATS
10:26:34   2  FROM USER_INDEXES WHERE TABLE_NAME LIKE '%TBL_A%';

TABLE_OWNER     TABLE_NAME      INDEX_NAME      UNIQUENES STATUS   GLO
--------------- --------------- --------------- --------- -------- ---
AYSHIBAT        NEW_TBL_A       NEW_TBL_A_PK    UNIQUE    VALID    YES
AYSHIBAT        NEW_TBL_A       NEW_TBL_A_I1    NONUNIQUE VALID    YES
AYSHIBAT        TBL_A           TBL_A_PK        UNIQUE    N/A      NO  ★ローカル索引に変わっている。
AYSHIBAT        TBL_A           TBL_A_I1        NONUNIQUE N/A      NO  ★ローカル索引に変わっている。

10:26:35 SQL> -- Constraints
10:26:35 SQL> SELECT OWNER, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
10:26:35   2  FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE '%TBL_A%';

OWNER           TABLE_NAME      CONSTRAINT_NAME C
--------------- --------------- --------------- -
AYSHIBAT        TBL_A           TBL_A_PK        P
AYSHIBAT        NEW_TBL_A       NEW_TBL_A_PK    P

10:26:38 SQL>
10:26:38 SQL> -- Redifiniitoned Table Data Check
10:26:38 SQL> SELECT COUNT(*) FROM NEW_TBL_A;

  COUNT(*)
----------
      1028

10:26:38 SQL> SELECT COUNT(*) FROM TBL_A;

  COUNT(*)
----------
      1032 ★件数が増えていってるのは裏でINSERTしているため

10:26:38 SQL> SELECT COUNT(*) FROM TBL_A;

  COUNT(*)
----------
      1038 ★件数が増えていってるのは裏でINSERTしているため
：
：

10:32:43 SQL> SELECT COUNT(*) FROM TBL_A;

  COUNT(*)
----------
      1180 ★最終的なデータ件数

10:32:36 SQL> SELECT COUNT(*) FROM TBL_A PARTITION (P1);

  COUNT(*)
----------
       285 ★HASHパーティションP1の件数

10:32:36 SQL> SELECT COUNT(*) FROM TBL_A PARTITION (P2);

  COUNT(*)
----------
       282 ★HASHパーティションP2の件数

10:32:36 SQL> SELECT COUNT(*) FROM TBL_A PARTITION (P3);

  COUNT(*)
----------
       305 ★HASHパーティションP3の件数

10:32:36 SQL> SELECT COUNT(*) FROM TBL_A PARTITION (P4);

  COUNT(*)
----------
       308 ★HASHパーティションP4の件数

10:32:36 SQL>

7. まとめ

HASHパーティション化とローカル索引への表定義変更を、 データの更新を止めずに実行できたで！彡(^)(^)

12cR1からのDBMS_REDEFINITIONには、上記で説明した一連のプロシージャを 一まとめにしたREDEF_TABLEプロシージャが提供されています。こちらは別の機会に彡(ﾟ)(ﾟ)

Oracle Database PL/SQLパッケージおよびタイプ・リファレンス
12c リリース1 (12.1)
122 DBMS_REDEFINITION
REDEF_TABLEプロシージャ
https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_redefi.htm#CHDDDDFG

8. 参考情報

マニュアルやサポートドキュメントも読んでおくんやで彡(ﾟ)(ﾟ)

Oracle Database管理者ガイド 12cリリース1 (12.1)
20.7 表のオンライン再定義
https://docs.oracle.com/cd/E57425_01/121/ADMIN/tables.htm#GUID-92361F74-4796-407D-A3B9-569C6E544E34
　
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス
12c リリース1 (12.1)
122 DBMS_REDEFINITION
https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_redefi.htm#CBBJJAIF
　
MOSドキュメント：How To Partition Existing Table Using DBMS_REDEFINITION (ドキュメントID 472449.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?id=472449.1
※ログインが必要です。

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
　
　

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

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

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

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

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:00 ★
2018-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:00 ★
2018-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: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: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:00
：
2018-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型 は
サマータイムを処理するための色々な機能を備えてるんやね。彡(ﾟ)(ﾟ)