スライド 1

Transcription

1 1

2 第 106 回夜な夜な! なにわオラクル塾 今さら聞けない データロード Export / Import 日本オラクル株式会社テクノロジー製品事業統括本部支社ソリューション本部 2013 年 07 月 10 日 2

3 以下の事項は 弊社の一般的な製品の方向性に関する概要を説明するものですまた 情報提供を唯一の目的とするものであり いかなる契約にも組み込むことはできません以下の事項は マテリアルやコード 機能を提供することをコミットメント ( 確約 ) するものではないため 購買決定を行う際の判断材料になさらないで下さいオラクル製品に関して記載されている機能の開発 リリースおよび時期については 弊社の裁量により決定されます Oracle と Java は Oracle Corporation 及びその子会社 関連会社の米国及びその他の国における登録商標です文中の社名 商品名等は各社の商標または登録商標である場合があります 3

4 Program Agenda Oracle Databaseのデータ ローディング Export/Import Oracle Database からのCSVアンロード 知っておきたい便利な使い方 まとめ 4

5 Oracle Database のデータ ローディング 5

6 Oracle Database のデータ ローディング データ ロードの種類 中間ファイルを利用するもの exp/imp ユーティリティ DataPump(expdp/impdp) ユーティリティ SQL*Loader ユーティリティ 外部表機能 トランスポータブル表領域機能 ( ソース側のデータベースは Enterprise Edition 必須 ) ネットワークを利用するもの 分散処理機能 ( データベース リンク + SQL) Oracle Data Integrator (ETL ツール ) Oracle GoldenGate( リアルタイム レプリケーション ツール ) ( リアルタイムにデータ連携 データ移行 データベース アップグレード等で利用 ) 6

7 Oracle Database のデータ ローディング 主にテキスト ファイルを利用するもの 外部ファイルのデータを Oracle Database の表に格納 ユーティリティ (SQL*Loader) の利用 Oracle Database の機能 ( 外部表 ) を利用 7

8 SQL*Loader の概要 外部ファイル Oracle Database に付属しているユーティリティのひとつ SQL*Loader の制御ファイルを用意することで 外部ファイルからの柔軟なローディングをおこないます SQL*Loader Oracle Database 表 制御ファイル 外部ファイルの場所 ファイル形式 ( 固定長 可変長 ) データの挿入先となる表 外部ファイル内のデータと表カラムの紐付け ログ ファイル 廃棄ファイル 不良ファイル SQL*Loader 実行ログを格納 ( ロード件数 エラー情報など ) 条件等が合わず ローディングされなかったレコードを格納 エラーによりローディングされなかったデータを格納 8

9 SQL*Loader の実行時モード従来型パス モード / ダイレクト パス モード 柔軟性 高速性に応じた実行モードを用意 柔軟 ) 従来型パス モード 内部的に INSERT 文を作成し INSERT を実行 高速 ) ダイレクト パス モード 内部的に INSERT を実行しません ブロック (ORACLE_BLOCK=I/O の単位 ) イメージを生成し 物理的に直接書込みます (= ダイレクト ロード ) いくつかの制限事項があります 表ロック取得 9

10 SQL*Loader のパラレル処理 パラレル ダイレクト パス 同一の表もしくはパーティション表の同一パーティションに対して同時にダイレクト パス ロードを実行できます あらかじめ準備が必要です SQL*Loader の同時実行数分 外部ファイル ( データ ) を分割 外部ファイルにあわせて制御ファイルを作成 ( もしくは実行時にパラメータ DATA で外部ファイルを指定 ) 実行時にパラメータ PALALLEL=TRUE を指定します 10

11 SQL*Loader 使用例 制御ファイルの作成 外部ファイルのフォーマット ロード先の表の情報をもとに制御ファイルを作成 外部ファイル :emp.dat 1001,ichiro, ,SALESMAN,junk, ,jiro, ,ANALYST,junk, ,saburo, ,MANAGER,junk,30 SQL> DESCRIBE emp 名前 NULL? 型 EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) emp 表の定義 カンマ区切りの可変長 日付は 8 桁の数字列 (YYYYMMDD) 表のカラムすべてと 1:1 に紐付けできない 表にロードすべきでないデータ junk が存在する LOAD DATA 制御ファイル :emp.ctl INTO TABLE emp APPEND FIELDS TERMINATED BY ',' ( empno, ename, hiredate DATE 'YYYYMMDD', job, junk FILLER, deptno ) 11

12 SQL*Loader 使用例 実行例 従来型パス モード % sqlldr scott/xx CONTROL=emp.ctl DATA=emp.dat ダイレクト パス モード % sqlldr scott/xx CONTROL=emp.ctl DATA=emp.dat DIRECT=true パラレル ダイレクト パス モード % sqlldr scott/xx CONTROL=emp.ctl DATA=emp1.dat LOG=emp1.log DIRECT=true PARALLEL=true & % sqlldr scott/xx CONTROL=emp.ctl DATA=emp2.dat LOG=emp2.log DIRECT=true PARALLEL=true & 事前に主キーを無効にしておくか パラメータ SKIP_INDEX_MAINTENANCE = true をセットします 12

13 SQL*Loader のロード方式性能比較 従来型パス モード vs ダイレクト パス モード ダイレクト パス モードは従来型パス モードに比べ非常に高速 従来型パス ロード 1.00 ダイレクト パス ロード emp 表 ( カラム構成が単純 ) へのロード (10,000,000 件 ) の比較例 ( 従来型パス ロードを 1 としたときの相対時間 ) 13

14 外部表の概要 Oracle Database 内に存在していない表 アクセス ドライバを通じて Oracle Database から読取り専用のアクセスをおこないます ORACLE_LOADER ORACLE_DATAPUMP 外部表への DML(INSERT/UPDATE/DELETE) 操作 索引付けはできませんが ビューやシノニムを作成できます 外部表へのアクセスはバッファ キャッシュを経由しません ( 後述 ) 14

15 外部表のアクセス ドライバそれぞれのアクセス ドライバの利用イメージ ORACLE_LOADER ORACLE_DATAPUMP Oracle Database 相互で利用 15

16 外部表のアクセス ドライバ ORACLE_LOADER と ORACLE_DATAPUMP ORACLE_LOADER 外部ファイルはテキスト データ ファイルのみ 圧縮されたテキスト データ ファイルも利用可能 PREPROCESSOR 指定 ORACLE_DATAPUMP 当アクセス ドライバでUNLOADしたファイルを利用 外部ファイルはプラットフォーム非依存のバイナリ ファイル DATAPUMP(expdp) のdmpファイルは利用できません UNLOADで生成した外部ファイルは Oracle Database 相互のデータ交換で利用します 当アクセス ドライバの機能で外部ファイルの圧縮 暗号化が可能 ORACLE_LOADERアクセス ドライバに比べ高速 16

17 外部表の使用例 使用前の準備 外部表を置く OS 上のディレクトリを決め Oracle Database のディレクトリ オブジェクトを作成します % sqlplus / as sysdba SQL> CREATE DIRECTORY ext_tbls AS '/app/oracle/admin/orcl/ext_tbls'; 外部表を作成する Oracle Database 上のユーザに対し ディレクトリ オブジェクトの権限を付与します SQL> GRANT READ ON DIRECTORY ext_tbls TO scott; SQL> GRANT WRITE ON DIRECTORY ext_tbls TO scott; 17

18 外部表の使用例 ORACLE_LOADER アクセス ドライバを使用した定義 外部ファイル ( テキスト ファイル ) の構造をもとに外部表を定義します 外部ファイル :ext_emp.dat 1001,ichiro, ,SALESMAN,junk, ,jiro, ,ANALYST,junk, ,saburo, ,MANAGER,junk,30 カンマ区切りの可変長 日付は 8 桁の数字列 (YYYYMMDD) 表のカラムすべてと 1:1 に紐付けできない 表にロードすべきでないデータ junk が存在する CREATE TABLE ext_emp ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), hiredate DATE, deptno NUMBER(2) )ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tbls ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ','( 外部ファイルの定義文 empno CHAR(4), ename CHAR(10), hiredate CHAR(8) date_format DATE mask "YYYYMMDD", job CHAR(9), junk CHAR(4), deptno CHAR(2) ) ) LOCATION ('ext_emp.dat') ); 18

19 外部表の使用例 ORACLE_LOADER アクセス ドライバを使用した実行 外部表への SELECT 文の実行 SQL> select * from ext_emp; EMPNO ENAME JOB HIREDATE DEPTNO ichiro SALESMAN jiro ANALYST saburo MANAGER 実行時のログファイル等の出力 指定ディレクトリに ログファイル 不良ファイル ( 対象データがあれば ) 廃棄ファイル ( 対象データがあれば ) が出力されます 19

20 外部表の使用例 ORACLE_DATAPUMPアクセス ドライバを使用した定義 外部表の作成の一環として外部ファイル ( ダンプファイル ) を作成します 作成済外部ファイルを利用して外部表を作成します CREATE TABLE ext_emp_ul ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY ext_tbls LOCATION ('ext_emp_ul.dmp') ) AS SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp ; 外部ファイル作成 CREATE TABLE ext_emp ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY ext_tbls LOCATION ('ext_emp_ul.dmp') ) ; 外部ファイル利用 20

21 外部表の前処理 ORACLE_LOADER アクセス ドライバの PREPROCESSOR 句 前処理プログラムを使用することで アクセス ドライバがサポートしていない形式のファイルを外部ファイルとして利用できます 圧縮された外部ファイルを解凍 前処理プログラムを置くディレクトリ オブジェクトの作成 権限付与を行います % cd /app/oracle/admin/orcl/exec_dir % ln -s /bin/zcat % sqlplus / as sysdba SQL> CREATE DIRECTORY exec_dir AS '/app/oracle/admin/orcl/exec_dir'; SQL> GRANT READ ON DIRECTORY exec_dir TO scott; SQL> GRANT EXECUTE ON DIRECTORY exec_dir TO scott; 外部ファイルの定義文 CREATE TABLE ext_emp ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), hiredate DATE, deptno NUMBER(2) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tbls ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE PREPROCESSOR exec_dir:'zcat' FIELDS TERMINATED BY ','(... ) ) LOCATION ('ext_emp.dat.gz') ); 21

22 外部表を利用したデータ ローディング DML 文 (INSERT / UPDATE / MERGE / DELETE) をそのまま使用します アクセス ドライバ種別も意識せず 透過的に利用できます INSERT INTO emp SELECT * FROM ext_emp; 外部表 SQL*Loader と同様 ダイレクト ロードも可能です SQL*Loader のダイレクト パスと同様 HWM より後ろのブロックを使用します INSERT /*+ APPEND */ INTO emp SELECT * FROM ext_emp; 外部表 ロード処理を PL/SQL でそのままバッチ処理の一部として取り込む事ができます 22

23 参考 )DML エラー ロギング INSERT UPDATE MERGE DELETE 文で利用可能 これまでは大量の行を対象とした単一の DML にエラーが発生すると処理のすべてがロールバックされていました 上記 DML に ERROR LOGS 句をつけることで利用します INSERT INTO emp (empno, ename, dptno, sal) ( SELECT empno, ename, dptno, sal FROM ext_emp ) LOG ERRORS INTO ERR$_EMP ('WEEKLY_BATCH') REJECT LIMIT 50; エラー ロギング表 ( 上記の ERR$_EMP) は DBMS_ERRLOG パッケージで作成 実行 DML とエラーロギング表への書き込みトランザクションは分離 DML 操作に失敗したデータを記録する為のものであり すべてのエラーを書き込むものではありません例 ) ORA-01653( 領域不足 ) ORA

24 参考 ) エラー ロギング表の例 INSERT INTO emp (empno,ename,dptno,sal) (SELECT empno,ename,dptno*1000,sal FROM ext_emp 故意に桁あふれ WHERE empno = 1) LOG ERRORS INTO ERR$_EMP ('WEEKLY_BATCH') REJECT LIMIT 50; UPDATE emp SET dptno = dptno*1000 WHERE empno = 20 LOG ERRORS INTO ERR$_EMP ('WEEKLY_BATCH2') REJECT LIMIT 50; 故意に桁あふれ INSERT でエラー発生 対象となるROWID UPDATE でエラー発生 24

25 外部表のアクセス ドライバ性能比較 ORACLE_LOADER vs ORACLE_DATAPUMP ORACLE_DATAPUMP で利用する外部ファイルはバイナリであり 型変換がほとんど発生しない Oracle Database 間のデータ連携では ORACLE_DATAPUMP アクセス ドライバの利用を推奨 ORACLE_LOADER 1.00 ORACLE_DATAPUMP emp 表 ( カラム構成が単純 ) へのロード (10,000,000 件 ) の比較例 (ORACLE_LOADER アクセス ドライバを 1 としたときの相対時間 ) 25

26 SQL*Loader と外部表の比較 ステージング表の利用 業務アプリケーションが利用している表にそのままデータをロードすることはほとんどありません 通常 ステージング表 (*1) に一度データをロードした後 エラーチェックや変換処理を行ったあと 業務アプリケーション用の表にロードします 外部表を利用すると 多くの場合でステージング表にロードする工程を省くことができます SQL*Loader 利用 ( ステージング表使用 ) 外部表利用 ( ステージング表未使用 ) (*1): ステージング表とは データの変換処理やロードの準備の為に設けた一時的な中間表のこと 26

27 SQL*Loader と外部表の比較 バッファ キャッシュの利用 ステージング表として利用している永続表への問い合わせ処理は基本的に Oracle Database のバッファ キャッシュを経由します 2 回目以降のアクセスは高速化できます 必要に応じて 既存のキャッシュを破棄する場合があります 外部表への問い合わせ処理は Oracle Database のバッファ キャッシュを経由しません 既存のキャッシュを破棄せずに処理をおこなうことができます 複数回のアクセスはキャッシュがないため非効率となる場合があります 複数回アクセスする場合はステージング表にロードします 27

28 SQL*Loader と外部表の比較 外部表のパラレル処理 外部表の参照の場合 永続表と同様にパラレル処理が可能 テーブルの属性としてパラレル度数を設定 セッション単位で設定 (ALTER SESSION) ヒントによる設定 ALTER SESSION FORCE PARALLEL QUERY; ALTER SESSION FORCE PARALLEL DDL; INSERT /*+ APPEND */ INTO emp SELECT * FROM ext_emp; 外部表を使用したパラレル処理の例 ORACLE_LOADER アクセス ドライバは制約があるので注意 文字の境界が文字列中の任意のバイトで始まり 境界を判断できないマルチバイト キャラクタ セット (SJIS EUC) を含む可変長データはパラレル処理が出来ません (UTF-8 は問題なし ) SQL*Loader は事前に外部ファイルを分割する必要があるため 手間がかかります 28

29 SQL*Loader と外部表の比較 SQL*Loader と外部表の使い分け SQL*Loader に適した処理 ネットワークを介したデータのロード 複合オブジェクト リレーショナル データの任意のロード セカンダリ ファイルを使用したLOBおよびコレクションのロード 定常的ではない 一時的なローディング ( 手早い ) 基本的には外部表を使用したロードをおこないます 任意のSQL 関数を利用した変換処理 ダイレクト パス インサート パラレル ロード ( ファイル分割不要 ) 圧縮済ファイルの使用 29

30 参考 )SQL*Loader による LOB のロード LOB 値を別ファイルから読み込むことができます LOAD DATA 制御ファイル INFILE test.dat INTO TABLE lob_tab TRUNCATE FIELDS TERMINATED BY WHITESPACE OPTIONALLY ENCLOSED BY '"' ( id integer external, ext_fname FILLER CHAR(10), blob_col lobfile(ext_fname) terminated by EOF ) 1 disk.bmp 2 book.bmp カレント フォルダに存在すること データ ファイル :test.dat 30

31 Export/Import 31

32 Export/Import とは データベース間のデータの移動を可能にします データベース間のデータの移動を可能にしています エクスポート エクスポート対象のオブジェクトを抽出し OS 上のファイルに書き込まれます 通称 この OS ファイルのことをダンプ ファイルと呼んでいますこのダンプファイルはバイナリ形式であり エクスポートインポートユーティリティのみで読み書きがなされます FTP を使用して別サイトに転送 または物理的に移送 ( テープの場合 ) できます インポート ダンプ ファイルからオブジェクトの定義および表データを読み込みます 32

33 Export/Import の用途論理バックアップの取得 Export ユーティリティで論理バックアップを取得します Import ユーティリティを使用して 再インポート可能です Backup Database Database 論理バックアップは 様々な状況で物理バックアップを補足するために役立ちますただ 論理バックアップをリストアするためには物理バックアップを再構築して Oracle サーバーで使用できるようにする必要がありますよって 論理バックアップは 物理バックアップを補うために使用する事を推奨します 33

34 Export/Import の用途データベースのバージョンアップ ハードウェアが老朽化してきたから そろそろハードウェアのリプレイスをしないと このタイミングで Oracle Database もバージョンアップしよう! バージョンアップするとなると データを移行する必要があるな Export/Import でデータを移行しよう! 34

35 Export/Import の種類 オリジナルの Export/Import exp および imp コマンドで起動するオリジナルのエクスポート ユーティリティおよびインポート ユーティリティです Oracle Database11g では非推奨 ( 原則としてサポートされなくなりました ) Data Pump Oracle Database10g から搭載された expdp および impdp コマンドで起動するエクスポート ユーティリティおよびインポート ユーティリティです データのロード アンロードを高速に行うことができます 35

36 オリジナルの Export/Import 概要図 Export Utility EXP Oracle Net Import Utility IMP xxx.dmp Oracle Net Oracle Database ダンプ ファイルはクライアント側に格納されます 36

37 Data Pump 概要図 Export Utility EXPDP Import Utility IMPDP Oracle Net ダンプ ファイルはサーバー側に格納されます Oracle Database Oracle Net PL/SQL Package Access Driver xxx.dmp EXPDP/IMPDP の実行エンジン 37

38 オリジナルと Data Pump の違い オリジナルの Export/Import は 基本的にユーティリティ側で実行されるが Data Pump はデータベース サーバー側で実行されます サーバ側で実行する Data Pump のメリット パラレル化が可能 API により呼び出しが可能 ジョブ管理 状況監視が可能 停止 再開が可能 38

39 Export/Import の起動 次のいずれかの方法を使用して Export/Import の起動およびパラメータの指定ができます コマンドライン 対話方式モード (Oracle Enterprise Manager) 39

40 Export/Import を使用する前に catexp.sql または catalog.sql スクリプトの実行 スクリプトを実行すると データベースはエクスポートおよびインポート操作に備えて調整されます Export/Import を実行するデータベース上の SYS ユーザーで 1 回実行しておく必要があります 通常はデータベースの作成時に catalog.sql が実行されているので 改めて実行する必要はありません データ ディレクトリへの必要なエクスポート ビューおよびインポート ビューの作成 EXP_FULL_DATABASE ロールの作成 EXP_FULL_DATABASE および IMP_FULL_DATABASE ロールへのすべての必要な権限の割当て DBA ロールへの EXP_FULL_DATABASE および IMP_FULL_DATABASE の割当て インストールされている catexp.sql のバージョンの記録 40

41 Export/Import を使用する前に 十分なディスク領域の確認 実際に Export/Import を実行する OS 上のユーザーが エクスポート ファイルの書き込み先のディスク上またはテープ上に 十分な記憶領域があることを確認してください アクセス権限の確認 Export/Import を実行する際に接続するデータベース ユーザには データベースに対する CREATE SESSION 権限が必要ですまた 別のユーザーが所有する表をエクスポートする場合は EXP_FULL_ DATABASE ロールを付与されている必要があります 41

42 コマンドからの Export/Import の起動 次のコマンドから Export/Import を起動できます exp(imp) ユーザ名 / パスワードエクスポートするモードダンプファイル Ex. exp(imp) scott/tiger owner=oracle file=exp.dmp 表モード ユーザー モード 表領域モード 全データベース モード 42

43 Export オリジナル Export Utility EXP EXP で利用可能なモード exp および imp コマンドで起動するオリジナルのエクスポート ユーティリティ オリジナル モード機能説明パラメータ フルデータベース全体のエクスポート FULL=y ユーザー指定したユーザー全体のエクスポート OWNER=user[, ] テーブル指定したテーブル全体のエクスポート TABLES=table[, ] テーブルスペース指定したテーブルスペース全体のエクスポート TABLESPACES=tablespace[, ] フル モードでの注意点 SYSが所有するオブジェクト およびORDSYS CTXSYS MDSYS およびORDPLUGINSスキーマはエクスポートされません例 1: > exp scott/tiger full=y フルモード 例 2: > exp scott/tiger file=exp.dmp tables=(emp,dept) テーブルモード 43

44 エクスポートするオブジェクトを指定します オリジナル オブジェクトの指定制約 権限 索引 トリガーをエクスポートするか指定します 制約 :CONSTRAINTS COMMAND > EXP scott/tiger TABLES=(wendy.emp) CONSTRAINTS=n 権限 :GRANTS COMMAND > EXP scott/tiger TABLES=(wendy.emp) GRANT=n 索引 :INDEXES COMMAND > EXP scott/tiger TABLES=(wendy.emp) INDEXES=n トリガー :TRIGGERS COMMAND > EXP scott/tiger TABLES=(wendy.emp) TRIGGERS=n デフォルトはすべて y になります 44

45 データの一貫性を維持できるよう指定します オリジナル CONSISTENT エクスポート ユーティリティによって読み込まれたデータのある時点における一貫性を維持し exp コマンドの実行中に変更されないように指定します CONSISTENT 指定 COMMAND > EXP scott/tiger TABLES=(wendy.emp) CONSISTENT=n デフォルトはCONSISTENT=nです 注意 : ユーザー SYS として接続しているとき または AS SYSDBA を使用しているとき ( あるいはその両方 ) に実行するエクスポートでは CONSISTENT=y はサポートされません 45

46 2 人のユーザーによる更新時のイベントの順序 オリジナル 時系列 ユーザ A TABLE1 のエクスポート開始 TABLE1 のエクスポート終了 TABLE2 のエクスポート開始 ユーザ B TABLE1 TABLE2 の更新トランザクションのコミット CONSISTENT = n の場合 TABLE2 の更新は書き込まれますが TABLE1 の更新は書き込まれません CONSISTENT = y の場合 TABLE1 TABLE2 の更新ともに書き込まれません 読み取り一貫性の確保を実現エクスポート開始した時点の整合性が確保できている状態で データをエクスポートできます 46

47 ダイレクト パス エクスポートの使用を指定します DIRECT 従来型パス エクスポートに比べて非常に高速なダイレクト パス エクスポートの使用を指定します オリジナル COMMAND > EXP scott/tiger TABLES=(wendy.emp) DIRECT=y デフォルトは DIRECT=n です 注意 : 次の場合は ダイレクトエクスポートは使用できません 対話方式でのエクスポートの起動 表領域モード ( TRANSPORT_TABLESPACES=Y ) Query パラメータの指定 ダイレクト パス ロードの指定 47

48 従来型とダイレクト パス ロードの違い オリジナル 従来型パス ロードによるエクスポート 1 エクスポートの実行 2SELECT 文発行 評価バッファ 5 式の評価が終了すると エクスポート ファイルに書き出さます 3 データをディスクからバッファ キャッシュへ読み込み Oracle 専用のメモリ領域 4 行はクライアント プロセスにある評価バッファに転送します 48

49 従来型とダイレクト パス ロードの違い オリジナル ダイレクト パス ロードによるエクスポート 1 エクスポートの実行 評価バッファ 2 データをディスクからバッファ キャッシュへ読み込み Oracle 専用のメモリ領域 3 評価バッファをバイパスし エクスポートファイルに書き出されます 評価バッファをバイパスするため 従来型パス エクスポートに比べ非常に高速です 49

50 Import オリジナル Import Utility IMP imp コマンドで起動するオリジナルのインポート ユーティリティ オリジナル IMP では EXP で使用できたパラメータのほとんどを利用することができます IMP でパラメータを指定することにより EXP で作成したダンプファイルからさらに対象データを絞りこんでインポートすることが可能です COMMAND > imp scott/tiger tables=emp,dept COMMAND > imp scott/tiger owner=wendy ダンプファイルから emp 表と dept 表のみをインポート ダンプファイルから wendy ユーザーのオブジェクトをインポート 50

51 オブジェクト作成エラーの処理方法を指定します オリジナル IGNORE IGNORE=y に指定すると データベース オブジェクトの作成時に作成エラーが発生しても このエラーは無視され エラーはレポートされずに継続します COMMAND > IMP scott/tiger FILE=exp.dmp IGNORE=y エラーを無視するよう指定 デフォルトは IGNORE=n ですこの場合 オブジェクト作成エラーがログに記録 または表示されて インポートが停止します表がすでに存在する場合 IGNORE=y を指定すると 行は既存の表にインポートされます ( エラー / メッセージは出力なし ) IGNORE=n を指定すると エラーがレポートされ 表は行が挿入されないまま スキップされますまた 表に依存するオブジェクトは作成されません 51

52 Data Pump とは Oracle Database10g から導入されたユーティリティです Oracle Data Pump テクノロジを使用すると データおよびメタデータをデータベース間で非常に高速に移動できます 利用方法 expdp/impdp コマンド Oracle Enterprise Manager DBMS_DATAPUMP PL/SQL パッケージ その他 外部表などのエンジンとして内部利用 52

53 Data Pump のここがすごい! ダイレクトパス API の改善による処理速度向上 エクスポート : オリジナルの Export に比べて 2 倍高速 インポート : オリジナルの Import に比べて 15~40 倍高速 Oracle Data Pump を使用したら速くなった! Data Pump は 大量データを扱う大きなジョブ向き! Data Pump では マスター表を作成するためのオーバーヘッドによって小さなジョブは遅くなりますが 大量のデータを高速に処理することは 中 大規模なジョブにとって大きなメリットとなります! 53

54 パフォーマンス 検証環境 Sun Enterprise MHz * 4CPU 4GB Memory 検証結果 800 従来型エクスポートと Data Pump エクスポートの処理時間比較 秒 検証内容 索引の張られていないテーブル (53partitions21,000,000rows) を用いて Data Pump を使ったエクスポート インポートと従来のものとを比較する作成されるダンプファイルは 1.15GB 従来型インポートと Data Pump インポートの処理時間比較秒 exp expdp expdp(2p) expdp(4p) 0 imp impdp impdp(2p) impdp(4p) 従来エクスポートに比べ Data Pump は 2 パラレルで約 9 分の 1 の処理時間!! 従来インポートに比べ Data Pump は 4 パラレルで約 13 分の 1 の処理時間!! 54

55 Export/Import 出力先ディレクトリの指定 Data Pump Data Pump ではクライアントではなくサーバーにより書き込みが行われるため 処理を行うディレクトリ位置が特定されている必要があります ダンプファイルを出力するディレクトリに対して DB 上でディレクトリオブジェクトを作成します実行時には ディレクトリオブジェクトを DIRECTORY パラメータで指定します SQL > CREATE DIRECTORY DPUMP_DIR1 AS /home/oracle/oradata/dpump_dir ; SQL > GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO scott; COMMAND > expdp scott/tiger tables=emp,dept directory=dpump_dir1 パラメータでディレクトリを指定していない場合 OS 環境変数 DATA_PUMP_DIR なければディレクトリオブジェクト DATA_PUMP_DIR が参照されます環境変数の設定 COMMAND > set DATA_PUMP_DIR=DPUMP_DIR1; export DATA_PUMP_DIR COMMAND > expdp scott/tiger schemas=scott ディレクトリオブジェクト作成 権限付与 55

56 Export Data Pump Data Pump Export Utility EXPDP EXPDP で利用可能なモード オリジナルの export と同様の操作性をもったエクスポートユーティリティ モード機能説明パラメータ フルデータベース全体のエクスポート FULL=y スキーマ指定したスキーマ全体のエクスポート SCHEMAS=schema[, ] テーブル指定したテーブル全体のエクスポート TABLES=table[, ] テーブルスペース指定したテーブルスペース全体のエクスポート TABLESPACES=tablespace[, ] 例 1: > expdp scott/tiger full=y フルモード 例 2: > expdp scott/tiger dumpfile=exp.dmp tables=emp,dept テーブルモード 56

57 data pump によるデータの移動方法 データ ファイル コピーを使用したデータ移動 (Transportable Tablespace) ダイレクト パスを使用したデータ移動 外部表を使用したデータ移動 従来型パスを使用したデータ移動 ネットワーク リンク インポートを使用したデータ移動 NETWORK_LINKインポート パラメータ 注意 : データ ポンプでは 無効な一意索引を持つ表は ロードされませんデータを表にロードするには その索引を削除するかまたは再度有効にする必要があります 57

58 フィルタ処理を行います Data Pump INCLUDE / EXCLUDE フィルタ処理を行い エクスポートの対象となるオブジェクトを細かく設定することができます エクスポートの対象オブジェクトを INCLUDE パラメータで指定します COMMAND > expdp scott/tiger include=table COMMAND > expdp scott/tiger include=index エクスポートの対象から除外するオブジェクトを EXCLUDE パラメータで指定します COMMAND > expdp scott/tiger exclude=index: LIKE EMP% 空白等を区切り文字にしないためにダブルクオーテーションが必要ですが OS によってはエスケープ文字が必要となります従って 通常はパラメータを記述したファイルを読み込ませると便利です LIKE や IN を指定することも可能です INCLUDE と EXCLUDE パラメータは排他的で 同時に指定できません 58

59 データのフィルタ処理を行います Data Pump QUERY / SAMPLE フィルタ処理を行い エクスポートの対象となるデータを細かく設定することができます エクスポート対象表のセットから行のサブセットを選択できます 例 : 職種が SALESMAN で 給与が 1600 より少ない従業員のみをエクスポートする場合 COMMAND > expdp scott/tiger TABLES=emp QUERY= "WHERE job= 'SALESMAN ' and sal <1600 " サンプリングしてソース データベースからアンロードするデータ行の割合を指定できます 例 :SCOTT の EMP 表の 50% がエクスポートされます COMMAND > expdp scott/tiger tables=emp SAMPLE= SCOTT"."EMP":50 59

60 ファイルサイズを指定します Data Pump FILESIZE 各ダンプ ファイルがそのサイズを上限とし 置換変数 (%U) を持つテンプレートを指定していてダンプ ファイルを分割します サイズが 3MB のダンプ ファイルを設定する COMMAND > expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_3m.dmp FILESIZE=3MB 3MB でもすべてのエクスポート データを保持するのに十分でなかった場合 次のエラーが表示され ジョブは中止されます ORA-39095: Dump file space has been exhausted: Unable to allocate bytes ファイル指定に置換変数が含まれている場合は 新しいファイルが作成されます COMMAND > expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_3m%U.dmp FILESIZE=3MB キロバイト (KB) メガバイト (MB) ギガバイト (GB) を付けて指定する事もできます 60

61 フラッシュバック モードを指定します Data Pump FLASHBACK_SCN / FLASHBACK_TIME 指定したSCNもしくは時刻における一貫性を維持したデータのエクスポートが可能です ある時刻における一貫性を維持したデータをエクスポートするために FLASHBACK_TIME パラメータを指定します COMMAND > cat parfile.txt FLASHBACK_TIME= TO_TIMESTAMP( 2004/03/20 10:00, YYYY/MM/DD HH:MI ) COMMAND > expdp scott/tiger parfile=parfile.txt 時刻指定 TO_TIMESTAMP() で時刻を指定 エスケープ文字が必要となるので パラメータファイルで指定 ある SCN における一貫性を維持したデータをエクスポートするために FLASHBACK_SCN パラメータを指定します COMMAND > expdp scott/tiger flashback_scn= SCN 指定 61

62 パラレル エクスポートを指定します Data Pump 要 Enterprise Edition PARALLEL エクスポート処理をパラレルで行うことが可能です 並列度を PARALLEL パラメータで指定します 出力先ディレクトリをそれぞれ指定することも可能です COMMAND > expdp scott/tiger parallel=3 dumpfile=dpump_dir1:expdat%u.dmp, dpump_dir2%u.dmp COMMAND > ls lr dpump_dir1:.expdat01.dmp.expdat02.dmp dpump_dir2:.expdat01.dmp 生成されたダンプファイルセットの確認 置き換え変数 (%U) を指定しない場合 dumpfile のファイル数は parallel の値に合わせる必要があります指定している場合 指定されたファイル指定がラウンドロビンに使用されます 並列度の指定 62

63 ダンプファイルのサイズの見積もります Data Pump ESTIMATE ダンプファイルを生成せず 生成されるダンプファイルのサイズを見積もることが可能です 見積もりのモードを ESTIMATE パラメータで指定します パラメータ値には次のどちらかを設定します BLOCKS ブロックサイズにオブジェクトのブロック数をかけて見積もります ( 精度は高くありません ) STATISTICS 統計情報を元に見積もります 見積もりのみ出力する場合には ESTIMATE_ONLY=y を指定します COMMAND > expdp scott/tiger tables=emp,dept estimate=blocks estimate_only=y 63

64 見積もり実行例 Data Pump 実際にデータを作成し (40 万行 2GB) 見積もりの精度を計測しました dbms_stats はサンプル 100% でかけてます BLOCKS STATISTICS 確認 COMMAND > expdp scott/tiger tables=test estimate=blocks estimate_only=y Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA. estimated SCOTT". TEST" GB Total estimation using BLOCKS method: GB COMMAND > expdp scott/tiger tables=test estimate=statistics estimate_only=y Estimate in progress using STATISTICS method... Processing object type TABLE_EXPORT/TABLE/TBL_TABLE_DATA/TABLE/TABLE_DATA. estimated SCOTT". TEST" GB Total estimation using STATISTICS method: GB 約 1.948GB マスターテーブルの容量が見積もり COMMAND > ls l に入らないため その分は考慮する expdat.dmp 必要があります 64

65 Tips Oracle RAC 構成で data pump または外部表を使用するには ディレクトリ オブジェクトのパスがクラスタ ファイル システム上に存在するようにする必要があります この目的で使用できる共有ストレージが構成内にない場合にも並列処理が必要であれば CLUSTER=no パラメータを使用して すべてのワーカー プロセスを data pump ジョブが開始されたインスタンスのみに置くことができます ASM を使用可能にした場合のディレクトリ オブジェクトの使用方法 SQL> CREATE or REPLACE DIRECTORY dpump_dir as '+DATAFILES/ ; ログ ファイル用の個別のディレクトリ オブジェクトの作成 SQL> CREATE or REPLACE DIRECTORY dpump_log as '/homedir/user1/ ; 使用例 > expdp hr DIRECTORY=dpump_dir DUMPFILE=hr.dmp LOGFILE=dpump_log:hr.log 65

66 Import Data Pump Import Utility IMPDP 9i までのインポートツールと同様の操作性をもった新インポートユーティリティ IMPDP では EXPDP で使用できたパラメータのほとんどを利用することができます IMPDP でパラメータを指定することにより EXPDP で作成したダンプファイルからさらに対象データを絞りこんでインポートすることが可能です COMMAND > impdp scott/tiger tables=emp,dept COMMAND > impdp scott/tiger include=index ダンプファイルから emp 表と dept 表のみをインポート Data Pump ダンプファイルから索引のみをインポート 66

67 ネットワーク リンクを使用します Data Pump NETWORK_LINK データベース リンクを使用して リモートデータベースから直接ローカルデータベースにインポートを行うことができます 参考 : エクスポートのネットワーク リンク指定は リモートデータベースのデータをローカルにエクスポートする機能ですインポートの場合には直接ローカルデータベースに書き込むので ダンプファイルを作成しません 接続するリモートデータベースを NETWORK_LINK パラメータで指定します指定する値はデータベース リンク名です COMMAND > impdp scott/tiger tables=emp,dept directory=dpump_dir network_link=scott.jp.oracle.com DB LINKの指定 67

68 Oracle Database からの CSV アンロード 68

69 なぜ CSV でロード / アンロードしたいのか? RDBMS 製品間のデータ交換 Oracle Database から他社 RDBMS へ 他社 RDBMS から Oracle Database へ 業務処理 ( 本番環境 ) としてデータ交換を行なっている 検証 開発用にデータをベンダーに提供する 69

70 CSV ロード / アンロードに関する問題 CSV はフォーマット エラーが発生しやすい ローディングが行えず PoC が始まらない Oracle Database には CSV アンローダーが存在しない どのような手法でアンロードするかが性能に大きく影響する CSV フォーマット エラーが発生しやすい 70

71 CSV フォーマットとは 狭義では CSV = Comma-Separated Values [RFC4180] フィールド終端 :, レコード終端 : CRLF 広義では CSV = Character-Separated Values フィールド終端とレコード終端が特定の文字 狭義の CSV を使いがちだがほとんどのソフトウェアが広義の CSV に対応している 狭義の CSV はフォーマット エラーを起こしやすい 71

72 フォーマット エラーの例 データにフィールド終端が含まれている abc def 1,234 uvw xyz 7,890 abc,def,1,234 uvw,xyz,7,890 2 列に分割されてしまっている abc def uvw xyz

73 正しい対応例 終端記号をデータに含まれない文字にする 例 ) フィールド終端 : タブ (^I) abc^idef^i1,234 uvw^ixyz^i7,890 タブ区切りテキスト (TSV) は Microsoft Excel 含め 対応ソフトウェアが多いので利用しやすい 注意 : ユーザーが自由入力できるアプリケーション (textarea 要素を含む Web アプリケーションなど ) ではデータにタブが含まれることが少なくない 73

74 ORACLE_LOADER の場合の最適な対応例 終端記号にマイナーな ASCII 制御文字を使用する ORACLE_LOADER ドライバー (SQL*Loader 含む ) は ASCII 制御文字を含むあらゆる文字に対応できる 例 ) フィールド終端 : ユニット分離標識 (^_) レコード終端 : レコード分離標識 (^^) abc^_def^_1,234^^uvw^_xyz^_7,890 マイナーな ASCII 制御文字がデータに含まれている確率はほぼゼロ Vim では Ctrl + V Ctrl + ^ で ASCII 制御文字を入力可能 制御ファイルでは DELIMITED BY X'1E' のように 16 進数指定も可能 74

75 誤った対応例 エンクロージャでは解決できない エンクロージャ " を追加する "abc","def","1,234" データに " は含まれていないか? 含まれている エラー "abc","def","1'23"" データに " が含まれていないことが確実なのであれば フィールド終端を " にするだけで解決 abc"def"1,234 75

76 ORACLE_LOADER の場合の間違いやすい対応例 1 エンクロージャをつけても レコード終端はデータに含められない ORACLE_LOADER ドライバー (SQL*Loader 含む ) はエンクロージャを使ってもデータにレコード終端を含められない 例 ) フィールド終端 :, レコード終端 : 改行 エンクロージャ : abc, def, エラー : 2 番目の囲み文字列がありません 可変レコード形式 (VAR) を使った対応方法もあるが レコード終端を改行から別の ASCII 制御文字に変更したほうが簡単 76

77 ORACLE_LOADER の場合の間違いやすい対応例 2 エスケープ文字は使用できない ORACLE_LOADERドライバー (SQL*Loader 含む ) はエスケープ文字でフィールド終端とレコード終端を無効化できない例 ) フィールド終端 :, レコード終端 : 改行 abc,def,1,234 uvw,xyz,78 単なる文字として扱われる 90 abc def 1,234 uvw xyz

78 ORACLE_LOADER の場合の間違いやすい対応例 3 文字列にしても解決できない ORACLE_LOADER ドライバー (SQL*Loader 含む ) は文字 列 に対応できるが これで常に解決できるわけではない 例 ) フィールド終端 :,, abc,,def,,1,234 一見良さそう uvw,,xy,,,7,890 想定外の区切り uvw xy,7,890 区切りがずれている 78

79 正しいアプリケーション実装 終端記号にマイナーな ASCII 制御文字を使用できないシステムでは少なくとも 2 個の印字可能文字は データに含まれないように RDBMS からの入力時 / RDBMS からの出力時に置き換える ISO10646 文字実体参照 :,, SQL インジェクション対策に合わせて実装する 79

80 知っておきたい便利な使い方 80

81 複数の表を Export し データのみを Import したい 複数の表を Export し データのみを Import したいが 外部キーが多数あり 整合性制約に違反してしまう オリジナル / Data Pump 以下のように 関連する参照整合性制約を disable し Import 後に参照整合性制約を enable にすると エラーを解消できます COMMAND> expdp system/manager ; SQL> alter table emp disable constraint emp_no_fk ; 関連するすべての参照整合性約を disable に設定します COMMAND> impdp system/manager ; SQL> alter table emp enable constraints emp_no_fk ; 関連するすべての参照整合性約を enable に設定します 81

82 Export ダンプから一部データを取り出す方法 オリジナル / Data Pump 誤って一部データを delete し commit してしまった!! エクスポートのダンプ ファイルから取り出したい SQL> delete from oradirect.emp where emp_id=100; SQL> commit; 以下のように削除したデータをエクスポートのダンプ ファイルより取り出せます! COMMAND> impdp system/manager remap_schema=oradirect:dummy SQL> insert into oradirect.emp select * from dummy.emp where emp_id=100; SQL> drop table dummy.emp 注 ) エクスポートした時点のデータと削除したデータが一致するかは不明であるため この例の場合 emp_id=100 のデータに関して エクスポート時と誤って削除した時が同じであったことを前提としています 82

83 索引作成文を受け取る方法 - オリジナル - オリジナル Option デフォルト用途使用例 INDEXFILE なし索引作成文を受け取るファイルを指定します imp scott/tiger TABLES=(wendy.emp) INDEXFILE= index.txt エクスポート ファイルの CREATE TABLE 文もコメントとして含まれます! 83

84 さらに Data Pump ではすべての SQL 文を受け取れます Option デフォルト 用途 使用例 SQLFILE なし インポートが他のパラメータに基づいて実行するすべての SQL DDL の書込み先のファイルを指定します impdp scott/tiger TABLES=(wendy.emp) SQLFILE= index.txt 注意 : パスワードは SQL ファイルに含まれないことに注意してくださいたとえば 実行した DDL に CONNECT 文が含まれている場合 その文はコメントで置き換えられ スキーマ名のみが表示されます 例 :TEST_TABLE 表をインポートする際の SQLFILE を受け取る場合 impdp oradirect/oradirect dumpfile=test_table.dmp tables=test_table sqlfile=sqlfile.txt Data Pump 84

85 SQLFILE の一例 Data Pump さらに INSERT 文も確認する事ができます CREATE TABLE 文や CREATE INDEX 文を確認する事ができます 85

86 Data Pump 11g 新機能を活用! Data Pump 圧縮 COMPRESSION={ALL DATA_ONLY METADATA_ONLY NONE} NONE... エクスポート操作全体について圧縮が無効 METADATA_ONLY(default)... 全ての メタデータ が圧縮形式でダンプ ファイルへ書き込み ( 以下の操作は Advanced Compression オプションが必要 ) DATA_ONLY... 全ての データ が圧縮形式でダンプ ファイルへ書き込み ALL... データ メタデータともに圧縮 Data Pump データやメタデータを圧縮してダンプファイルにエクスポートできる Advanced Compression Option ( 例 ) スキーマを指定し メタデータ データともに圧縮してダンプ ファイル scott.dmp へエクスポート $ expdp scott/tiger DIRECTORY=dpump_dir DUMPFILE=scott.dmp COMPRESSION=ALL SCHEMAS=scott 86

87 Data Pump 11g 新機能を活用! Data Pump 暗号化 データやメタデータを暗号化してダンプファイルにエクスポートできる ENCRYPTION = {ALL DATA_ONLY ENCRYPTED_COLUMNS_ONLY METADATA_ONLY NONE} ALL: すべてのデータおよびメタデータについて暗号化が有効 DATA_ONLY: データのみが暗号化 ENCRYPTED_COLUMNS_ONLY: 暗号化された列のみが暗号化される METADATA_ONLY: メタデータのみが暗号化 NONE: データは暗号化されない ( 例 ) ダンプ ファイルでデータのみが暗号化されるエクスポート操作 $ expdp scott/tiger DIRECTORY=dpump_dir DUMPFILE=scott_enc.dmp SCHEMAS=scott ENCRYPTION=data_only ENCRYPTION_PASSWORD=tiger Data Pump Advanced Security Option 87

88 まとめ Oracle Database において 2 種類のテキスト ファイルからのデータ ロード方法があります SQL*Loader 外部表を使用したローディング SQL*Loaderは以前のバージョンから提供されており 馴染み深いのですが 現在は外部表の利用が主流です 外部表を利用することでデータ ロードをSQLで柔軟におこなえます エクスポート インポートの種類は2 種類 オリジナルのExport/ImportとData Pump Oracle Database 10gからはData Pumpを使用する Oracle Database 10g 以前のバージョンからデータを移行する場合は オリジナルのエクスポート インポートを使用する ダイレクト パス ロードで高速な移行を実現 Optionコマンドを使って 最適なExport/Importを実現 Advanced Compressionで圧縮して高速化を実現 セキュリティを高めたいなら Advanced Security 88

89 Appendix HWM を下げるオペレーション 89

90 参考 ) ダイレクト ロードと HWM ハイウォーターマーク (HWM) とは テーブルに割り当てたブロックの中で いままでデータが挿入されたことのある最後尾のブロック ハイウォーターマーク (High Water Mark): 高水位標 データ格納済 ブロック 空きブロック データ格納済 ブロック データ削除 空きブロック 空きブロック HWM HWM テーブルのフルスキャン時にはHWMまで読み取ります メリット : テーブルの大きさに対してデータ量が少ない場合 高速に検索できる デメリット : 大量削除などでHWM 以前の空きが多い場合 実レコード数に比較して時間がかかる テーブルのフルスキャン 空きブロック テーブルのフルスキャン 空きブロック HWM HWM 90

91 参考 ) ダイレクト ロードと HWM ダイレクト ロードの書込み ダイレクト ロードは HWM の後ろにデータを書き込みます ブロック イメージを作成し直接物理的に書き込む為 使用中のブロックを利用しません ダイレクト ロードは書込み速度は速いが 利用できない空き領域 (HWM よりも前の空き領域 ) がでてきます データ格納済 ブロック 空きブロック ダイレクト ロードによるデータ書込み開始ブロック HWM データ削除が頻繁に発生する表にダイレクト ロードを行う際はHWMを下げるような運用も検討します 91

92 HWM を下げるオペレーション 業務的に対象表にアクセスさせない状態で実施 1. 論理バックアップユーティリティ (EMP/IMP) + 元表削除 (DROP) 2. CREATE AS SELECT + TRUNCATE + INSERT SELECT 3. CREATE AS SELECT + 元表削除 (DROP) + RENAME 4. ALTER TABLE < 表名 > MOVE TABLESPACE < 表領域名 >) 索引等のメンテナンスも必須 オンライン状態で実施 1. オンライン表再定義 (Enterprise Edition 機能 ) 2. オンライン セグメント縮小 ( 標準機能 ) 92

93 HWM を下げるオペレーション オンライン セグメント縮小 オンライン セグメント縮小 業務でテーブルを利用中でもセグメント縮小が可能 自動セグメント領域管理 (ASSM) のみ利用可能 空き領域 HWM データの移動 HWM の移動 領域の解放 ALTER TABLE < 表名 > ENABLE ROW MOVEMENT; ALTER TABLE < 表名 > SHRINK SPACE[CASCADE]; 93

94 94

95 95