タイトル
TOP → Oracle → This Page

SQL*Loader

概要

SQL*LoaderはOracleが標準で提供するユーティリティ。
外部ファイルのデータを表にロードすることができる。
様々なデータ形式のデータ・ファイルに対応しており、
複数のデータ・ファイルからのロードや
複数の表へのロードなどもサポートしている。
また、大量のレコードを取り込む場合、
IMPORTユーティリティでインポートするとかなりの時間がかかるが、
SQL*Loaderで取り込むとびっくりするぐらい短い時間で取り込めたりする。
ただし、SQL*Loader用の制御ファイルの作成などが必要なので、
手軽さは IMPORT、Data Pump IMPORT よりは劣る。
SQL*Loader概要図

コマンド

SQL*Loader は sqlldr コマンドで実行可能
実行方法は以下の3つがある。

sqlldr
パラメータなしで実行するとヘルプ画面が表示されて終了する。

sqlldr パラメータ名=パラメータ値 [,パラメータ名=パラメータ値,...]
コマンドの後ろに各種パラメータを付加して実行する。

sqlldr parfile=パラメータファイル
前もってパラメータを記述したファイルを指定して実行する。

権限

後述するデータエントリ・モードで TRUNCATE を指定する場合は以下の権限が必要。
取り込み先の表が実行ユーザの所有である場合
　→DROP TABLE 権限が必要
取り込み先の表が他スキーマの所有である場合
　→DROP ANY TABLE 権限が必要

コマンドパラメータ一覧

パラメータ値
(※1)		デフォルト説明
BAD STRING 拡張子が.badのデータ・ファイル名 SQL*Loaderによって作成される不良ファイル名
挿入中にエラーとなったレコード、または形式が不適切なレコードが出力される
エラーレコードがなかった場合は不良ファイルは作成されない
BINDSIZE NUMBER ※2 従来型パスのバインド配列の最大サイズ(バイト)
COLUMNARRAYROWS NUMBER ※2 ダイレクト・パスの列配列に対する行数
COMMIT_DISCONTINUED T/F FALSE ロードされた行は、ロードが停止してもコミットするかどうか
CONTROL STRING - SQL*Loader制御ファイル名
DATA STRING 拡張子が.datの制御ファイル名 ロードするデータが入っているデータ・ファイルの名前
DIRECT T/F FALSE 従来型パスまたはダイレクト・パスのどちらの方法でデータをロードするか
TRUE：ダイレクト・パス・ロード
FALSE：従来型パス・ロード
DISCARD STRING 拡張子が.dscのデータ・ファイル名 SQL*Loaderで作成する廃棄ファイル名
表に挿入されず、拒否もされなかったレコードが出力される
DISCARDMAX NUMBER ALL 廃棄最大レコード数
廃棄レコード件数がここで指定した数に達するとロードは中止される
DISCARDMAX=ALL だとロードは中止されない
ERRORS NUMBER ※2 許容される最大挿入エラー件数
挿入エラー件数がここで指定した数を超えるとロードは中止される
FILE STRING - エクステントを割り当てるデータベース・ファイル
PARALLEL=TRUEの場合のみ有効
LOAD NUMBER ALL ロードする論理レコード数
SKIPで指定した件数のレコードをスキップした後に、ロードする論理レコード件数の最大数を指定
LOAD=ALL だと全レコードがロードされる
実際のレコード件数が指定された最大数より少ない場合でも特にエラーは発生しない
LOG STRING 拡張子が.logの制御ファイル名 SQL*Loaderによって作成されるログ・ファイル
MULTITHREADING T/F ※2 ダイレクト・パスでのマルチスレッドの使用有無
PARALLEL T/F FALSE ダイレクト・ロード時に複数の同時セッションによって同じ表にデータをロードできるかどうか
PARFILE STRING - パラメータファイル名
READSIZE NUMBER ※2 読取りバッファのサイズ(バイト)
ROWS NUMBER ※2 従来型パスのバインド配列内、またはダイレクト・パスのデータ保管の間の行数
SILENT STRING - 以下、指定した内容の実行中に表示されるメッセージを抑制
HEADER：SQL*Loaderのヘッダー・メッセージ
FEEDBACK：「commit point reached」フィードバック・メッセージ
ERRORS：不良ファイルに書き込まれた場合のデータ・エラー・メッセージ
DISCARDS：廃棄ファイルに書き込まれた場合のメッセージ
PARTITIONS：パーティション表のダイレクト・ロード中、
　ログ・ファイルに対するパーティションごとの統計情報
ALL：全て
silent=(HEADER, FEEDBACK) のように複数指定可能
SKIP NUMBER 0 スキップする論理レコード数
ファイルの先頭から何件の論理レコードをロード対象外とするかを指定
STREAMSIZE NUMBER ※2 ダイレクト・パスのストリーム・バッファのサイズ(バイト)
USERID STRING - 処理を実行するユーザーの接続情報
username/password[@instance][ AS SYSDBA] 形式で指定
※1 NUMBER：数値、STRING：文字列、T/F：TRUEまたはFALSE
※2 OSによって異なる
※ 他にもパラメータはありますが独断と偏見で「あまり使わない」と判断したものは省略しています
※ SQL*Loader制御ファイル中で指定したパラメータよりも、
　コマンドラインで指定したパラメータの方が優先されます

ダイレクト・パス・ロード

ダイレクト・パス・ロードでデータをロードすると、データベースバッファを経由せずに
直接データファイルへデータを書き込むため非常に高速に処理できます。

ただし、ダイレクト・パス・ロードを利用する場合は以下の制限があります。
  • データのロード時に外部キー制約とチェック制約は無視される
  • クラスタ化された表には使用できない
  • 索引構成表には使用できない
  • 未処理のアクティブ・トランザクションがある表には使用できない
  • BFILE型、LOB型、REF型、VARRAY型には未対応
  • 制御ファイルでSQL関数を使用できない（Oracle 9iからは使用可能）
  • データのロード中は表がロックされる

実行例

最低限のパラメータで実行
sqlldr usr1/pass1@db1 control='C:\test1.ctl'

最初の10行は無視する
sqlldr usr2/pass2@db2 control='C:\test2.ctl' log='C:\test2.log' skip=10 rows=100

ダイレクト・パス・ロードで実行
sqlldr usr3/pass3@db3 control='C:\test3.ctl' log='C:\test3.log' direct=true

SQL*Loader制御ファイル設定一覧

句説明
OPTIONS 書式
OPTIONS(パラメータ名=パラメータ値 [,パラメータ名=パラメータ値,...])

コマンドライン・パラメータを指定可能
ここで指定しない場合は省略可能
半角カッコで囲む
半角カンマ区切りで複数指定可能
LOAD DATA お約束の記述
とりあえず指定しましょう
INFILE 書式
INFILE 'データ・ファイル名' ["オプション"]
※データ・ファイル名はシングルクォートで囲むのにオプションはダブルクォートで囲む
　ややこしい・・・

パラメータ説明
データ・ファイル名 ロード元のデータ・ファイル名
オプション OSによって指定方法が違うようだ(泣)
色々試してみて下さい
どちらも固定長レコードの場合にサイズを指定する方法です
・RECSIZE 数値
・fix 数値
BADFILE 書式
BADFILE 不良ファイル名

不良ファイル名
DISCARDFILE 書式
DISCARDFILE 廃棄ファイル名

廃棄ファイル名
INTO TABLE句がWHEN句を持っている場合、WHEN句に該当しなかったレコードが出力される
CHARACTERSET 書式
CHARACTERSET キャラクタ・セット

データ・ファイルのキャラクタ・セット
INTO TABLE 書式
[ロード方法] INTO TABLE [表名]

パラメータ説明
ロード方法 以下のいずれかを指定
APPEND：行追加、既存行がある場合は新しい行として追加
INSERT：空表に対するロード、空でない場合はエラー
REPLACE：行置換、既存行は全てDELETEしてからロード
TRUNCATE既存行をTRUNCATEしてからロード
表名 ロード先の表名
WHEN 書式
WHEN (条件)

ロードするデータの条件を指定
条件に一致しないデータは廃棄ファイルに出力される
条件を指定する必要がない場合は不要
表名の後に指定する
(例1)第5列の値が0の行をロード
WHEN (5) = '0'
(例2)deptno列の値が10、job例の値がSALESの行をロード
WHEN (deptno = '10') AND (job = 'SALES')
FIELDS 書式
FIELDS [TERMINATED BY (区切り記号)] [OPTIONALLY] [ENCLOSED BY (囲み記号)
固定長やデータを囲む記号がない場合は指定不要

パラメータ説明
区切り記号 項目(列)の区切り記号
以下のいずれかを指定
WHITESPACE：半角スペース区切り
X'09'：タブ区切り
','：カンマ区切り
EOF：EOF区切り
囲み記号 項目(列)の囲み記号
'"'：ダブルクォート囲み
TRAILING NULLCOLS 指定すると列が行中に存在しない場合、その列の値はNULLとして処理される
項目(列)指定 書式
(列名 [オプション] [,列名 [オプション],...])

項目名 FILLER この項目をロード対象外とする
項目名 CONSTANT '定数' 指定した値でセットする
項目名 POSITION(開始バイト:終了バイト) 固定長データの場合の開始バイト、終了バイト
項目名 SEQUENCE(初期値, 増分値) 行追加ごとに 初期値＋増分値でセットする
項目名 SEQUENCE(COUNT, 増分値) 行追加ごとに レコード数＋増分値でセットする
項目名 SEQUENCE(MAX, 増分値) 行追加ごとに 項目最大値＋増分値でセットする
項目名 SYSDATE システム日時をセットする
項目名 属性 項目の属性を指定
項目名 "関数" Oracle関数を指定

属性Oracleデータ型説明
CHAR CHAR,VARCHAR2 文字列
DATE DATE 日付
DECIMAL EXTERNAL NUMBER 数値
ZONED NUMBER 小数有り数値
CHARは255バイトを超える場合はCHAR(500)のようにバイト数を括弧付で追加する必要がある
DATEは書式を追加することも可能でその場合は
DATE(14) "YYYYMMDDHH24MISS"
のように指定する
※ [] 内の内容は省略可能
※ 他にも設定可能項目はありますが独断と偏見で「あまり使わない」と判断したものは省略しています

制御ファイル例

SQL*Loader制御ファイルサンプル のページでケース別に紹介

リンク

Oracle Database ユーティリティ 10g リリース2（10.2） 6 SQL*Loaderの概念
Oracle Database ユーティリティ 10g リリース2（10.2） 7 SQL*Loaderコマンドライン・リファレンス
Oracle Database ユーティリティ 10g リリース2（10.2） 8 SQL*Loader制御ファイル・リファレンス
Oracle Database ユーティリティ 10g リリース2（10.2） 9 フィールド・リスト・リファレンス
Oracle Database ユーティリティ 10g リリース2（10.2） 10 オブジェクト、LOBおよびコレクションのロード
Oracle Database ユーティリティ 10g リリース2（10.2） 11 従来型パス・ロードおよびダイレクト・パス・ロード

更新履歴

2012/02/01　ダイレクト・パス・ロードの説明追記
2012/01/21　注意事項等追記
2010/02/17　記述ミス修正、サンプルへのリンク追加
2010/02/16　新規作成

TOP → Oracle → This Page