Oracleデータ移行

🚀 Oracle Data Pump とは？

Oracle Data Pump は、データおよびメタデータを非常に高速に他のデータベースへ移動するためのプロトコルです。
データ移行に使う主なツールは：
- expdp（データエクスポート）
- impdp（データインポート）
これらは Oracle のコマンドラインツールまたは Enterprise Manager から利用可能です。

🧪 実践：エクスポートの準備

1️⃣ OS上にエクスポートファイル用のディレクトリ作成

bash
mkdir /your/directory/path

2️⃣ Oracleデータベースにディレクトリオブジェクトを作成

sql
CREATE DIRECTORY dump_dir AS '/your/directory/path';

データポンプはこのディレクトリを通じてファイル入出力を行う。

📤 エクスポートの実行（例）

特定スキーマのエクスポート

bash
expdp user/password@pdb1 schemas=HR directory=dump_dir dumpfile=hr_export.dmp logfile=hr_export.log

schemas パラメータで対象スキーマを指定。
directory は先ほど作成したディレクトリオブジェクト。
dumpfile 出力ファイル名、logfile は処理ログ。

🔍 ジョブの確認・監視

実行中のData Pumpジョブは、次のビューで確認：

sql
SELECT * FROM dba_datapump_jobs;

エクスポート完了後、ジョブ情報は削除される。

📋 エクスポートログの内容

最初に マスターテーブル が作成される（内部的なジョブ制御に使用）
マスターテーブルは最終的にダンプファイルに保存され、正常終了後に削除される
ログには：
- エクスポート対象テーブルと行数
- インデックス、ユーザー、権限などの情報も記載

🎯 条件付きエクスポート

1️⃣ テーブル単位のエクスポート

bash
expdp user/password tables=hr.employees,hr.locations directory=dump_dir dumpfile=tables.dmp logfile=tables.log

2️⃣ 除外（exclude）オプション（パラメータファイル使用推奨）

text
# parfile.txt
schemas=HR
exclude=table:"IN ('JOBS', 'DEPARTMENTS')"

bash
expdp user/password parfile=parfile.txt

3️⃣ クエリで条件抽出

bash
expdp user/password tables=hr.employees query="WHERE department_id = 60" directory=dump_dir dumpfile=partial.dmp

📦 その他のモード

モード	説明
Full	データベース全体をエクスポート
Schemas	特定スキーマ
Tables	特定テーブル
Tablespaces	表領域単位
Transportable	表領域を他DBへ移送

✅ 推奨事項

コマンドラインでの直接指定よりも、パラメータファイルで管理する方が柔軟で再利用性が高い。
本番環境での運用前に、テスト環境で各オプションを試すことを推奨。

🧾 ダンプファイルの中身を確認する方法

✅ 中身を確認するには？

impdp コマンドに SQLFILE オプションを使って実行
→ 実際にはインポートせず、SQL文をファイル出力する

bash
impdp user/password dumpfile=hr_export.dmp directory=dump_dir sqlfile=hr_export.sql

出力された .sql ファイルを開くと、以下のような内容が確認できる：
- CREATE USER
- GRANT 文
- CREATE TABLE 文など

🧪 スキーマのインポートと変換操作

1️⃣ Tablespace のリマップ（`REMAP_TABLESPACE`）

例：PDB1 にある HR スキーマを PDB2 にインポートし、表領域を変更する

bash
impdp user/password dumpfile=hr_export.dmp directory=dump_dir remap_tablespace=six_oks:users

元々の six_oks 表領域 → 新たに作成した users 表領域へ移行
結果：全テーブルが users 表領域に作成されていることを確認

2️⃣ スキーマ名のリマップ（`REMAP_SCHEMA`）

例：HR スキーマを HR_TEST という別のスキーマにインポート

bash
impdp user/password dumpfile=hr_export.dmp directory=dump_dir remap_schema=HR:HR_TEST

結果：HR_TEST ユーザーが作成され、HR スキーマの全データがその中にインポートされる

🔁 エクスポート → インポートを1ステップで（`NETWORK_LINK`）

2段階（expdp → impdp）ではなく、データベースリンクを使って直接コピーすることも可能。

手順：

PDB2 側で PDB1 へのデータベースリンクを作成

sql
CREATE DATABASE LINK pdb1_link CONNECT TO user IDENTIFIED BY password USING 'pdb1';

impdp でネットワークリンクを使って直接インポート

bash
impdp user/password network_link=pdb1_link schemas=HR remap_schema=HR:HR_TEST

結果：.dmpファイルを使わずに、PDB1 から PDB2 へ直接データを移行可能

🔒 権限に関する注意点

このレッスンでは、実演のためにユーザーに強い権限（FULL EXPORT/IMPORT）を付与
本番環境では管理者ユーザーにのみ付与すべき

📥 CSVファイルからデータをロードする方法（第1回）

🛠 方法1：LinuxコマンドでINSERT文を自動生成

空のテーブル locations を作成
→ データを格納する準備をする。
Linuxのコマンドを使用してCSVからSQLを生成
- cat コマンドでCSVファイルを表示
- sed コマンドで "（ダブルクォート） を '（シングルクォート） に置換
- awk コマンドで各行を INSERT INTO locations VALUES (...) 形式に変換
- 結果を locations.sql というファイルに保存
SQLスクリプトを実行
- Oracle Cloud Console やローカル接続からPDBに接続
- @locations.sql を実行してデータ挿入
データ確認
- SELECT * FROM locations; でテーブルの中身を確認
- CSVファイルからのデータが正しく格納されていることを確認

💡補足

この手法はクラウド環境でもローカル環境でも利用可能
CSVファイルはこのレッスンのリソースセクションからダウンロード可能

📄 Oracle 外部表（External Table）を使ってCSVファイルを読み込む

非クラウド（ローカル）環境での外部表の設定方法

🛠 実施手順

1. 📁 CSVファイルの準備

ローカルサーバにlocations.csvファイルを配置
値からダブルクォーテーション（"）を削除しておく
CSVファイルはOracleサーバーの物理ディレクトリに保存

2. 📂 Oracleのディレクトリオブジェクト作成

Oracleにログインして、CREATE DIRECTORY文で論理名を作成（例：LOC_DIR）
ALL_DIRECTORIESビューで確認可能

sql
CREATE DIRECTORY loc_dir AS '/path/to/your/csv/files';

3. 🧾 外部表（External Table）の作成

通常の表定義に加えて、ファイルの読み取り方法を記述する必要あり
Oracle Loader Driverを使用し、CSVのフィールド区切り（例：,）を指定

sql
CREATE TABLE locations_ext (
  location_id     NUMBER,
  street_address  VARCHAR2(40),
  postal_code     VARCHAR2(12),
  city            VARCHAR2(30),
  state_province  VARCHAR2(25),
  country_id      CHAR(2)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY loc_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
  )
  LOCATION ('locations.csv')
)
REJECT LIMIT UNLIMITED;

4. 🔎 データの確認

通常のSQLで外部表にアクセス可能：

sql
SELECT * FROM locations_ext;

データはCSVから直接読み込まれ、データベースにロードされることはない
外部表は読み取り専用（更新・挿入・削除は不可）

✅ メリット・注意点

項目	内容
✅ メリット	CSVの内容をSQLでそのまま参照可能
⚠️ 注意点	書き込み不可（READ ONLY）、CSVファイルの構造に制限あり

☁️ クラウド環境での外部表作成

🔒 背景と課題

クラウド環境ではHTTPデータベースへの直接アクセスが制限されているため、
前回のローカル環境での外部表作成手順は使用不可
しかし、オブジェクト・ストレージは使用可能

📦 オブジェクト・ストレージとは？

データを「ファイル」ではなく「オブジェクト」として管理
クラウドで一般的なストレージ方式

🛠 外部表作成の手順（クラウド編）

1. 🔑 アクセストークンの発行

Oracle Cloud コンソールにログイン
「Identity & Security → Users」に進む
自分のユーザーを選択し、「Auth Token（認証トークン）」を生成
　→ 理由の記述（例：外部表作成用）

2. 📘 クレデンシャルの作成（DBMS_CLOUDパッケージ使用）

sql
BEGIN
  DBMS_CLOUD.create_credential(
    credential_name => 'API_TOKEN',
    username        => 'your_email@example.com',
    password        => '発行したトークン'
  );
END;

3. 🗂 バケットとCSVファイルの準備

Oracle Cloudの「Object Storage」でバケットを作成
作成したバケットに CSVファイル（例：locations.csv）をアップロード
ファイルの「オブジェクトURL（URI）」をコピー

4. 📄 外部表の作成（CREATE_EXTERNAL_TABLE プロシージャ）

sql
BEGIN
  DBMS_CLOUD.create_external_table(
    table_name      => 'LOCATIONS_EXT',
    credential_name => 'API_TOKEN',
    file_uri_list   => 'https://objectstorage.../locations.csv',
    format          => json_object('delimiter' value ','),
    column_list     => 'location_id NUMBER, street_address VARCHAR2(40), ...'
  );
END;

5. ✅ 動作確認

sql
SELECT * FROM LOCATIONS_EXT;

正常にデータが取得できれば、クラウド環境での外部表の作成に成功です

🎉 まとめ

ローカル環境ではサーバー上のCSVファイルを使用
クラウド環境ではオブジェクトストレージを使用し、トークン認証とDBMS_CLOUDパッケージで管理
これにより、どちらの環境でも柔軟にCSVデータを外部表として活用可能

📦 **SQL*Loader** データロード

🧰 SQL*Loaderとは？

Oracleデータベースのテーブルにデータを取り込むユーティリティ
柔軟なデータ解析エンジンを備えており、データ形式の制限が少ない
使用する主なファイル：
- 📄 データファイル：CSVなどの実データ
- ⚙️ コントロールファイル：データの構造、配置先のテーブルなどを定義
- 📝 パラメータファイル（任意）：共通のオプション設定

🪜 手順まとめ

1. 🔧 データ取り込み先テーブルの作成

sql
CREATE TABLE locations (...);
-- テーブルにまだ0件

2. ▶️ SQL*Loader の実行

bash
sqlldr userid=user/pass control=control_file.ctl

control_file.ctl に必要なロード設定が記述されている

3. 📈 結果の確認

ログ出力により、23件が正常にロードされ、1件が失敗
失敗したレコードは「badファイル」に出力される
　→ 形式エラーなどでSQL*LoaderやDBが拒否した行
logファイルも作成され、実行結果・エラーの詳細が記録されている

4. 🧪 テーブルの中身を確認

sql
SELECT COUNT(*) FROM locations;
-- 23件確認できる

🎉 まとめ

SQL*Loaderは大量データのバルクロードに最適
コントロールファイルで柔軟な定義が可能
失敗行はbadファイル、詳細はlogファイルで確認可能

前の記事（Oracle Data Guard）

次の記事：Group関数・Group by句・Having句

Oracleデータ移行

🚀 Oracle Data Pump とは？

🧪 実践：エクスポートの準備

1️⃣ OS上にエクスポートファイル用のディレクトリ作成

2️⃣ Oracleデータベースにディレクトリオブジェクトを作成

📤 エクスポートの実行（例）

特定スキーマのエクスポート

🔍 ジョブの確認・監視

📋 エクスポートログの内容

🎯 条件付きエクスポート

1️⃣ テーブル単位のエクスポート

2️⃣ 除外（exclude）オプション（パラメータファイル使用推奨）

3️⃣ クエリで条件抽出

📦 その他のモード

✅ 推奨事項

🧾 ダンプファイルの中身を確認する方法

🧪 スキーマのインポートと変換操作

1️⃣ Tablespace のリマップ（REMAP_TABLESPACE）

2️⃣ スキーマ名のリマップ（REMAP_SCHEMA）

🔁 エクスポート → インポートを1ステップで（NETWORK_LINK）

手順：

🔒 権限に関する注意点

📥 CSVファイルからデータをロードする方法（第1回）

🛠 方法1：LinuxコマンドでINSERT文を自動生成

💡補足

📄 Oracle 外部表（External Table）を使ってCSVファイルを読み込む

🛠 実施手順

1. 📁 CSVファイルの準備

2. 📂 Oracleのディレクトリオブジェクト作成

3. 🧾 外部表（External Table）の作成

4. 🔎 データの確認

✅ メリット・注意点

☁️ クラウド環境での外部表作成

🔒 背景と課題

📦 オブジェクト・ストレージとは？

🛠 外部表作成の手順（クラウド編）

1. 🔑 アクセストークンの発行

2. 📘 クレデンシャルの作成（DBMS_CLOUDパッケージ使用）

3. 🗂 バケットとCSVファイルの準備

4. 📄 外部表の作成（CREATE_EXTERNAL_TABLE プロシージャ）

5. ✅ 動作確認

🎉 まとめ

📦 SQL*Loader データロード

🧰 SQL*Loaderとは？

🪜 手順まとめ

1. 🔧 データ取り込み先テーブルの作成

2. ▶️ SQL*Loader の実行

3. 📈 結果の確認

4. 🧪 テーブルの中身を確認

🎉 まとめ

1️⃣ Tablespace のリマップ（`REMAP_TABLESPACE`）

2️⃣ スキーマ名のリマップ（`REMAP_SCHEMA`）

🔁 エクスポート → インポートを1ステップで（`NETWORK_LINK`）

📦 **SQL*Loader** データロード