<Insert Picture Here>
意外と知らない？！データローディングの基礎
Oracle
Direct Seminar
Agenda
データロードの種類
SQL＊Loaderを使用したデータロード
外部表を使用したデータロード
・SQL Serverからの移行アセスメント
・MySQLからの移行相談
・PostgreSQLからの移行相談
・Accessからの移行アセスメント
・Oracle Database バージョンアップ支援
・Oracle Developer/2000 Webアップグレード相談
・パフォーマンス・クリニック
・Oracle 構成相談
・Oracle Database 高可用性診断
・システム連携アセスメント
・システムセキュリティ診断
・簡易業務診断
・メインフレーム資産活用
データロードの種類
1.中間ファイルを利用するもの
2.ネットワークを利用するもの（参考）
中間ファイル
•
Data Import/Export
•
Data Pump
•
SQL*Loader
•
外部表
•
トランスポータブル表領域
(参考)
•
DB Link + SQL
•
Oracle Transparent
Gateway
•
Oracle Data Integrator
Export Utility EXP xxx.dmp データの抽出
中間ファイルを利用するデータロード
クライアントData Import/Export
Data Pump
Import Utility IMP データのロード サーバ サーバ Export Utility EXPDP データの抽出xxx.dmp 転送 xxx.dmp Import Utility IMPDP データのロード
SQL*Loader
外部ファイル SQL*Loader データのロード外部表+SQL
サーバ サーバ サーバ 外部表 ロード先表 INSERT INTO…. SELECT * FROM 外部ファイル ※ネットワーク経由も可参考：トランスポータブル表領域（TTS）
Export Utility
EXPDP
（EXP）
xxx.dmp
Oracle Database
Oracle Database
データの抽出
データのロード
xxx.dmp
表領域の情報を抜き出す
転送
xxx.dbf
Import Utility
IMPDP
（IMP）
Enterprise
Edition
データファイルを
コピーする
コピー元の表領
域を読みとり専用
に
Data
Export/Import
Data pump
SQL*Loader
外部表
ソース
Oracle DB
Oracle DB
外部ファイル
外部ファイル
/Oracle DB
主な用途
論理
Backupツール
論理
Backupツール
テキスト・
ローディング
テキスト・
ローディング
Datapump
ローディング
利用可能な
Version
Version5.X以前～
10g～
Version6.X以前～
9i～
10g～（アンロード）
実行形態
Client側で実行可
（Net経由）
Server側で実行
Client側で実行可
（Net経由）
Server側で実行
ファイル
形式
独自
独自
（Data Exp/Impと
互換性はなし）
テキスト
（固定長/可変長）
テキスト
（固定長/可変長）
独自（DataPump）
中間ファイルを利用するデータロード（特徴）
本セミナー内容
参考：Data Export/ImportとData Pump
Export Utility EXP xxx.dmp データの抽出 クライアントData Import/Export
Data Pump
Import Utility IMP データのロード サーバ サーバ Export Utility EXPDP データの抽出 xxx.dmp 転送 xxx.dmp Import Utility IMPDP サーバ サーバ
特徴：
•
ダンプファイルはクライアント側に
格納される
•
Data Pump Import/Exportと
比べた場合、比較的低速
•
Oracle Database 10g以降は
Data Pump Import/Exportの
使用を推奨
特徴：
•
ダンプファイルはサーバ側に
格納される
•
9iまでのexp/impと
同等の機能+更なる付加機能を
持つ、新たな機能を搭載
Exp/impに比べ、数倍高速
パラレル処理が実行可能
•
exp/imp と、Data Pumpで
ダンプファイルの互換性はない
Agenda
はじめに
データロードの種類
SQL＊Loaderを使用したデータロード
外部表を使用したデータロード
・SQL Serverからの移行アセスメント
・MySQLからの移行相談
・PostgreSQLからの移行相談
・Accessからの移行アセスメント
・Oracle Database バージョンアップ支援
・Oracle Developer/2000 Webアップグレード相談
・パフォーマンス・クリニック
・Oracle 構成相談
・Oracle Database 高可用性診断
・システム連携アセスメント
・システムセキュリティ診断
・簡易業務診断
・メインフレーム資産活用
Oracle Database
データのロード
SQL＊Loaderを使用したデータロード
SQL*Loader
外部ファイル
（text,csv）
SQL*Loaderを使用することで外部ファイルのデータを
Oracleデータベースの表にロードすることが可能
SQL*Loaderの特徴
•
柔軟性／高速性に応じた２つのモードを用意
従来型パス・モード
ダイレクト・パス・モード
•
様々な形式（固定長や可変長）、様々な文字コード（EUC,SJIS）の
外部データの取り込みが可能
•
同時に複数の表へロードが可能
•
複数のデータファイルから一度に同じ表へロードが可能
SQL*Loaderに関するファイル
•
ログファイル
ロード件数やロード中に発生した
エラーログなどを記録するファイル
•
廃棄ファイル
エラー以外の理由により
ロードされなかった
レコードを記録するファイル
•
不良ファイル
エラーによりロードされなかった
レコードを記録するファイル
•
制御ファイル
データの場所、
データの挿入先
データの分析などが
記述されたファイル
•
データファイル
ロードするデータが
格納されたファイル
SQL*Loader
•
データベース
SQL*Loader
従来型パスロード
•
従来型パスロードでは
テキストデータを読み取りながら
内部的に INSERT を発行
•
Insertと同程度の時間がかかる
SQLコマンドの生成
キャッシュ上の
ブロックへの書き込み
ディスクへの書き込み
REDOログ･
バッファ
データベース・
バッファ･キャッシュ
共有プール
SGA
SQL*Loader
ダイレクト・パス・ロード
•
制限事項
例：）
ロードする間テーブルに
ロックがかかる
参照整合性制約、トリガーなど
•
バッファキャッシュを迂回し、
表に割り当てられたエクステントに
直接ブロックが保存される。
ブロックイメージを作成
ディスクへの書き込み
REDOログ･
バッファ
データベース・
バッファ･キャッシュ
共有プール
従来型パスロードVSダイレクトパスロード
1回あたりのロード時間
※従来型パスロードのロード時間を1とした場合の 相対処理時間 （実際の処理時間に任意の数を掛けています） ※資料中に掲載されている検証内容は、特定環境における検証結果に ついての報告であり、すべての環境において同様の動作を保証するも のではありませんので、予めご了承ください。 ※5,000 レコードロードの結果 0.3 １ 0ダイレクト
パスロード
従来型
パスロード
ダイレクトパスロード
従来型パスロード
ダイレクトパスロードは
従来型パスロードと比べ非常に高速
0.2 0.4 0.6 0.8 1.0参考：SQL*Loaderの使用方法
1.外部ファイルを準備する
例：emp.dat, emp.csv,emp.txt
2.制御ファイルの作成
viエディタ等で制御ファイルを作成
<制御ファイルサンプル samp1.ctl>
1 OPTIONS(
2
DIRECT = TRUE,
3 )
4
5 LOAD DATA
6 INFILE ‘emp.dat'
7 INTO TABLE tab_samp1
8 APPEND
9 FIELDS TERMINATED BY ','
10 (empno,ename)
モードを選択
例：ダイレクトパスロードを使
用する場合
emp.dat
1 7499,ALLEN
2 7521,WARD
3 7566,JONES
参考：SQL*Loaderの使用方法
3. SQL*Loaderの実行
% sqlldr scott/tiger control=samp1.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on 水 Sept 1 21:14:26 2010
(c) Copyright 1982, 2009, Oracle and/or its affiliates. All rights reserved.
コミット・ポイントに達しました。 - 論理レコード件数 3
4.ロード結果の確認
1 SQL> select * from tab_samp1;
2
EMPNO ENAME
3 ---
---4
1 Yamada
5
2 Suzuki
6
3 Tanaka
パラレル・ダイレクト・パス・ロード
•
複数のセッションを同時に実行して、
同一の表またはパーティション表の同一パーティション
に対してダイレクト・パス・ロードを実行
ダイレクト・パス・ロードのパフォー
マンスが向上
P1
P2
P3
P4
パラレル処理
P1
シリアル処理
待機
参考：パラレル・ダイレクト・パス・ロード設定方法
1.ローディング用ファイルを準備する
例：emp1.dat, emp2.dat, emp3.dat
2.制御ファイルをそれぞれのセッション毎に作成
<制御ファイルサンプル samp1.ctl>
1 OPTIONS(
2 DIRECT = TRUE,
3 PARALLEL =
TRUE
4 )
5 LOAD DATA
6 INFILE ‘emp1.dat'
7 INTO TABLE tab_samp1
8 APPEND
9 FIELDS TERMINATED BY ','
10 (empno,ename)
もしくはコマンドプロンプトから
有効化
sqlldr USERID=scott/tiger
CONTROL=samp1.ctl DIRECT=TRUE
PARALLEL=true
<制御ファイルサンプル samp2.ctl>
・・・・・・・・・・・・・・・・・・・・・・・・・・
・・・・・・・・・・・・・・・・・・・・・・・・・
“PARALLEL”オプションを
TRUEに設定することで
パラレル化を有効化
並列化させるセッション毎に
それぞれロードする
外部ファイルを準備する
sqlldr USERID=scott/tiger
CONTROL=samp2.ctl DIRECT=TRUE
PARALLEL=true
sqlldr USERID=scott/tiger
CONTROL=samp3.ctl DIRECT=TRUE
PARALLEL=true
SQL*Loader (
ダイレクト・パス・モード
)の考慮点
•
ローディング中はテーブル全体がロック
検索処理は可能だが、更新処理は待たされる
•
データを加工しながらローディングできない
一度、ワーク・テーブルを用意する必要がある
•
運用が複雑
インデックスのメンテナンス手順
パラレルでロードする場合、
複数の制御ファイルを作成するのが面倒
パラレル・ローディングの指定が面倒
etc.
従来型パスロード とダイレクトパスロードの
使い分け
従来型パスロードを使用するケース
•
ロードと並行して表にアクセスする場合
•
参照整合性制約および列チェック整合性制約のある大き
な表に、比較的少数の行をロードする場合
ダイレクトパスロードを使用するケース
•
従来型パス・ロードと比較すると非常に高速なので、
•
短時間でデータをロードしたい場合
Agenda
はじめに
データロードの種類
SQL＊Loaderを使用したデータロード
外部表を使用したデータロード
・SQL Serverからの移行アセスメント
・MySQLからの移行相談
・PostgreSQLからの移行相談
・Accessからの移行アセスメント
・Oracle Database バージョンアップ支援
・Oracle Developer/2000 Webアップグレード相談
・パフォーマンス・クリニック
・Oracle 構成相談
・Oracle Database 高可用性診断
・システム連携アセスメント
・システムセキュリティ診断
・簡易業務診断
・メインフレーム資産活用
無償
技術サービスOracle
Direct
Concierge
Oracle Database
外部表とは
外部表
外部ファイル
（text,csv etc.）
SELECT * FROM
•
外部ファイル(CSVファイル等)をデータベース内に
格納されているテーブルのように扱うことが可能
–
CSVファイルの各列のマッピングをテーブル作成時に定義
•
2種類のアクセス・ドライバ
ORACLE_LOADER アクセス・ドライバ
ORACLE_DATAPUMP アクセス・ドライバ
ディレクトリ・オブジェクト
•
DML操作（UPDATE、INSERTまたはDELETE）は実行でき
ず、索引も作成できない
アクセス・ドライバ：
ORACLE_LOADERとORACLE_DATAPUMP
ORACLE_LOADER
ORACLE_DATAPUMP
アンロード 転送特徴：
•
9iから使用可能
•
ロードのみ可能
•
データはテキスト・データファイルから
ロード
•
デフォルトで使用
•
CSVファイル等のデータを
ロードする場合に利用
特徴：
•
10gから使用可能
•
アンロード・ロードが可能
•
データはダンプ・ファイルからロード
（注：外部表のアンロードで作成されるダンプ・ファイルと
Data pumpで作成されるダンプ・ファイルは異なります）
•
別のOracle Database内にあるデータ
をロードする場合に利用
外部表 ディレクトリ・オブジェクト 外部ファイル （.txt,.csv） ディレクトリ・オブジェクト 外部表 内部表 .dmp ディレクトリ・オブジェクト 外部表 .dmp外部表の作成（ORACLE_LOADER）
1.外部ファイルを準備する
例：emp.dat, emp.csv,emp.txt
2.ディレクトリオブジェクトの作成
SQL> CREATE DIRECTORY load_src as ‘C:¥Documents and
SQL> Settings¥My Documents';
3.ディレクトリオブジェクトの読み込み・書き込み権限を付与
SQL> GRANT READ ON DIRECTORY load_src TO scott;
SQL> GRANT WRITE ON DIRECTORY load_src TO scott;
外部ファイルがある
外部表の作成（ORACLE_LOADER）
4.外部表の作成
SQL> CREATE TABLE emp_ext
( empno NUMBER
, ename varchar2(10)
:
)
ORGANIZATION EXTERNAL
( DEFAULT DIRECTORY load_src
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ',')
LOCATION (
‘emp.dat'
)
)
外部表の宣言
ロードするデータファイルの
指定
外部表の作成（ORACLE_LOADER）
SQL> select empno,ename from emp_ext;
EMPNO ENAME
---7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
11行が選択されました。
5.通常の表へのアクセスと同様にSQL文を実行する
外部表 -アンロード（ORACLE_DATAPUMP）
•
外部表の機能を利用して、データのアンロードが可能
9i でも利用できた外部表の構文で、データ型にはORACLE_DATAPUMPを
指定
SQLの問い合わせを記述することで、自由にアンロード可能
DPUMP_DIRで指定されたディレクトリ下に「dept.dmp」というダンプファイル
が作成される
SQL> create table dept_external1
organization external (
type oracle_datapump
default directory dpump_dir
location('dept.dmp') )
as select * from dept;
ORACLE_DATAPUMP
を指定
AS SELECT ～ で
参考：外部表のエラー処理
EMP表
SELECT * FROM EMP；
•
データ・ファイルと外部表の間で変換エラーが発生すると、
エラーの発生した行は無視される
•
SQL*Loader同様、エラーログやエラーでロードされなかったデータは
ログファイル、不良ファイルおよび廃棄ファイルに記録される
※ログファイル、不良ファイルおよび廃棄ファイルはディレクトリオブジェクト内に出力される
変換エラー
外部表を使用したデータロード
INSERT INTO EMP SELECT *
FROM EXT_EMP;
EMP表
（ロード先表）
EXT_EMP表
（外部表）
•
INSERT INTO … SELECT文やCREATE TABLE … AS SELECT文
を利用して外部表を読み込みながら、ロード先のターゲットとなるテ
ーブルにデータをロードする方法
•
SQL*Loaderと異なり、SQLでロードするデータの選択が可能
ディレクトリ・オブジェクト
外部ファイル
（text,csv etc.）
外部表を利用したデータのロード
外部表としてテキスト
データを読み込みながら
データをロード
SQL>CREATE TABLE emp_ext
( empno NUMBER
, ename varchar2(10)
:
)
ORGANIZATION EXTERNAL
( DEFAULT DIRECTORY load_src
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ',')
LOCATION (
‘emp.dat'
)
)
/
SQL> INSERT /*+ APPEND */ INTO EMP SELECT * FROM EMP_EXT;
1.外部表を作成する
外部表でのパラレルロード
•
外部表を利用した場合でもパラレルでのロードが可能
1 CREATE TABLE emp_ext
2 ( empno NUMBER
3 , ename varchar2(10)
4 :
5 )
6
ORGANIZATION EXTERNAL
7 ( DEFAULT DIRECTORY load_src
8
ACCESS PARAMETERS
9
( RECORDS DELIMITED BY NEWLINE
10 FIELDS TERMINATED BY ',')
11 LOCATION (
‘emp.dat'
)
12 )
13
PARALLEL 2
※SQL文のヒント句を使用した並列化も可能
PARALLEL句で
並列度を指定
•
ロード元のソースデータの文字コードがACIIとUTF-8の場合、
ソースファイルを分割することなく、Oracle側が自動分割して並列に読み込む
•
Shift_JISやEUC-JPでCSV形式のような可変長データの場合は
並列度と同じ数だけファイルを分割する必要がある
圧縮ファイルでの外部表
11gR2 新機能
•
圧縮ファイル形式などのテキストファイル以外のデータも外部表として
ロードが可能
•
外部表に読み取るデータファイルの前処理（Preprocessor）を
行うプログラムを指定して、実行
•
解凍とロードを一括して行えるので、ディスクスペースを節約
1 CREATE TABLE sales_external
2 (...)
3 ORGANISZATION EXTERNAL
4 ( TYPE ORACLE_LOADER
5 DEFAULT DIRECTORY data_dir1
6 ACCESS PARAMETERS
7 (RECORDS DELIMITED BY NEWLINE
8
PREPROCESSOR exec_dir:
9 'gunzip.sh'
10
FIELDS TERMINATED BY 11
'|'
12 )
13 LOCATION (...)
14 );
SQL*LoaderVS外部表
1回あたりのロード時間
※SQL*Loaderのロード時間を1とした場合の 相対処理時間 （実際の処理時間に任意の数を掛けています） ※資料中に掲載されている検証内容は、特定環境における検証結果に ついての報告であり、すべての環境において同様の動作を保証するも のではありませんので、予めご了承ください。 ※約300万レコードロードの結果 0.98 １ 0外部表
SQL*Loader
外部表
SQL*Loader
ロード時間は大きな違いはない
0.2 0.4 0.6 0.8 1.0SQL*Loaderと外部表
SQL*Loader
外部表
ソース
外部ファイル
外部ファイル/Oracle DB
利用可能な
Version
Version6.X以前～
9i～
10g～（データのアンロード）
実行形態
Client側で実行
（Net経由）
Server側で実行
ファイル
形式
テキスト
（固定長/可変長）
テキスト
（固定長/可変長）
独自（DataPump）
ロードスピード
外部表とSQL*Loaderでは
大差はない
外部表とSQL*Loaderでは
大差はない
データ
クレンジング
データを加工しながらの
ローディングにはワーク・テーブルが必要
直接データを加工しながら
ローディングができる
パラレルロード
設定が面倒
設定が容易
ロード対象
データの選択
ロードするデータの選択は不可
SQLでロードするデータの選択が可能
•
外部ファイルのロードには外部表を利用したロードが効果的
まとめ
•
Oracleでは2種類の外部ファイルのデータロード方法を提供
SQL*Loader
外部表
•
SQL*Loaderは従来型パスロード とダイレクトパスロード２つのロード方法があり、
用途によって、それぞれを使い分ける
•
外部表は外部ファイルをデータベース内に格納されている
テーブルのように扱うことができる機能
•
外部表を利用することでSQL*Loaderと比べ、容易にデータのローディングが可能
OTN×ダイセミ でスキルアップ!!
※OTN掲示版は、基本的にOracleユーザー有志からの回答となるため100%回答があるとは限りません。
ただ、過去の履歴を見ると、質問の大多数に関してなんらかの回答が書き込まれております。
Oracle Technology Network(OTN)
を御活用下さい。
・一般的な技術問題解決方法などを知りたい！
・セミナ資料など技術コンテンツがほしい！
一般的技術問題解決にはOTN掲示版の
「データベース一般」
をご活用ください
http://otn.oracle.co.jp/forum/index.jspa?categoryID=2
過去のセミナ資料、動画コンテンツはOTNの
「OTNセミナー
オンデマンド コンテンツ」
へ
http://www.oracle.com/technology/global/jp/ondemand/otn-seminar/index.html
※ダイセミ事務局にダイセミ資料を請求頂いても、お受けできない可能性がございますので予めご了承ください。
ダイセミ資料はOTNコンテンツ オン デマンドか、セミナ実施時間内にダウンロード頂くようお願い致します。
Oracle Direct 新サービスができました
新規Oracle Direct Concierge
（無償支援サービス）
お問い合わせフォーム
http://www.oracle.co.jp/inq_pl/INQUIRY/quest?rid=28
•Webシステム ボトルネック診断サービス
•WebLogic Serverバージョンアップ支援サービス
•Oracle Application Server、その他アプリケーションサーバーからの
OTNセミナー オンデマンド コンテンツ
ダイセミで実施された技術コンテンツを動画で配信中!!
ダイセミのライブ感はそのままに、お好きな時間で受講頂けます。
※掲載のコンテンツ内容は予告なく変更になる可能性があります。
期間限定での配信コンテンツも含まれております。お早めにダウンロード頂くことをお勧めいたします。
OTN オンデマンド
Oracle エンジニアのための技術情報サイト
オラクルエンジニア通信
http://blogs.oracle.com/oracle4engineer/
• 技術資料
• ダイセミの過去資料や製品ホワイト
ペーパー、スキルアップ資料などを
多様な方法で検索できます
• キーワード検索、レベル別、カテゴ
リ別、製品・機能別
• コラム
• オラクル製品に関する技術コラムを
毎週お届けします
• 決してニッチではなく、誰もが明日
から使える技術の「あ、そうだった
んだ！」をお届けします
先月はこんな資料が人気でした
Oracle Database 11gR2 RAC インストレーション・
ガイド ASM 版 Microsoft Windows x86-64
Oracle Database 11gR2 旧バージョンからのアッ
プグレード
オラクル クルクルキャンペーン
Enterprise Edition
はここが違う!!
•
圧倒的なパフォーマンス
! • データベース管理がカンタン
!
• データベースを止めなくていい
! • もちろん障害対策
も万全! Oracle Databaseの ライセンス価格を大幅に抑えて ご導入いただけます詳しくはコチラ
http://www.oracle.co.jp/campaign/kurukuru/index.html
あの
Oracle Database Enterprise Edition
が超おトク
!!
お問い合わせフォーム
http://www.oracle.co.jp/inq_pl/INQUIRY/quest?rid=28
多くのお客様でサーバー使用期間とされる 5年間にライセンス期間を限定 • 期間途中で永久ライセンスへ差額移行 • 5年後に新規ライセンスを購入し継続利用 • 5年後に新システムへデータを移行http://www.oracle.co.jp/inq_pl/INQUIRY/quest?rid=28
Oracle Direct
検索
あなたにいちばん近いオラクル
Oracle
Direct
まずはお問合せください
Web問い合わせフォーム
フリーダイヤル
専用お問い合わせフォームにてご相談内容を承ります。
※フォームの入力には、Oracle Direct Seminar申込時と同じ ログインが必要となります。 ※こちらから詳細確認のお電話を差し上げる場合がありますので、ご登録さ れている連絡先が最新のものになっているか、ご確認下さい。
