Zenn
🌟

ORACLE MASTER Silver DBA 取得に必要な知識のまとめ～データの移動編～

2024/06/18に公開
Oracle
tech

はじめに

本記事はORACLE MASTER Silver DBA取得に必要な知識の個人的まとめになります。

ORACLE MASTERとは

Oracle Databaseの管理スキルを証明するOracle社の資格です。

Bronze, Silver, Gold, Platinumの4ランクあります。

Oracle Data Pump

Oracle Data PumpはデータをOracle Database外部にエクスポート、あるいはOracle Database内部にインポートするツールです。

Oracle Databaseカンのデータの移動や、現在のデータのバックアップに活用できます。

Data Pumpのアーキテクチャは以下の通りです。

  • エクスポート処理を起動するためにコマンドラインツールexpdpを使用します
    • expdpがデータベースサーバーで起動することも、データベースサーバー以外のマシンで起動することも可能
  • expdpはデータベースサーバーまたはデータベースざーばー以外のマシンで実行可能ですが、エクスポート処理の本体はデータベースサーバー上で実行されます
  • ダンプファイルはあらかじめ構成したディレクトリオブジェクトに対応するOSのディレクトリに出力されます
    • ディレクトリオブジェクトはデータベースサーバー上のOSのディレクトリをOracle Databaseから使用するための仕組みです
  • インポート実行時は逆の動作となり、指定したディレクトリのダンプファイルから表にデータをロードされます
    • コマンドラインツールimpdpを使用する
  • Data Pumpの処理状況はOracle Databse内のマスタ内に記録されます

Data Pumpを使用するにはダンプファイルの入出力先となるOSのディレクトリに対応するディレクトリオブジェクトをあらかじめ作成しておく必要があります。

ディレクトリオブジェクトは CREATE DIRECTORY で作成します。

Data Pumpの処理対象となるデータの範囲に応じて、Data Pumpには以下の5つの動作モードが存在します。

モード 説明
スキーマモード SCHEMASパラメータで指定されたスキーマのすべてのオブジェクトをエクスポート・インポートする
表モード TABLESパラメータで指定された表のみをエクスポート・インポートする
表領域モード TABLESPACESパラメータで指定された表領域に格納されているオブジェクトをエクスポート・インポートする
全体モード データベース全体をエクスポート・インポートする。FULLパラメータでこのモードを指定する
トランスポータブル表領域モード データベース間で表領域を移行するために使用する。TRANSPORT_TABLESPACESパラメータでこのモードを指定する

動作モードを指定するパラメータが指定されなかった場合、スキーマモードで動作し、Oracle Databaseへ接続したユーザーのスキーマのすべてのオブジェクトをエクスポート・インポートします。

Data OUmpを起動して動作パラメータを指定する方法（インターフェイス）には以下の3つの方法があります。

モード 説明
コマンドライン コマンド名に続いて、オプションを指定する
パラメータファイル パラメータを記載したテキストファイルにオプションを指定する
対話型 シェル類似のインターフェイスにコマンド入力してオプションを指定する。実行中の処理を一時停止・再開する場合に有用

動作モード以外の主なパラメータを以下に示します。

パラメータ 説明
DUMPFILE ダンプファイル名を指定する。 <ディレクトリ名>: という書式で併せてディレクトリ名を指定することもできる
DIRECTORY ダンプファイルを配置するディレクトリ名を指定する。DUMPFILEにディレクトリ名を指定している場合は指定不要
FILESIZE ダンプファイルの最大サイズを指定する
LOGFILE 処理結果を記録するログファイル名を指定する。<ディレクトリ名>: という書式で併せてディレクトリ名を指定することもできる
REMAP SCHEMA エクスポート時のスキーマとは別のスキーマにインポートするときに指定する。 <変換元スキーマ><変換後スキーマ> という書式でスキーマ変換の対応関係を指定する
EXCLUDE 除外したい処理対象を指定する
INCLUDE 含めたい処理対象を指定する
TABLE EXISTS_ACTION インポート時にすでに表が存在する場合の動作を指定する。以下を指定可能。
・ SKIP：対象表へのインポート処理をスキップし、別の表の処理を継続する
・APPEND：既存のデータは変更せず、ダンプファイルのデータを追加するかたちでインポートする
・TRUNCATE：既存のデータをすべて削除した後、ダンプファイルのデータをインポートする
・REPLACE：既存の表を削除した後、表を作成してからデータをロードする
PARALLEL パラレル処理の並列度を指定する。指定した数のプロセスが起動して処理を並列実行する
JOB_NAME 実行する処理に対して明示的にジョブ名を指定する。ジョプ名を指定しない場合、 SYS_EXPORT_ から始まるジョブ名が自動的に付与される

DUMPFILEパラメータに指定するファイル名には、置換変数(%U)を含めることができます。置換変数を使うと、複数のダンプファイルが作成される場合にそれぞれのファイル名を自動的に生成できます。

置換変数に指定した部分は 01 から 99 まで増加する固定幅の2桁の整数になります。

この機能はFILESIZEパラメータでダンプファイルの最大サイズを指定している場合やPARALLELパラメータでパラレル処理を指定している場合に有用です。

SQL *Loader

SQL *LoaderはOSファイルシステム上のデータをOracleデータベースの表にロードするツールです。

SQL *LoaderはCSVなど多くの形式のデータに対応しています。

SQL *Loaderの留意点は以下の通りです。

  • SQL *Loaderを起動するにはコマンドラインで sqlldr を実行します
  • ロード先の表明やデータファイルの形式など、実行するロード処理の内容を制御ファイルに記載します
  • 制御ファイルの記載内容に従い、データファイルのデータがOracle Databaseの表にロードされます
  • ロード処理を実行すると、実行された処理に応じて、ログファイル、廃棄ファイル、不良ファイルが出力されます

SQL *Loaderの処理に関わるファイルの役割は以下の通りです。

対象 説明
データファイル ロード対象のデータを含むファイル。SQL *Loaderではデリミタで区切られたテキストファイル、固定長のテキストファイル、バイナリファイルなどの様々なデータファイルをロードできる
制御ファイル データをロードする表やデータファイルの形式と列のマッピングなどを記述する
ログファイル SQL *Loaderの動作内容が出力されるログファイル
廃棄ファイル ロード対象外のレコードが出力されるファイル。パラメータで明示的にファイル名をしてしない限り出力されない
不良ファイル エラーによりロードできなかったレコードが出力されるファイル。エラーが1件もない場合、不良ファイルは出力されない

通常、SQL *Loaderを用いてデータをロードするには制御ファイルを用意する必要がありますが、SQL *Loaderエクスプレスモードでは制御ファイルなしでデータをロードできます。

SQL *Loaderエクスプレスモードでデータをロードするには、コマンドラインのTABLEパラメータにロード対象の表名を指定します。TABLEパラメータの指定は必須です。デフォルトでは TABLEパラメータで指定した表名.dat がデータファイルのファイル名に使用されます。

SQL *Loaderエクスプレスモードの留意点は以下の通りです。

  • ロード対象の表の列データ型は、文字、数値、日時である必要があります
  • データファイルは、CSVファイル、タブ区切りファイルなどの特定の区切り文字（デリミタ）で区切られたテキスト形式のファイルである必要があります
    • デフォルトの区切り文字は , です
    • コマンドラインのTERMINATED_BYパラメータで任意の区切り文字を指定可能です
  • コマンドラインのDATAパラメータにデータファイルのファイル名を指定できます
    • DATAパラメータを指定しないと、データファイルのファイル名には TABLEパラメータで指定した表名.dat が使用されます
  • ロード対象の表に存在しているデータは削除されず、ロード対象のデータが追加されるAPPENDモードでロードされます
  • デフォルトではロードの並列度が自動的に設定されます

エクスプレスモードでは、まず外部表を用いたロード方法が実行可能か施行され、外部表ロードが実行できない場合はダイレクトパスロードでロードされます。

外部表ロードの場合、内部的に以下の処理が実行されます。

  1. SQL *LoaderデータファイルがあるOSディレクトリに対応するディレクトリオブジェクトを作成します
  2. 1.で作成したディレクトリオブジェクトを使用して、SQL *Loaderデータファイルのデータを読み出せる外部表を作成します
  3. 2.で作成した外部表から、ロード先の表にデータをINSERTします
  4. 作成した外部表、ディレクトリオブジェクトを削除します

上記の通り、外部表ロードを実行するにはディレクトリオブジェクト及び外部表を作成できる必要があります。これらの作成権限がない場合、エクスプレスモードではロード方法を外部表からダイレクトパスロードに自動的に切り替えます。

エクスプレスモードでロード処理を実行して生成されるログファイルには、以下の情報が記録されます。

  • 実行した処理に対応するSQL *Loader制御ファイルの内容
    • 同様の処理を通常のSQL *Loader操作で実行する場合に必要となる制御ファイルを作成するために活用できます
  • 外部表ロード実行時に自動的に内部で実行されるSQL文の情報
    • 具体的には作成したディレクトリオブジェクトの情報、外部表を作成するための CREATE TABLE ORGANIZATION EXTERNAL など

外部表

外部表とは、OSファイルシステム上のファイルをあたかも、データベース上の表のように扱うことができる仕組みです。たとえば、あるCSVファイルに対して、 SELECT を実行できます。

データの実体は外部ファイル内にあり、データベース上には存在しません。このため、外部表は外部ファイルを実表としたビューのようなものと考えることができます。

外部表には機能が異なるいくつかのアクセスドライバが用意されています。

ORACLE_LOADERアクセスドライバを用いると、SQL *Loaderでロードできる形式の外部ファイルを外部表としてアクセスできるようになります。

ORACLE_DATAPUMPアクセスドライバの外部表を活用すると、問い合わせ結果をダンプだイルに出力し、そのダンプファイルを別のOracle Databaseで外部表を介して読み取ることが可能です。

なお、外部表でロードできる断碑ファイルはORACLE_DATAPUMPアクセスドライバの外部表機能で出力して生成されたダンプファイルのみです。（Data Pumpの expdp でエクスポートされたダンプファイルはロードできない）

外部表のデータは読み取り専用です。変更処理は実行できず、索引を作成することはできません。

また、外部表はパーティション化できます。パーティション化とは、1つのオブジェクトを複数に分割する仕組みです。

外部表をパーティション化すると、データのI/O量を削減し、処理を高速化できる場合があります。

おわり

データの移動編はここまで。

次はシーケンス、シノニム、索引について説明していきます。

Discussion