<Insert Picture Here>
夜な夜な! なにわオラクル塾
Presented By アシスト #48
OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。文中 の社名、商品名等は各社の商標または登録商標である場合があります。
Program Agenda
Oracle Databaseのデータ・ローディング
Export/Import
Oracle Database からのCSVアンロード
知っておきたい便利な使い方
まとめ
1
2
3
4
5
Oracle Databaseのデータ・ローディング
データ・ロードの種類
•
中間ファイルを利用するもの
•
exp/impユーティリティ、Data Pump（expdp/impdp）ユーティリティ
•
SQL*Loaderユーティリティ
•
外部表機能
•
トランスポータブル表領域機能 （ソース側のデータベースはEnterprise
Edition必須）
•
ネットワークを利用するもの
•
分散処理機能（データベース・リンク ＋ SQL）
•
Oracle Data Integrator （ETLツール）
•
Oracle GoldenGate（リアルタイム・レプリケーション・ツール）
（リアルタイムにデータ連携、データ移行、データベース・アップグレード等
で利用）
Oracle Databaseのデータ・ローディング
主にテキスト・ファイルの内容をロードする
•
外部ファイルのデータをOracle Databaseの表に格納
•
ユーティリティ（SQL*Loader）の利用
SQL*Loaderの概要
•
Oracle Databaseに付属しているユーティリティのひとつ
•
SQL*Loaderの制御ファイルを用意することで、
外部ファイルからの柔軟なローディングをおこないます
廃棄ファイル
不良ファイル
ログ・ファイル
制御ファ
イル
Oracle Database
表
SQL*Loader
外部ファ
イル
•外部ファイルの場所
•ファイル形式（固定長、可変長）
•データの挿入先となる表
•外部ファイル内のデータと表カラムの紐付け
•SQL*Loader実行ログを格納
（ロード件数、エラー情報など）
•条件等が合わず、ローディング
されなかったレコードを格納
•エラーによりローディング
されなかったデータを格納
SQL*Loaderの実行時モード
従来型パス・モード / ダイレクト・パス・モード
•
柔軟性、高速性に応じた実行モードを用意
•
柔軟） 従来型パス・モード
•
内部的にINSERT文を作成し
INSERTを実行
•
高速） ダイレクト・
パス・モード
•
内部的に
INSERTを実行しません
•
ブロック(ORACLE_BLOCK=I/Oの単位）イメージを生成し、
物理的に
直接
書込みます（=
ダイレクト・ロード
）
•
いくつかの制限事項があります
表ロック取得
SQL*Loaderのパラレル処理
パラレル・ダイレクト・パス
•
同一の表もしくはパーティション表の同一パーティションに対して
同時にダイレクト・パス・ロードを実行できます
•
あらかじめ
準備
が必要です
•
SQL*Loaderの同時実行数分、外部ファイル（データ）を
分割
•
外部ファイルにあわせて制御ファイルを作成（もしくは実行時にパラメータ DATA で外部
ファイルを指定）
•
実行時にパラメータ PALALLEL=TRUE を指定します
SQL*Loader使用例
制御ファイルの作成
•
外部ファイルのフォーマット、ロード先の表の情報をもとに制御ファイルを作成
emp表の定義
制御ファイル：emp.ctl
LOAD DATA
INTO TABLE emp
APPEND
FIELDS TERMINATED BY ','
(
empno,
ename,
hiredate DATE 'YYYYMMDD',
job,
junk FILLER,
deptno
)
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)
1001,ichiro,20001025,SALESMAN,junk,10 1002,jiro,20011125,ANALYST,junk,20 1003,saburo,20021225,MANAGER,junk,30外部ファイル：emp.dat
・カンマ区切りの可変長 ・日付は8桁の数字列（YYYYMMDD） ・表のカラムすべてと１：１に紐付けできない ・表にロードすべきでないデータ「junk」が存在する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 &
事前に主キーを無効にしておくか、
SQL*Loaderのロード方式性能比較
従来型パス・モード vs ダイレクト・パス・モード
•
ダイレクト・パス・モードは従来型パス・モードに比べ非常に高速
0.19
1.00
0.00 0.20 0.40 0.60 0.80 1.00ダイレクト・パス・ロード
従来型パス・ロード
emp表（カラム構成が単純）へのロード（10,000,000件）の比較例外部表
外部ファイルにあるデータをデータベース内に表があるかのようにアクセスする
•
既存のSQL*Loader機能を補足する機能
•
データベースに表がある場合と同様に、外部ソースのデータにア
クセス可能
•
テキストファイル or バイナリファイル
外部
ファイ
ル
Oracle Database
表
外部表
INSERT
INTO
SELECT
任意のSQL関数を利用した変換処理
ダイレクト・パス・インサート
パラレル・ロード
圧縮済ファイルの使用（11g R2以降）
利用例：ステージング表を作成しないデータロード
テキストファイル or バイナリファイル
アクセス・ドライバの利用イメージ
•
ORACLE_LOADER
•
外部ファイルはテキスト・データ・ファイルのみ
•
圧縮されたテキスト・データ・ファイルも利用可能
•
PREPROCESSOR指定
•
ORACLE_DATAPUMP
•
当アクセス・ドライバでUNLOADしたファイルを利用
•
外部ファイルは
プラットフォーム非依存
の
バイナリ・ファイル
•
Data Pump(expdp)のdmpファイルは利用できません
•
ORACLE_LOADERアクセス・ドライバに比べ高速
•
外部ファイルの圧縮、暗号化が可能
•
UNLOADで生成した外部ファイルは、
Oracle Database相互のデータ交換で利用します
外部表の使用例(1)
使用前の準備
•
外部表を置くOS上のディレクトリを決め、Oracle Databaseの
ディレクトリ・オブジェクトを作成します
•
外部表を作成するOracle Database上のユーザに対し、
ディレクトリ・オブジェクトの権限を付与します
% sqlplus / as sysdba
SQL> CREATE DIRECTORY
ext_tbls
AS '/app/oracle/admin/orcl/ext_tbls';
SQL> GRANT
READ
ON DIRECTORY ext_tbls TO scott;
SQL> GRANT
WRITE
ON DIRECTORY ext_tbls TO scott;
外部表の使用例(2)
ORACLE_LOADERアクセス・ドライバを使用した定義
•
外部ファイル（テキスト・ファイル）の構
造をもとに外部表を定義します
1001,ichiro,20001025,SALESMAN,junk,10
1002,jiro,20011125,ANALYST,junk,20
1003,saburo,20021225,MANAGER,junk,30
外部ファイル：ext_emp.dat
•カンマ区切りの可変長 •日付は8桁の数字列（YYYYMMDD） •表のカラムすべてと１：１に紐付けできない •表にロードすべきでないデータ「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') );
外部ファイルの
定義文
外部表の使用例(3)
ORACLE_LOADERアクセス・ドライバを使用した実行
•
外部表へのSELECT文の実行
•
実行時のログファイル等の出力
SQL> select * from ext_emp;
EMPNO ENAME JOB HIREDATE DEPTNO
--- --- --- --- ---
1001 ichiro SALESMAN 00-10-25 10
1002 jiro ANALYST 01-11-25 20
1003 saburo MANAGER 02-12-25 30
指定ディレクトリに
・ログファイル
・不良ファイル（対象データがあれば）
・廃棄ファイル（対象データがあれば）
が出力されます
外部表の使用例(4)
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
')
)
外部表の前処理
ORACLE_LOADERアクセス・ドライバのPREPROCESSOR句
•
前処理プログラムを使用することで、アクセ
ス・ドライバがサポートしていない形式のファ
イルを外部ファイルとして利用できます
•
圧縮された外部ファイルを解凍
•
前処理プログラムを置くディレクトリ・オブジェ
クトの作成、権限付与を行います
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
')
);
外部ファイル
の定義文
% 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;
外部表を利用したデータ・ローディング
•
DML文（INSERT / UPDATE / MERGE / DELETE）をそのまま使用します
•
アクセス・ドライバ種別も意識せず、透過的に利用できます
•
SQL*Loaderと同様、ダイレクト・ロードも可能です
•
SQL*Loaderのダイレクト・パスと同様、HWMより後ろのブロックを使用します
•
ロード処理をPL/SQLでそのままバッチ処理の一部として取り込む
事ができます
INSERT INTO emp SELECT * FROM
ext_emp
;
外部表
SQL*Loaderと外部表の比較
ステージング表の利用
•
業務アプリケーションが利用している表にそのままデータをロードすることは
ほとんどありません
•
通常、ステージング表(*1)に一度データをロードした後、エラーチェックや変換処理
を行ったあと、業務アプリケーション用の表にロードします
•
外部表を利用すると、多くの場合でステージング表にロードする工程を
省くことができます
SQL*Loader利用
（ステージング表使用）
外部表利用
（ステージング表未使用）
SQL*Loaderと外部表の比較
バッファ・キャッシュの利用
•
ステージング表として利用している永続表への問い合わせ処理は基本的にOracle
Databaseのバッファ・キャッシュを経由します
•
2回目以降のアクセスは高速化できます
•
必要に応じて、既存のキャッシュを破棄する場合があります
•
外部表への問い合わせ処理はOracle Databaseのバッファ・キャッシュを経由しません
•
既存のキャッシュを破棄せずに処理をおこなうことができます
•
複数回のアクセスはキャッシュがないため非効率となる場合があります
→ 複数回アクセスする場合はステージング表にロードします
SQL*Loaderと外部表の比較
外部表のパラレル処理
•
外部表の参照の場合、永続表と同様にパラレル処理が可能
•
テーブルの属性としてパラレル度数を設定
•
セッション単位で設定(ALTER SESSION)
•
ヒントによる設定
•
ORACLE_LOADERアクセス・ドライバは制約があるので注意
•
文字の境界が文字列中の任意のバイトで始まり、境界を判断できないマルチ
バイト・キャラクタ・セット（SJIS、EUC）を含む可変長データはパラレル処理が出
来ません（UTF-8は問題なし）
•
SQL*Loaderは事前に外部ファイルを分割する必要があるため、手間がか
かります
ALTER SESSION FORCE PARALLEL QUERY;
ALTER SESSION FORCE PARALLEL DDL;
INSERT /*+ APPEND */ INTO emp SELECT * FROM ext_emp;
外部表を使用した
パラレル処理の例
SQL*Loaderと外部表の比較
SQL*Loaderと外部表の使い分け
•
SQL*Loaderに適した処理
•
ネットワークを介したデータのロード
•
複合オブジェクト・リレーショナル・データの任意のロード
•
セカンダリ・ファイルを使用したLOBおよびコレクションのロード
•
定常的ではない、一時的なローディング（手早い）
•
基本的には外部表を使用したロードをおこないます
•
任意のSQL関数を利用した変換処理
•
ダイレクト・パス・インサート
•
パラレル・ロード(ファイル分割不要)
•
圧縮済ファイルの使用
外部表のアクセス・ドライバ性能比較
ORACLE_LOADER vs ORACLE_DATAPUMP
•
ORACLE_DATAPUMPで利用する外部ファイルは
バイナリであり、型変換がほとんど発生しない
•
Oracle Database間のデータ連携ではORACLE_DATAPUMPアクセス・ドライ
バの利用を推奨
0.84
1.00
0.00 0.20 0.40 0.60 0.80 1.00ORACLE_DATAPUMP
ORACLE_LOADER
emp表（カラム構成が単純）へのロード（10,000,000件）の比較例（ORACLE_LOADERアクセス・ドライバを1としたときの相対時間）EXPORT/IMPORT
Export/Importとは
データベース間のデータ の移動を可能にします
•
データベース間のデータ の移動を可能にしています
エクスポート
インポート
• 通称、このOSファイルのことを
ダンプ・ファイルと呼
んでいます
このダンプファイルはバイナリ形式であり、エクスポー
トインポート
ユーティリティのみで読み書きがなされます
• FTPなどを使用して別サイトに転送、または物理的
に移送（テープの場合）できます
エクスポート対象のオブジェクトを抽出し、
OS上のファイル
に書き込まれます
ダンプ・ファイルからオブジェクトの定
義および表データを読み込みます
Export/Importの用途
論理バックアップの取得
Database
Backup
Database
論理バックアップは、様々な状況で物理バックアップを補足するために役立ちます
ただ、論理バックアップをリストアするためには物理バックアップを再構築して、
Oracleサーバーで使用できるようにする必要があります
よって、論理バックアップは、物理バックアップを補うために使用する事を推奨します
Exportユーティリティで論理バックアッ
プを取得します
Importユーティリティを使用して、
再インポート可能です
Export/Importの用途
データベースのバージョンアップ
ハードウェアが老朽化してきたから、そろそ
ろハードウェアのリプレイスをしないと・・・・
このタイミングでOracle Database
も
バージョンアップ
しよう！
バージョンアップするとなると、
データを移行
する必要がある
な・・・
Export/Importでデータを移行しよう！
Export/Importの種類
•
オリジナルのExport/Import
•
expおよびimpコマンドで起動するオリジナルの
エクスポート・ユーティリティおよびインポート・ユーティリティです
•
Oracle Database11g では非推奨(原則としてサポートされなくなりました)
•
Data Pump
•
Oracle Database10gから搭載されたexpdpおよびimpdpコマンドで起動す
る
エクスポート・ユーティリティおよびインポート・ ユーティリティです
•
データのロード・アンロードを高速に行うことができます
オリジナルのExport/Import 概要図
Import Utility
IMP
Oracle Net
Export Utility
EXP
xxx.dmp
Oracle Database
Oracle Net
ダンプ・ファイル
はクライアント側
に格納されます
Data Pump 概要図
Oracle Net
PL/SQL Package Access Driver
Oracle Database
expdp/impdpの
実行エンジン
Import Utility
impdp
Oracle Net
Export Utility
expdp
xxx.dmp
ダンプ・ファイルは
サーバー側に
格納されます
コマンドからのExport/Importの起動
•
次のコマンドからExport/Importを起動できます
表モード
ユーザー・モード
表領域モード
全データベース・モード
exp（imp）
ユーザ名/パスワード
エクスポートするモード
ダンプファイル
Ex. exp(imp) scott/tiger owner=scott file=exp.dmp
Export – オリジナル –
モード
機能説明
パラメータ
フル
データベース全体のエクスポート
FULL=ｙ
ユーザー
指定したユーザー全体のエクスポート
OWNER=user[, …]
テーブル
指定したテーブル全体のエクスポート
TABLES=table[, …]
テーブルスペース
指定したテーブルスペース全体のエク
スポート
TABLESPACES=tablespace[, …]
EXPで利用可能なモード
例1: > exp scott/tiger full=y
例2: > exp scott/tiger file=exp.dmp tables=(emp,dept)
フルモード
テーブルモード
Export Utility
EXP
expおよびimpコマンドで起動する
オリジナルのエクスポート・ユーティリティ
フル・モードでの注意点
SYSが所有する オブジェクト、およびORDSYS、CTXSYS、 MDSYS および
ORDPLUGINSスキーマはエクスポートされません
Import – オリジナル –
Import Utility
IMP
impコマンドで起動する
オリジナルのインポート・ユーティリティ
COMMAND > imp scott/tiger tables=emp,dept
IMPでは、
EXPで使用できたパラメータのほとんどを利用することができます
IMPでパラメータを指定することにより、EXPで作成したダンプファイルからさら
に対象データを絞りこんでインポートすることが可能です
ダンプファイルからemp表と
dept表のみをインポート
COMMAND > imp scott/tiger owner=scott
ダンプファイルからscottユーザーの
オブジェクトをインポート
Data Pump とは
•
Oracle Database10gから導入されたユーティリティです
•
Data Pumpテクノロジを使用すると、
データおよびメタデータをデータベース間で非常に高速に
移動できます
利用方法
• expdp/impdpコマンド
• Oracle Enterprise Manager
• DBMS_Data Pump PL/SQLパッケージ
• その他、外部表などのエンジンとして内部利用
Data Pumpのここがすごい!
ダイレクトパスAPIの改善による処理速度向上
•
エクスポート：オリジナルのExportに比べて2倍高速
•
インポート：オリジナルのImportに比べて15～40倍高速
Data Pumpを使用したら
速くなった！
Data Pumpは、大量データを扱う大きなジョブ向き！
Data Pumpでは、マスター表を作成するためのオーバー
ヘッドによって小さなジョブは遅くなりますが、大量のデー
タを高速に処理することは、中・大規模なジョブにとって
大きなメリットとなります！
Data Pump
Export/Import – 出力先ディレクトリの指定
•
Data Pumpではクライアントではなくサーバーにより書き込みが行われるため、
処理を行うディレクトリ位置が特定されている必要があります
•
ダンプファイルを出力するディレクトリに対して、DB上でディレクトリオブジェクトを作成
します実行時には、ディレクトリオブジェクトを
DIRECTORYパラメータで指定
します
•
パラメータでディレクトリを指定していない場合、OS環境変数「DATA_PUMP_DIR」、
なければ ディレクトリオブジェクト「DATA_PUMP_DIR」が参照されます
Data Pump
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
権限付与
ディレクトリオブジェクト作成
環境変数の設定
COMMAND > set DATA_PUMP_DIR=DPUMP_DIR1; export DATA_PUMP_DIR
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
Export – Data Pump –
オリジナルのexportと同様の操作性をもった
エクスポートユーティリティ
モード
機能説明
パラメータ
フル
データベース全体のエクスポート
FULL=y
スキーマ
指定したスキーマ全体のエクスポート
SCHEMAS=schema[, …]
テーブル
指定したテーブル全体のエクスポート
TABLES=table[, …]
テーブルスペース
指定したテーブルスペース全体のエク
スポート
TABLESPACES=tablespace[, …]
expdpで利用可能なモード
例1: > expdp scott/tiger full=y
例2: > expdp scott/tiger dumpfile=exp.dmp tables=emp,dept
フルモード
テーブルモード
Export Utility
expdp
フィルタ処理を行います
エクスポートの対象から除外するオブジェクトを
EXCLUDEパラメータで指定
します
COMMAND > expdp scott/tiger exclude=index:¥”LIKE ¥’EMP%¥’¥”
LIKEやINを指定す
ることも可能です
※空白等を区切り文字にしないためにダブルクオーテーションが
必要ですが、OSによってはエスケープ文字が必要となります従っ
て、通常は
パラメータを記述したファイルを読み込ませる
と便利で
す
COMMAND > expdp scott/tiger include=index
フィルタ処理を行い、エクスポートの対象となるオブジェクトを
細かく設定することができます
INCLUDE / EXCLUDE
Data Pump
エクスポートの対象オブジェクトを
INCLUDEパラメータで指定
します
データのフィルタ処理を行います
Data Pump
フィルタ処理を行い、エクスポートの対象となるデータを細かく設定
することができます
エクスポート対象表のセットから行のサブセットを選択できます
例：職種が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
ファイルサイズを指定します
Data Pump
各ダンプ・ファイルがそのサイズを上限とし、置換変数(%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 217088 bytes
FILESIZE
ファイル指定に置換変数が含まれている場合は、新しいファイルが作成されます
COMMAND > expdp hr DIRECTORY=dpump_dir1 DUMPFILE=hr_3m%U.dmp FILESIZE=3MB
キロバイト(KB)、メガバイト(MB)、
フラッシュバック・モードを指定します
指定したSCNもしくは時刻における一貫性を維持したデータの
エクスポートが可能です
あるSCNにおける一貫性を維持したデータをエクスポートするために
FLASHBACK_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()で時刻を指定
エスケープ文字が必要となるので、
パラメータファイルで指定
FLASHBACK_SCN / FLASHBACK_TIME
Data Pump
パラレル・エクスポートを指定します
Data Pump
エクスポート処理をパラレルで行うことが可能です
並列度を
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:
置き換え変数(％U)を指定しない場合、
dumpfileのファイル数はparallelの値に合わ
せる必要があります指定している場合、
指定されたファイル指定がラウンドロビンに
使用されます
並列度の指定
生成されたダンプ
ファイルセットの確認
PARALLEL
※要Enterprise Edition
ダンプファイルのサイズの見積もります
ESTIMATE
ダンプファイルを生成せず、生成されるダンプファイルのサイズを
見積もることが可能です
見積もりのモードを
ESTIMATEパラメータで指定します
パラメータ値には次のどちらかを設定します
BLOCKS ・・・ ブロックサイズにオブジェクトのブロック数をかけて見積もります
（精度は高くありません）
STATISTICS ・・・ 統計情報を元に見積もります
見積もりのみ出力する場合には、
ESTIMATE_ONLY=yを指定します
COMMAND > expdp scott/tiger tables=emp,dept estimate=blocks estimate_only=y
Import – Data Pump –
Data Pump
COMMAND > impdp scott/tiger tables=emp,dept
Import Utility
impdp
impdpでは、
expdpで使用できたパラメータのほとんどを利用することができます
impdpでパラメータを指定することにより、expdpで作成したダンプファイルからさらに対
象データを絞りこんでインポートすることが可能です
ダンプファイルからemp表とdept
表のみをインポート
COMMAND > impdp scott/tiger include=index
ネットワーク・リンクを使用します
Data Pump
NETWORK_LINK
データベース・リンクを使用して、リモートデータベースから直
接ローカルデータベースにインポートを行うことができます
参考：エクスポートのネットワーク・リンク指定は、リモートデータベース
のデータをローカルにエクスポートする機能ですインポートの場合には
直接ローカルデータベースに書き込むので、ダンプファイルを作成しま
せん
COMMAND > impdp scott/tiger tables=emp,dept directory=dpump_dir
network_link=scott.jp.oracle.com
接続するリモートデータベースを、
NETWORK_LINKパラメータで指定します
指定する値はデータベース・リンク名です
ORACLE DATABASE からのCSVアン
ロード
なぜCSVでロード / アンロードしたいのか？
•
RDBMS製品間のデータ交換
•
Oracle Database から他社RDBMSへ
•
他社RDBMSから Oracle Database へ
•
業務処理(本番環境)としてデータ交換を行なっている
•
検証・開発用にデータをベンダーに提供する
CSVロード / アンロードに関する問題
•
CSVはフォーマット・エラーが発生しやすい
•
ローディングが行えず、検証/開発が始まらない
•
Oracle Database には CSVアンローダーが存在しない
•
どのような手法でアンロードするかが性能に大きく影響する
•
CSVフォーマット・エラーが発生しやすい
CSVフォーマットとは
•
狭義では CSV = Comma-Separated Values [RFC4180]
•
フィールド終端: , レコード終端: CRLF
•
広義では CSV = Character-Separated Values
•
フィールド終端とレコード終端が特定の文字
•
狭義のCSVを使いがちだが
ほとんどのソフトウェアが広義のCSVに対応している
•
狭義のCSVはフォーマット・エラーを起こしやすい
フォーマット・エラーの例
データにフィールド終端が含まれている
abc
def
1,234
uvw
xyz
7,890
abc
def
1
234
uvw
xyz
7
890
abc
,
def
,
1,234
uvw
,
xyz
,
7,890
2列に分割されてしまっている
望ましい対応例
終端記号をデータに含まれない文字にする
•
例）フィールド終端: タブ（^I）
abc
^I
def
^I
1,234
uvw
^I
xyz
^I
7,890
•
タブ区切りテキスト (TSV) は Microsoft Excel 含め、
対応ソフトウェアが多いので利用しやすい
• 注意:
ユーザーが自由入力できるアプリケーション
（textarea要素を含むWebアプリケーションなど）では
データにタブが含まれることが少なくない
ORACLE_LOADERの場合のよい対応例
終端記号にマイナーなASCII制御文字を使用する
•
ORACLE_LOADERドライバー（SQL*Loader含む）は
ASCII制御文字を含むあらゆる文字に対応できる
•
例）フィールド終端: ユニット分離標識（^_）
レコード終端: レコード分離標識（^^）
abc
^_
def
^_
1,234
^^
uvw
^_
xyz
^_
7,890
•
マイナーなASCII制御文字がデータに含まれている確率はほぼゼロ
•
Vimでは Ctrl + V → Ctrl + ^ でASCII制御文字を入力可能
ORACLE_LOADERの場合の間違いやすい対応例1
エンクロージャでは解決できない
•
エンクロージャ " を追加する
"
abc
","
def
","
1,234
"
•
データに " は含まれていないか？ → 含まれている → エラー
"
abc
","
def
","
1'23"
"
•
データに " が含まれていないことが確実なのであれば、
フィールド終端を " にするだけで解決
abc
"
def
"
1,234
ORACLE_LOADERの場合の間違いやすい対応例2
エンクロージャをつけても、レコード終端はデータに含められない
•
ORACLE_LOADERドライバー（SQL*Loader含む）は
エンクロージャを使ってもデータにレコード終端を含められない
例）フィールド終端: , レコード終端: 改行 エンクロージャ: “
”
abc
“,”
def
“,”
12
34
“
→ エラー: 2番目の囲み文字列がありません
•
可変レコード形式 (VAR) を使った対応方法もあるが、
レコード終端を改行から別のASCII制御文字に変更したほうが簡単
ORACLE_LOADERの場合の間違いやすい対応例3
エスケープ文字は使用できない
•
ORACLE_LOADERドライバー（SQL*Loader含む）は
エスケープ文字でフィールド終端とレコード終端を無効化できない
例）フィールド終端: , レコード終端: 改行
abc
,
def
,
1
¥
,234
uvw
,
xyz
,
78
¥
90
abc
def
1¥
,234
uvw
xyz
78¥
90
単なる文字として扱われる
ORACLE_LOADERの場合の間違いやすい対応例4
文字列にしても解決できない
•
ORACLE_LOADERドライバー（SQL*Loader含む）は
文字 “列” に対応できるが、これで常に解決できるわけではない
•
例）フィールド終端: ,,
abc
,,
def
,,
1,234 ← 一見良さそう
uvw
,,
xy,
,,
7,890 ← 想定外の区切り
uvw
xy
,7,890
区切りがずれている
複数の表をExportし、データのみをImportしたい
オリジナル / Data Pump
複数の表をExportし、データのみ
をImportしたいが、外部キーが多
数あり、整合性制約に違反してし
まう・・・
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に設定します
以下のように、関連する参照整合性制約を disableし、
Exportダンプから一部データを取り出す方法
オリジナル / Data Pump
誤って一部データをdelete
し、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
SQL> delete from oradirect.emp
where emp_id=100;
SQL> commit;
以下のように削除したデータを
エクスポートのダンプ・ファイルより取り出せます
注）エクスポートした時点のデータと削除したデータが一致するかは不明であるため、
この例の場合emp_id=100のデータに関して、 エクスポート時と誤って削除した時が
索引作成文を受け取る方法 －オリジナル－
オリジナル
エクスポート・ファイルのCREATE
TABLE文もコメントとして含まれます
Option
INDEXFILE
デフォルト なし
用途
索引作成文を受け取るファイルを指定します
さらにData PumpではすべてのSQL文を
受け取れます
Data Pump
Option
SQLFILE
デフォルト なし
用途
インポートが他のパラメータに基づいて実行するすべてのSQL
DDLの書込み先のファイルを指定します
使用例
impdp scott/tiger TABLES=(wendy.emp)
SQLFILE=‘index.txt’
注意：
パスワードは、SQLファイルに含まれないことに注意してください
たとえば、実行したDDLにCONNECT文が含まれている場合、
その文はコメントで置き換えられ、スキーマ名のみが表示されます
例：TEST_TABLE表をインポートする際のSQLFILEを受け取る場合
SQLFILEの一例
Data Pump
CREATE TABLE文や
CREATE INDEX文を
確認する事ができます
さらにINSERT文も
確認する事ができます
Data Pump 圧縮
データやメタデータを圧縮してダンプファイルにエクスポートできる
•
COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}
•
NONE...エクスポート操作全体について圧縮が無効
•
METADATA_ONLY(default)...全ての“メタデータ”が圧縮形式で
ダンプ・ファイルへ書き込み
(以下の操作はAdvanced Compressionオプションが必要)
•
DATA_ONLY...全ての“データ”が圧縮形式でダンプ・ファイルへ書き込み
•
ALL...データ、メタデータともに圧縮
•
(例）スキーマを指定し、メタデータ、データともに圧縮してダンプ・ファイルscott.dmp
へエクスポート
$ expdp scott/tiger DIRECTORY=dpump_dir
DUMPFILE=scott.dmp COMPRESSION=ALL SCHEMAS=scott
Data Pump
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
Direct NFS（dNFS）の概要
•
dNFSとは
•
Oracle Database内部に実装されたNFSクライアント機能
•
Oracle Database 11g Release 1から使用可能
•
dNFSの特徴
1.
OSカーネルのNFSクライアント（kNFS）より高いディスクI/O性能
2.
簡単な手順で機能を有効化
•
アプリケーションの書き換えは必要ない
•
ストレージの構成や運用に影響はない
•
複数イーサネット・ポートを使用したネットワーク帯域のスケー
ラビリティの設定が簡単
SQL*Loader及び外部表でのdNFSの使用
•
SQL*Loader及び外部表の入力ファイルに対して、dNFSを使用したアクセスが
可能
•
ファイルサイズに応じて自動でdNFSが利用される
•
1GB以上の入力ファイルに対しては、自動でdNFSを使用したアクセス
を実行
•
1GB以下の入力ファイルに対しては、kNFSを使用したアクセスを実行
•
明示的にdNFSによるアクセスを実行したい場合は、DNFS_ENABLEパラ
メータをENABLEに指定する
•
dNFSによる読み取りバッファ数に対して制限をかけることも可能
•
DNFS_READBUFFERSパラメータの値を変更する
SQL*Loader/外部表での使用方法
明示的な設定方法
•
SQL*Loader での使用方法
1.
明示的にdNFSを使用して入力ファイルにアクセスする場合
2.
明示的にdNFSを使用せずに入力ファイルにアクセスする場合
•
外部表での使用方法
•
access パラメータのDNFS_ENABLE/ DNFS_DISABLEで指定する
•
外部表でdNFSが利用されるのは、ORACLE_LOADERを使用した場合
•
ORACLE_DATAPUMPでは使用できない
$ sqlldr username/password control=control_file
DNFS_ENABLE=TRUE
Data PumpでのLOB記憶域の変更
•
Data Pump(impdp)で、インポートするLOBをSecureFilesに変更可能
•
Oracle Database 11gまで：従来のリリースまでは、LOBをインポート
する時に
expdpをしたLOBの属性のままでしかインポートできな
かった
•
Oracle Database 12cでは：TRANSFORMパラメータの
LOB_STORAGE
オプションでLOBの属性を変更可能
•
設定方法
•
TRANSFORM=LOB_STORAGE:[SECUREFILE | BASICFILE |
DEFAULT | NO_CHANGE]をimpdp時に指定する
Data PumpでのLOB記憶域の変更
実行例
SQL> select table_name,column_name,securefile from user_lobs; TABLE_NAME COLUMN_NAME SEC
--- --- --- T1 C1 NO
$ expdp test/test directory=datapump_dir tables=t1 …中略…
ジョブ"TEST"."SYS_EXPORT_TABLE_01"が月 9月 2 17:59:43 2013 elapsed 0 00:00:24で正常に完 了しました
$ impdp test/test directory=datapump_dir transform=LOB_STORAGE:SECUREFILE …中略…
ジョブ"TEST"."SYS_IMPORT_FULL_01"が月 9月 2 17:33:35 2013 elapsed 0 00:00:02で正常に完了 しました
SQL> select table_name,column_name,securefile from user_lobs; TABLE_NAME COLUMN_NAME SEC
--- --- --- T1 C1 YES
