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型 は
サマータイムを処理するための色々な機能を備えてるんやね。彡(ﾟ)(ﾟ)
はてなブログの横幅を調整する。(デザイン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 に
効果は有るんですかね？どちらも裏でデータファイルのコピーが
走っているはずなんで、隙を見て検証してみますやで彡(ﾟ)(ﾟ)
DBMS_FILE_TRANSFERパッケージでDB(11gR2) -> DBLINK -> PDB(12cR2)のデータファイルの転送(コピー)を実行する。(Oracle Database)
DBMS_FILE_TRANSFERパッケージで中間ファイルを介さずに、
DB(11gR2) to PDB(12cR2) の直接のファイル転送(コピー)を
実行してみますやで彡(ﾟ)(ﾟ)
Oracle Database PL/SQL Packages and Types Reference 12c Release 2 (12.2) 67 DBMS_FILE_TRANSFER https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_FILE_TRANSFER.html#GUID-9E206058-78CB-4028-8883-7C28B845F86C
今回試すのは以下のような接続トポロジです。
11gR2(データファイルのコピー元) ⇒ DBLINK ⇒ 12cR2(コピー先、DBMS_FILE_TRANSFERを実行)
1. データファイルのコピー元にディレクトリ・オブジェクトを作成
データファイルのパスを参照可能なディレクトリ・オブジェクトを作成して、
READ権限を付与します。下記例では専用ユーザーを作成していますが、必須ではありません。
-- 11gR2(ユーザー作成) CREATE USER FILETRANS IDENTIFIED BY xxxxxxxx DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT CREATE SESSION TO FILETRANS; -- 11gR2(ディレクトリ・オブジェクト作成) CREATE DIRECTORY DIR_DATAFILE as '+DATA/ORCL/DATAFILE'; GRANT READ ON DIRECTORY DIR_DATAFILE TO FILETRANS; User created. Grant succeeded. Directory created. Grant succeeded.
2. データファイルのコピー先(PDB)にディレクトリ・オブジェクトを作成
データファイルのコピー先(PDB)のパスにアクセス可能なディレクトリ・オブジェクトを
PDB上に作成して、DBMS_FILE_TRANSFERパッケージを実行するユーザーに
ディレクトリ・オブジェクトのWRITE権限を付与します。
-- 12cR2 CONNECT /AS SYSDBA ALTER SESSION SET CONTAINER = PDB01; SHOW CON_NAME; CREATE DIRECTORY DIR_DATAFILE_PDB01 as '/u01/app/oracle/oradata/AYUCDB/6C4B2D3386C20FB8E055000000000001/datafile/'; GRANT WRITE ON DIRECTORY DIR_DATAFILE_PDB01 TO AYSHIBAT; Connected. Session altered. CON_NAME ------------------------------ PDB01 Directory created. Grant succeeded.
3. データファイルのコピー先(PDB)に、コピー元へ接続するDBLINKを作成
データファイルのコピー先(PDB)に、コピー元(今回は11gR2)へ接続する
DBLINK(DATABASE LINK)を作成します。コピー元の接続ユーザーは、
コピーするデータファイルのディレクトリ・オブジェクトのREAD権限が必要です。
-- 12cR2 CONNECT AYSHIBAT/xxxxxxxx@PDB01 SHOW CON_NAME; CREATE DATABASE LINK DBL_ORCL_11GR2 CONNECT TO FILETRANS IDENTIFIED BY xxxxxxxx USING 'ORCL_11GR2'; -- DBLINKの動作確認 SET LINESIZE 170; SELECT * FROM V$VERSION@DBL_ORCL_11GR2; SELECT * FROM V$VERSION; Connected. CON_NAME ------------------------------ PDB01 Database link created. BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 PL/SQL Release 12.2.0.1.0 - Production 0 CORE 12.2.0.1.0 Production 0 TNS for Linux: Version 12.2.0.1.0 - Production 0 NLSRTL Version 12.2.0.1.0 - Production 0
4. DBMS_FILE_TRANSFERパッケージのGET_FILEプロシージャを実行して、データファイルをコピー
コピー先(今回は12cR2側)でDBMS_FILE_TRANSFERパッケージの
GET_FILEプロシージャを実行して、データファイルをコピーします。
-- 12cR2 BEGIN DBMS_FILE_TRANSFER.GET_FILE( 'DIR_DATAFILE' -- 11gR2側のディレクトリ・オブジェクト , 'tbs_tts1.266.979841605' -- 11gR2側のデータファイル名 , 'DBL_ORCL_11GR2' -- 12cR2 ⇒ 11gR2 の DBLINK名 , 'DIR_DATAFILE_PDB01' -- 12cR2側のディレクトリ・オブジェクト , 'tbs_tts1.266.979841605' -- 12cR2側のデータファイル名 ); END; / BEGIN DBMS_FILE_TRANSFER.GET_FILE( 'DIR_DATAFILE' -- 11gR2側のディレクトリ・オブジェクト , 'tbs_tts2.267.979841605' -- 11gR2側のデータファイル名 , 'DBL_ORCL_11GR2' -- 12cR2 ⇒ 11gR2 の DBLINK名 , 'DIR_DATAFILE_PDB01' -- 12cR2側のディレクトリ・オブジェクト , 'tbs_tts2.267.979841605' -- 12cR2側のデータファイル名 ); END; /
# DBMS_FILE_TRANSFER実行前 $ pwd /u01/app/oracle/oradata/AYUCDB/6C4B2D3386C20FB8E055000000000001/datafile $ ls -la total 1133172 drwxr-x---. 2 oracle oinstall 4096 Jun 27 14:26 . drwxr-x---. 3 oracle oinstall 4096 May 16 12:19 .. -rw-r-----. 1 oracle oinstall 398467072 Jun 27 14:25 o1_mf_sysaux_fhq8s86l_.dbf -rw-r-----. 1 oracle oinstall 272637952 Jun 27 14:37 o1_mf_system_fhq8s865_.dbf -rw-r-----. 1 oracle oinstall 135274496 Jun 26 19:11 o1_mf_temp_fhq8s86n_.dbf -rw-r-----. 1 oracle oinstall 104865792 Jun 27 14:37 o1_mf_undotbs1_fhq8s86m_.dbf -rw-r-----. 1 oracle oinstall 259530752 Jun 27 13:47 o1_mf_users_fhq8s86o_.dbf
SQL> BEGIN 2 DBMS_FILE_TRANSFER.GET_FILE( 3 'DIR_DATAFILE' -- 11gR2側のディレクトリ・オブジェクト 4 , 'tbs_tts1.266.979841605' -- 11gR2側のデータファイル名 5 , 'DBL_ORCL_11GR2' -- 12cR2 ⇒ 11gR2 の DBLINK名 6 , 'DIR_DATAFILE_PDB01' -- 12cR2側のディレクトリ・オブジェクト 7 , 'tbs_tts1.266.979841605' -- 12cR2側のデータファイル名 8 ); 9 END; 10 / PL/SQL procedure successfully completed. SQL> BEGIN 2 DBMS_FILE_TRANSFER.GET_FILE( 3 'DIR_DATAFILE' -- 11gR2側のディレクトリ・オブジェクト 4 , 'tbs_tts2.267.979841605' -- 11gR2側のデータファイル名 5 , 'DBL_ORCL_11GR2' -- 12cR2 ⇒ 11gR2 の DBLINK名 6 , 'DIR_DATAFILE_PDB01' -- 12cR2側のディレクトリ・オブジェクト 7 , 'tbs_tts2.267.979841605' -- 12cR2側のデータファイル名 8 ); 9 END; 10 / PL/SQL procedure successfully completed.
# DBMS_FILE_TRANSFER実行後 $ pwd /u01/app/oracle/oradata/AYUCDB/6C4B2D3386C20FB8E055000000000001/datafile $ ls -la total 1153668 drwxr-x---. 2 oracle oinstall 4096 Jun 27 14:40 . drwxr-x---. 3 oracle oinstall 4096 May 16 12:19 .. -rw-r-----. 1 oracle oinstall 398467072 Jun 27 14:25 o1_mf_sysaux_fhq8s86l_.dbf -rw-r-----. 1 oracle oinstall 272637952 Jun 27 14:37 o1_mf_system_fhq8s865_.dbf -rw-r-----. 1 oracle oinstall 135274496 Jun 26 19:11 o1_mf_temp_fhq8s86n_.dbf -rw-r-----. 1 oracle oinstall 104865792 Jun 27 14:37 o1_mf_undotbs1_fhq8s86m_.dbf -rw-r-----. 1 oracle oinstall 259530752 Jun 27 13:47 o1_mf_users_fhq8s86o_.dbf -rw-r-----. 1 oracle oinstall 10493952 Jun 27 14:40 tbs_tts1.266.979841605 ★コピーされたデータファイル -rw-r-----. 1 oracle oinstall 10493952 Jun 27 14:40 tbs_tts2.267.979841605 ★コピーされたデータファイル $
見事にデータファイルがコピーされましたやで！彡(^)(^)
5. まとめ
cpやscp等によるファイルシステム(中間ファイル)を介した転送と異なって、
DBMS_FILE_TRANSFERパッケージは DB to DB による直接のファイル転送(コピー)が
実現できるんやで彡(^)(^) ＃中間ファイルを介さないので理論上は速い。
転送中(転送経路上)の圧縮ができるとより理想的なんやけど、
下記パラメータ(SQLNET.COMPRESSION)を使ったら実現可能なんやろうか……？
彡(ﾟ)(ﾟ)
Oracle Database Net Servicesリファレンス 12cリリース2 (12.2) 5.2.20 SQLNET.COMPRESSION https://docs.oracle.com/cd/E82638_01/netrf/parameters-for-the-sqlnet-ora-file.html#GUID-61CE4FA9-3ABB-4E9B-B788-FB57E6B56F47 データ圧縮を有効または無効にします。サーバー、クライアントの両方ともこのパラメータをONに設定している場合、接続に圧縮が使用されます。
Oracle Database 11gR2(11.2.0.4)から12cR2(12.2.0.1)のPDBにTTS(トランスポータブル表領域)をしてみる。
表題の通り、Oracle Database 11gR2(11.2.0.4)から12cR2(12.2.0.1)の
PDBにTTS(トランスポータブル表領域)を実行してみるやで彡(ﾟ)(ﾟ)
プラットフォームはどちらも Linux x86 64bitで試します。
特別な手順は必要なくマニュアル通りに粛々と実施するだけです。
Oracle Database管理者ガイド 11gリリース2 (11.2) B56301-08 データベース間で表領域をトランスポートする手順および例 https://docs.oracle.com/cd/E16338_01/server.112/b56301/tspaces.htm#i1007252 Oracle Database データベース管理者ガイド 12c リリース2 (12.2) 15.1.2.2 トランスポータブル表領域またはトランスポータブル表の使用例 https://docs.oracle.com/cd/E82638_01/admin/transporting-data.html#GUID-5158E6EB-591A-4DF0-9A44-BAF1E10338A8
0. 移行対象の表／表領域
移行対象は以下の表(TBL_TEST_TTS1,TBL_TEST_TTS2)／表領域(TBS_TTS1,TBS_TTS2)とします。
-- 11gR2 COLUMN OWNER FORMAT A20; COLUMN SEGMENT_NAME FORMAT A20; COLUMN BYTES FORMAT 999,999,999; COLUMN TABLESPACE_NAME FORMAT A20; SELECT OWNER, SEGMENT_NAME, BYTES, TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME IN ('TBS_TTS1', 'TBS_TTS2'); OWNER SEGMENT_NAME BYTES TABLESPACE_NAME -------------------- -------------------- ------------ -------------------- AYSHIBAT TBL_TEST_TTS1 196,608 TBS_TTS1 AYSHIBAT TBL_TEST_TTS2 196,608 TBS_TTS2
1. 移行元／移行先のエンディアンを確認
まず移行元／移行先のエンディアンを確認します。
今回はどちらも同じエンディアン彡(ﾟ)(ﾟ)
-- 移行元(11gR2)のエンディアン COLUMN PLATFORM_NAME FORMAT A30; COLUMN ENDIAN_FORMAT FORMAT A30; SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME; PLATFORM_NAME ENDIAN_FORMAT ------------------------------ ------------------------------ Linux x86 64-bit Little -- 移行先(12cR2)のエンディアン COLUMN PLATFORM_NAME FORMAT A30; COLUMN ENDIAN_FORMAT FORMAT A30; SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME; PLATFORM_NAME ENDIAN_FORMAT ------------------------------ ------------------------------ Linux x86 64-bit Little
2. 表領域が自己完結しているかを確認
下記のコマンドで表領域が自己完結しているかを確認します。
TRANSPORT_SET_VIOLATIONSで行が出なければ、自己完結しています。
-- 11gR2 EXECUTE SYS.DBMS_TTS.TRANSPORT_SET_CHECK('TBS_TTS1, TBS_TTS2', TRUE); SELECT * FROM SYS.TRANSPORT_SET_VIOLATIONS; PL/SQL procedure successfully completed. no rows selected
3. 移行対象の表領域を READ ONLY にします。
下記のコマンドで移行対象の表領域を READ ONLY にします。
-- 11gR2 ALTER TABLESPACE TBS_TTS1 READ ONLY; ALTER TABLESPACE TBS_TTS2 READ ONLY; Tablespace altered. Tablespace altered.
4. 表領域の定義情報をエクスポート(expdp)
expdpコマンドで表領域の定義情報をエクスポートします。
定義情報だけのエクスポートなので、時間はそれほど掛かりません。
# 11gR2 expdp AYSHIBAT/xxxxxxxx@orcl dumpfile=tts_users.dmp directory=DIR_AYSHIBAT transport_tablespaces=TBS_TTS1,TBS_TTS2 logfile=exp_tts_users.log Export: Release 11.2.0.4.0 - Production on Tue Jun 26 18:49:44 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "AYSHIBAT"."SYS_EXPORT_TRANSPORTABLE_01": AYSHIBAT/********@orcl dumpfile=tts_users.dmp directory=DIR_AYSHIBAT transport_tablespaces=TBS_TTS1,TBS_TTS2 logfile=exp_tts_users.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "AYSHIBAT"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for AYSHIBAT.SYS_EXPORT_TRANSPORTABLE_01 is: /home/oracle/work/ayshibat/tts_users.dmp ****************************************************************************** Datafiles required for transportable tablespace TBS_TTS1: +DATA/orcl/datafile/tbs_tts1.266.979841605 Datafiles required for transportable tablespace TBS_TTS2: +DATA/orcl/datafile/tbs_tts2.267.979841605 Job "AYSHIBAT"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Tue Jun 26 18:50:53 2018 elapsed 0 00:01:06
5. dmpファイルとデータファイルの転送(コピー)
dmpファイルとデータファイルを移行先(12cR2)に転送(コピー)します。
データファイルの転送にはcpコマンド/DBMS_FILE_TRANSFER/RMAN等々の手段が有りますが、
今回は 11gR2のASM ⇒ asmcmdでファイルシステムに出力 ⇒ scpで転送 ⇒ 12cR2のファイルシステム とします。
# 11gR2 asmcmd cd +DATA/ORCL/DATAFILE ls -l Type Redund Striped Time Sys Name DATAFILE MIRROR COARSE JUN 26 18:00:00 Y SYSAUX.257.836897651 DATAFILE MIRROR COARSE JUN 26 18:00:00 Y SYSTEM.256.836897649 DATAFILE MIRROR COARSE JUN 26 18:00:00 Y TBS_TTS1.266.979841605 DATAFILE MIRROR COARSE JUN 26 18:00:00 Y TBS_TTS2.267.979841605 DATAFILE MIRROR COARSE JUN 26 18:00:00 Y UNDOTBS1.258.836897653 DATAFILE MIRROR COARSE JUN 26 18:00:00 Y USERS.259.836897653 cp '+DATA/ORCL/DATAFILE/TBS_TTS1.266.979841605' '/home/grid/work/ayshibat/TBS_TTS1.266.979841605' cp '+DATA/ORCL/DATAFILE/TBS_TTS2.267.979841605' '/home/grid/work/ayshibat/TBS_TTS2.267.979841605' copying +DATA/ORCL/DATAFILE/TBS_TTS1.266.979841605 -> /home/grid/work/ayshibat/TBS_TTS1.266.979841605 copying +DATA/ORCL/DATAFILE/TBS_TTS2.267.979841605 -> /home/grid/work/ayshibat/TBS_TTS2.267.979841605 ↓ 【scpでdmpファイルとデータファイルを転送(コピー)】 ↓ # 12cR2 cd /u01/app/oracle/oradata/AYUCDB/6C4B2D3386C20FB8E055000000000001/datafile/ ls -la -rw-r--r--. 1 oracle oinstall 10493952 Jun 26 18:55 TBS_TTS1.266.979841605 ★scpで転送(コピー)したデータファイル -rw-r--r--. 1 oracle oinstall 10493952 Jun 26 18:56 TBS_TTS2.267.979841605 ★scpで転送(コピー)したデータファイル -rw-r-----. 1 oracle oinstall 398467072 Jun 26 18:35 o1_mf_sysaux_fhq8s86l_.dbf -rw-r-----. 1 oracle oinstall 272637952 Jun 26 18:57 o1_mf_system_fhq8s865_.dbf : cd /home/oracle/work/ayshibat ls -la tts_users.dmp -rw-r--r--. 1 oracle oinstall 94208 Jun 26 18:50 tts_users.dmp ★scpで転送(コピー)したdmpファイル
6. PDBに表領域の定義情報をインポート(impdp)
impdpコマンドで表領域の定義情報をインポートします。
やはり定義情報だけのインポートなので、時間はそれほど掛かりません。
# 12cR2 impdp AYSHIBAT/xxxxxxxx@PDB01 dumpfile=tts_users.dmp directory=DIR_AYSHIBAT \ transport_datafiles=\ '/u01/app/oracle/oradata/AYUCDB/6C4B2D3386C20FB8E055000000000001/datafile/TBS_TTS1.266.979841605',\ '/u01/app/oracle/oradata/AYUCDB/6C4B2D3386C20FB8E055000000000001/datafile/TBS_TTS2.267.979841605' \ logfile=imp_tts_users.log Import: Release 12.2.0.1.0 - Production on Tue Jun 26 19:10:48 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "AYSHIBAT"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "AYSHIBAT"."SYS_IMPORT_TRANSPORTABLE_01": AYSHIBAT/********@PDB01 dumpfile=tts_users.dmp directory=DIR_AYSHIBAT transport_datafiles=/u01/app/oracle/oradata/AYUCDB/6C4B2D3386C20FB8E055000000000001/datafile/TBS_TTS1.266.979841605,/u01/app/oracle/oradata/AYUCDB/6C4B2D3386C20FB8E055000000000001/datafile/TBS_TTS2.267.979841605 logfile=imp_tts_users.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "AYSHIBAT"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Tue Jun 26 19:11:05 2018 elapsed 0 00:00:13
7. PDBに接続してデータを確認
PDBに接続してデータを確認します。見事にデータが転送されています。
やったぜ彡(^)(^)
-- 12cR2 sqlplus /nolog CONNECT AYSHIBAT/xxxxxxxx@PDB01 SHOW CON_NAME; COLUMN OWNER FORMAT A20; COLUMN SEGMENT_NAME FORMAT A20; COLUMN BYTES FORMAT 999,999,999; COLUMN TABLESPACE_NAME FORMAT A20; SELECT OWNER, SEGMENT_NAME, BYTES, TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME IN ('TBS_TTS1', 'TBS_TTS2'); SELECT COUNT(*) FROM TBL_TEST_TTS1; SELECT COUNT(*) FROM TBL_TEST_TTS2; SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 26 19:14:27 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected. CON_NAME ------------------------------ PDB01 OWNER SEGMENT_NAME BYTES TABLESPACE_NAME -------------------- -------------------- ------------ -------------------- AYSHIBAT TBL_TEST_TTS2 196,608 TBS_TTS2 AYSHIBAT TBL_TEST_TTS1 196,608 TBS_TTS1 COUNT(*) ---------- 10000 COUNT(*) ---------- 10000
8. 表領域の READ ONLY状態を解除
表領域のREAD ONLY状態を解除します。ちな移行元(今回は11gR2)のREAD ONLY解除は、
データファイル書き出しが終わった時点で実施してOKですやで彡(ﾟ)(ﾟ)
-- 11gR2 SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME IN ('TBS_TTS1', 'TBS_TTS2'); ALTER TABLESPACE TBS_TTS1 READ WRITE; ALTER TABLESPACE TBS_TTS2 READ WRITE; SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME IN ('TBS_TTS1', 'TBS_TTS2'); TABLESPACE_NAME STATUS ------------------------------ --------- TBS_TTS1 READ ONLY TBS_TTS2 READ ONLY Tablespace altered. Tablespace altered. TABLESPACE_NAME STATUS ------------------------------ --------- TBS_TTS1 ONLINE TBS_TTS2 ONLINE -- 12cR2 SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME IN ('TBS_TTS1', 'TBS_TTS2'); ALTER TABLESPACE TBS_TTS1 READ WRITE; ALTER TABLESPACE TBS_TTS2 READ WRITE; SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES WHERE TABLESPACE_NAME IN ('TBS_TTS1', 'TBS_TTS2'); TABLESPACE_NAME STATUS ------------------------------ --------- TBS_TTS1 READ ONLY TBS_TTS2 READ ONLY Tablespace altered. Tablespace altered. TABLESPACE_NAME STATUS ------------------------------ --------- TBS_TTS1 ONLINE TBS_TTS2 ONLINE
9. まとめ
特別な手順は無く、11gR2 ⇒ 12cR2(PDB) の
TTS(トランスポータブル表領域)が実行できました彡(^)(^)
リアルな環境を考慮すると、5. のデータファイル転送(コピー)のところで
最も時間が掛かるのですが、一旦中間ファイルに書き出す今回のやり方よりも、
中間ファイルを介さずに直接転送する方法も有るようなので、
詰める余地が有りますやね彡(ﾟ)(ﾟ)
