データのロードと再編成
ロード
データベースにバッチ処理でデータを挿入する処理。
ダイレクトロードインサート、SQL*Loaderユーティリティを使用する方法などがある。
再編成
データのフラグメンテーションや行移行の解消、エクステント統合を行うための処理。
同一データベース内で表から表へとデータをコピーする際、バッファキャッシュを迂回して、データをデータファイルに直接書きこむことで処理を高速化する手法。
ダイレクトロードインサートを行うと、データは全て高水位標より上の領域にロードされる。
NOLOGGING句を指定するとREDOエントリが作成されず、エクステント割り当てなどの最小限のログのみがREDOログファイルに記録される。そのため、実行後にデータファイルのバックアップをする必要がある。
ダイレクトロードインサートは、INSERT文にAPPENDヒントを指定する。
構文）
INSERT /*+APPEND */ INTO 表名 [LOGGING/NOLOGGING] SELECT文;
例）old_emp表のデータをnew_emp表にコピーする
INSERT /*+APPEND */ INTO new_emp SELECT * FROM old_emp;
INSERT文にPARALLELヒントを追加すると、ダイレクトロードインサートを同時に平行して行うことができる。
ただし、それ以前にALTER SESSION ENABLE PARALLEL DML文を発行しておく必要がある。
構文）
INSERT /*+PARALLEL */ INTO 表名 [LOGGING/NOLOGGING] SELECT文;
例）old_emp表のデータをパラレルモードでnew_emp表にコピーする
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+PARALLEL */ INTO new_emp SELECT * FROM old_emp;
外部からのファイルから、オラクルデータベースの表にデータを取り込むためのユーティリティ。
SQL*Loaderには次のような機能がある。
SQL*Loader
は、表の作成は行わず、既存の表にデータをロードするため、データをロードする表は、データベース中に存在している必要がある。
ロードする表にデータがすでに存在していても、または空であっても問題は生じない。
ロードを実行するには、次の権限が必要となる。
入力ファイル ：
データ・ファイル、制御ファイル、パラメータ・ファイル
出力ファイル ：
ログ・ファイル、破棄ファイル、不良ファイル
制御ファイル
データがロードされる表の名前、入力データ、フィールド仕様など、ロード処理を制御するために必要な情報を格納。
データファイル
制御ファイルで指定されている形式の、ロードの対象となるデータが記述される。
データは固定長でも可変長でも良い。
入力データは文字/バイナリ/パック10進数/日付/ゾーン10進数など、任意の形式にすることができる。
パラメータファイル
ロード時のコマンド行パラメータを定義する場合に使用する。
キーワード＝値という形でパラメータを指定。
不良データファイル
エラーが発生して
ロードできなかったデータが格納される。
ログファイル
データロードした結果の詳細を記述する。
ロード件数や処理時間、エラーコードなどを確認できる。
廃棄ファイル
条件に満たなくてロードの対象とならないデータが格納される。
例）EMP表にテキストデータ（data.dat）をロードする場合の制御ファイルを作成する。
＜EMP表の構成＞
CREATE TABLE EMP(
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
STATUS CHAR(1)
);
＜固定長テキストデータ（data.dat）の内容＞
7369SMITH CLERK 790219801217 80050 20 7499ALLEN SALESMAN 769819810220 160000 3000030 7521WARD SALESMAN 769819810222 125000 5000030 7566JONES MANAGER 783919810402 297500 20 7654MARTIN SALESMAN 769819810928 125000 14000030 7698BLAKE MANAGER 783919810501 285000 30 7782CLARK MANAGER 783919810609 245000 10 7788SCOTT ANALYST 756619870419 300000 20 7839KING PRESIDENT 19811117 500000 10 7844TURNER SALESMAN 769819810908 150000 00030 7876ADAMS CLERK 778819870523 110000 20 7900JAMES CLERK 769819811203 95000 30 7902FORD ANALYST 756619811203 300000 20 7934MILLER CLERK 778219820123 130000 10
＜固定長データロード制御ファイル（dataload.ctl）の内容＞
LOAD DATA ----- ① INFILE 'data.dat' ----- ② INSERT ----- ③ INTO TABLE EMP ----- ④ ( EMPNO POSITION(01:04) INTEGER EXTERNAL, ----- ⑤ ENAME POSITION(05:14) CHAR, ----- ⑥ JOB POSITION(15:23) CHAR, MGR POSITION(24:27) INTEGER EXTERNAL, HIREDATE POSITION(28:35) CHAR "to_date(:HIREDATE,'YYYYMMDD')", ----- ⑦ SAL POSITION(36:42) DECIMAL EXTERNAL ":SAL/100", ----- ⑧ COMM POSITION(43:49) DECIMAL EXTERNAL ":COMM/100", DEPTNO POSITION(50:51) CHAR, STATUS CHAR "0" ----- ⑨ )
①制御ファイルの先頭には、LOAD DATA文が必要。
②ロードの対象となるテキストデータのファイル名称をINFILEの後に指定。
③データをロードするモードを指定。モードには以下の４種類がある。
| INSERT | 空の表にデータを挿入。すでに表にデータが存在する場合はエラーとなり、ロードを終了する。 |
| APPEND | データを追加。すでに表にデータが存在する場合は、新規のデータのみが表にロードされる。 |
| REPLACE | 表の既存の行がすべて削除（delete）され、新規にデータがロードされる。 |
| TRUNCATE | 表の既存の行がすべて削除（truncate）され、新規にデータがロードされる。対象の表の参照整合性制約を使用不可にしておく必要がある。 |
④データがロードされる表を指定。
⑤ロードされる表の列名、その列にロードされるテキストデータのデータ位置、ロードするデータ型を指定。EMPNO列に数値型でデータを挿入する。
例）EMPNO列に'7369'（POSITIONに指定されている１バイト目から４バイト目のデータ）を数値型で挿入する。
7369SMITH CLERK 790219801217 80050 20
EMPNO POSITION(01:04) INTEGER EXTERNAL
(列名) (データ位置) (データ型)
⑥ENAME列に文字型でデータを挿入。
⑦HIREDATE列に文字型データを日付型に変換してデータを挿入。
⑧SAL列に小数点を含む数値型でデータを挿入。
例）SAL列に'80050'（POSITIONに指定されている３６バイト目から４２バイト目のデータ）を１００で割った'800.50'を数値型で挿入する
7369SMITH CLERK 790219801217 80050 20
SAL POSITION(36:42) DECIMAL EXTERNAL ":SAL/100"
(列名) (データ位置) (データ型) (データ加工式)
⑨STATUS列に'0'の文字列を固定で挿入。
＜可変長テキストデータ（data.dat）の内容＞
"7369","SMITH","CLERK","7902","19801217","80050","","20" "7499","ALLEN","SALESMAN","7698","19810220","160000","30000","30" "7521","WARD","SALESMAN","7698","19810222","125000","50000","30" "7566","JONES","MANAGER","7839","19810402","297500","","20" "7654","MARTIN","SALESMAN","7698","19810928","125000","140000","30" "7698","BLAKE","MANAGER","7839","19810501","285000","","30" "7782","CLARK","MANAGER","7839","19810609","245000","","10" "7788","SCOTT","ANALYST","7566","19870419","300000","","20" "7839","KING","PRESIDENT","","19811117","500000","","10" "7844","TURNER","SALESMAN","7698","19810908","150000","000","30" "7876","ADAMS","CLERK","7788","19870523","110000","","20" "7900","JAMES","CLERK","7698","19811203","95000","","30" "7902","FORD","ANALYST","7566","19811203","300000","","20" "7934","MILLER","CLERK","7782","19820123","130000","","10"
＜可変長データロード制御ファイル（dataload.ctl）の内容＞
LOAD DATA INFILE 'data.dat' INSERT INTO TABLE EMP FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ----- ①，② TRAILING NULLCOLS ----- ③ ( EMPNO, ENAME, JOB, MGR, HIREDATE "to_date(:HIREDATE,'YYYYMMDD')", ----- ④ SAL ":SAL/100", COMM ":COMM/100", DEPTNO, STATUS "0" )
①FIELDS TERMINATED句により、ロードの対象となるテキストデータのデータフィールドが区切られている文字を指定。
②OPTIONALLY ENCLOSED句により、指定された文字で挟まれたデータが読み込まれてフィールドに対応付けられる。
③テキストデータ中にデータが存在せず、相対位置で指定された列の値を処理するために使用。
④それぞれの列にデータを挿入。
構文）UNIX
SQLLDR [キーワード=]値 …
起動時には、必要なキーワードを指定する。
代表的なキーワード
| USERID | データをロードする表が存在するデータベースに接続するためのユーザー名，パスワードを指定 |
| CONTROL | 制御ファイル名を指定 |
| DATA | データファイル名を指定 |
| PARFILE | パラメータファイル名を指定 |
| LOG | ログファイル名を指定 |
| BAD | 不良ファイル名を指定 |
| DISCARD | 廃棄ファイル名を指定 |
| ERRORS | 最大不良レコード数を指定。 データロードの際にエラーが発生した件数が、ここで指定した数に達した時にロード処理を終了 |
| DIRECT | ダイレクトパスロードを指定（TRUE） |
| PARALLEL | パラレルダイレクトパスロードを指定（TRUE） |
SQL*Loader には、従来型パスとダイレクトパスの2 つのデータのロード方法がある。
データをロードするのにINSERT文を使用する。
したがって、通常のINSERT文による挿入処理同様、制約は全てチェックされ、INSERTトリガーも起動する。
レコード挿入後、データはCOMMITを使って保存され、REDOログエントリの生成は表のLOGGING属性によって制御される。
SQL*Loader
で従来型パスによるロードを実行する場合、バッファ・リソースに関して他のすべてのプロセスと同等の処理であるとみなされ、競合が生じる。このため、ロードにかなりの時間がかかる。
また、SQL コマンドが生成され、Oracle
に渡されてから実行されるので、さらにオーバーヘッドが発生する。
挿入が発生するたびに、Oracle
は空き領域のあるブロック（ディスク内に散在して、部分的に書込み可能なブロック）を探し、そこにデータを書き込む。通常のデータベース使用の場合はそれほどでもないが、このアクションは大量データのロード速度を大幅に低下させることがある。
従来型パスを経由してロードする表については、特に必要な条件はない。
従来型パス・ロードを使用する場合
例）起動例（UNIX）従来型
SQLLDR USERID=SCOTT/TIGER
CONTROL=DATALOAD.CTL LOG=DATALOAD.LOG BAD=DATALOAD.BAD
ダイレクト・パス・ロードでは、データベースバッファキャッシュを迂回し、表に割り当てられたエクステントに直接ブロックが保存される。
ロードデータはORACLEのデータブロックを単位として表の高水位標の後に書きこまれ、最後に高水位標が移動される。REDOログエントリは、データベースがアーカイブログモードの場合のみ作成される。
ダイレクト・パス・ロードによる処理は、従来型パス・ロードと比較すると非常に高速だが、制限事項がいくつかある。
主キー制約、一意制約、NOT NULL制約はチェックされるが、外部キー制約とチェック制約は使用禁止になる。また、クラスタ化された表には行をロードすることができない。
ロード中は、他のトランザクションが変更を行うことはできない。
ダイレクト・パス・ロード方法を使用する場合、一般的なロード条件の他に、次の条件が満たされている必要がある。
前述の制限のいずれにも該当せず、且つ、次のようなときにダイレクト・パス・ロードを使用。
例）起動例（UNIX）ダイレクトパスロード
SQLLDR USERID=SCOTT/TIGER
CONTROL=DATALOAD.CTL LOG=DATALOAD.LOG BAD=DATALOAD.BAD DIRECT=TRUE
EXPORT/IMPORTはOracleデータベース間のデータの移動や、バックアップに利用されるユーティリティ。
相互に補足しあっており、 EXPORTユーティリティは、Oracleデータベースのデータを、オペレーティング・システムのファイルに書き込み、IMPORTユーテリティは、EXPORTファイルを読み、Oracleデータベースに書き戻す。
EXPORT/IMPORTユーテリティは、一般的に、次のような用途に利用される。
EXPORTユーティリティは、Oracle Enterprise Manager を使用することができるが、一般的にはEXPコマンドを使用する。
代表的なパラメータ
| USERID | エクスポート処理を実行するユーザー名 / の後に、パスワードも記述する。 |
| BUFFER | データを取り出すために使用するバッファのバイト数 |
| FILE | エクスポート・ユーティリティで作成するファイル名 デフォルト：EXPDAT.DMP |
| GRANTS | 権限をエクスポートするかどうか（
Y / N ) デフォルト：Y |
| INDEXES | 索引をエクスポートするかどうか（
Y / N ) デフォルト：Y |
| ROWS | データ行をエクスポートするかどうか（
Y / N ) デフォルト：Y |
| CONSTRAINTS | 制約をエクスポートするかどうか（
Y / N ) デフォルト：Y |
| COMPRESS | インポート処理中に、データを
1エクステントにまとめるかどうか（ Y / N ) デフォルト：Y |
| DIRECT | ダイレクトパスエクスポートをするかどうか（ Y / N ) デフォルト：N データを直接エクスポートクライアントに転送するため、従来型よりも高速 |
EXPORTの選択モード
| FULL / OWNER / TABLES |
EXPORT時に取り出すオブジェクトの単位を指定する。 以下の選択モードのいずれかを選択する。
|
例1） 全データベースのエクスポート
ユーザーsystem でエクスポートを実行。データベース全体をエクスポートし、ファイルora8all.dmp に出力。exp system/manager FULL=Y FILE=ora8all.dmp
例2） 特定表のエクスポート
ユーザーmate01 が所有する表 empとdept をエクスポート。権限と索引もエクスポートする。exp mate01/oracle8 TABLES=emp,dept GRANTS=Y INDEXS=Y
EXPORTユーティリティは、FULL=Yを指定して、データベース全体のバックアップとして利用することもできる。
バックアップを効率的に行うために、バックアップ後に変更された差分だけを取得することもできる。
これらはEXP実行時に、INCTYPEパラメータで指定する。
ただし、全データベース・モード（FULL=Y）でのみ、増分、累積、全エクスポートを実行することができる。
| 増分エクスポート | 前回エクスポートした以降に更新されたオブジェクトだけエクスポートする。 パラメータ ： INCTYPE = INCREMENTAL |
| 累積エクスポート | 前回、全エクスポートまたは累積エクスポートした以降に更新されたオブジェクトをエクスポートする。 パラメータ ： INCTYPE = CUMULATIVE |
| 全エクスポート | 増分と累積エクスポートを実現するために使用する。全データベースをエクスポートする。 パラメータ ： INCTYPE = COMPLETE |
例3） 増分エクスポート
ユーザーsystem で、増分エクスポートexp system/manager INCTYPE=INCREMENTAL
IMPORTユーティリティは、Oracle Enterprise Manager を使用することができるが、一般的にはIMPコマンドを使用する。
代表的なパラメータ
| USERID | インポート処理を実行するユーザー名 / の後に、パスワードも記述する。 |
| BUFFER | データを取り出すために使用するバッファのバイト数 |
| FILE | インポート・ユーティリティで使用するファイル名 デフォルト：EXPDAT.DMP |
| SHOW | エクスポート・ファイルの内容をリストするだけかどうか（
Y / N ) デフォルト：N |
| IGNORE | インポートしているオブジェクトが既に存在していた場合の対応方法 Y :行が既存の表にインポートされる。 N :エラーがレポートされ、スキップされる。（デフォルト） |
| GRANTS | 権限をインポートするかどうか（
Y / N ) デフォルト：Y |
| INDEXES | 索引をインポートするかどうか（
Y / N ) デフォルト：Y |
| ROWS | データ行をインポートするかどうか（
Y / N ) デフォルト：Y |
| FULL | インポート時に取り出すオブジェクトの単位を指定する。 全データベース・モードのインポート |
| FROMUSER | インポートされるオブジェクトのスキーマ ユーザー・モードのインポート |
| TOUSER | インポートするオブジェクトのスキーマ |
| TABLES | 表モードのインポート |
例1） 特定ユーザーのインポート
ユーザーsystem でscottのオブジェクトを、mate01のスキーマにインポートimp system/manager FROMUSER=scott TOUSER=mate01
IMPORTユーティリティは、障害発生時のデータ回復方法として利用することもでき、増分、累積、全エクスポートされたファイルを、効果的に取り込むことができる。
IMP実行時に、INCTYPEパラメータで指定。
| SYSTEM | 前回の増分エクスポート・ファイルを使用して、最新のシステム・オブジェクトをインポートする。 |
| RESTORE | 時系列順に、エクスポート・ファイルを適用する。 |
例2） エクスポート/インポートの運用例
データ管理者として、次のようなバックアップを実施。
- 3週間ごとに全エクスポート（ｘ）
- 週末ごとに累積エクスポート（ｃ）
- 毎晩の増分エクスポート（ｉ）
実施カレンダー
月 火 水 木 金 土 日 1 2 3 4 5 6 7 ｘ ｉ ｉ ｉ ｉ ｉ ｃ 8 9 10 11 12 13 14 i i i i i i c 15 16 i 障害
16日に障害が発生した場合、インポート・ユーティリティを使用して、15日までの時点に戻す。
7日の累積エクスポート・ファイルには、2日～6日までの増分エクスポートの内容が含まれる。
2日～6日までの増分エクスポートは、破棄することができる。
14日の累積エクスポート・ファイルと、8日～13日までの増分エクスポートも同様の関係。
2001/09/21 担当：R.S