【Oracle】DBリンクを使ってテーブルのリフレッシュを手軽にDELETE/INSERTで行う

役立つシーン

• 複数のデータベース間でデータを共有したいとき
• 本番DB・本番テーブルのデータをテストDBにある全く同一の構造のテーブルに持ってきて、本番データと同じデータで開発やテストを行いたい時
  • テスト環境の最新化など

初めて知った時にもっと早く知りたかった！と思ったので、書き残しておきます

データベース・リンクとは

データベース・リンクとは、他のデータベース上のオブジェクトにアクセスできる、データベース上のスキーマ・オブジェクトです。
他のデータベースは、Oracle Databaseシステムである必要はありません。ただし、Oracle以外のシステムにアクセスする場合は、Oracle異機種間サービスを使用する必要があります。

• データベース・リンクとはあるデータベースに存在するテーブルと、他のデータベースに存在するテーブルとをスキーマの論理構造によって紐づけ、相互にテーブル同士でアクセスしあえるようにする方法
  • リンクさせることで、他のDBにあるテーブルなどを、自分のDBにあるように扱うことができる
    • つまり、リンクを貼ることでDBやスキーマの影響を受けずデータを扱うことが可能になる
• 略称は「DBリンク」（でーびーりんく）と呼ばれている（観測範囲では）

データベース・リンクの確認・作成・削除

確認

• DBリンクを作成する前に、今開いているDBのスキーマとどのDBがすでにDBリンクされているか確認することができる
• 自分がDBリンクを作成した後、実際に作成できているか確認することができる

構文

SELECT * FROM ALL_DB_LINKS;

ALL_DB_LINKSについて

• OracleのALL_DB_LINKSをSELECT対象として指定すると、今開いているDBのスキーマのDBリンクを確認することができる（そういうOracleの仕様）
  • なお、ALL_DB_LINKSはビューであり、このようなビューをOracleデータベースの「データディクショナリービュー（Data Dictionary Views）」という

ALL_DB_LINKSは、現行のユーザーがアクセスできるデータベース・リンクを示します。

作成

• 任意のデータベースのテーブルと、異なる任意のデータベースのテーブルをリンクさせることができる
• 職場では影響範囲が大きいため、個人の判断で貼ることはしない

構文

CREATE DATABASE LINK <データベースリンク名>
CONNECT TO <接続先のユーザー名> IDENTIFIED BY <接続先のパスワード>
USING '<データベースサーバのIPアドレス/接続先のデータベースサービス名>';

• SQL文と区別するために<>を記載していますが、実際のSQLに書く必要はありません
• <データベースサーバのIPアドレス/接続先のデータベースサービス名> (=DB接続名) は「'」（シングルクオーテーション）で囲う必要があります
• なお、パブリックデータベースリンク (=全てのユーザがアクセス可能なデータベースリンク) を作成する時は「CREATE PUBLIC DATABASE LINK」で作成します

削除

• 作成されているDBリンクを削除することができる
• 職場では影響範囲が大きいため、個人の判断で削除することはしない

構文

DROP DATABASE LINK <データベースリンク名>;

• パブリックデータベースリンク (=全てのユーザがアクセス可能なデータベースリンク) を削除する時は「DROP PUBLIC DATABASE LINK」で削除します

データベース・リンクを使ってテーブルのリフレッシュを手軽に行う方法

ここでは、DBリンクの貼られた2つのDBの全く同名称・同構造のテーブルにデータをコピーして追加する方法を記載します。

例：本番環境と全く同じ構造のテスト環境のテーブルに、本番環境からデータを持ってきて入れたい時

フルーツテーブル🍎を例に説明します。

本番DB : PROD.TBL_FRUIT

ID	FRUIT_NM	SEASON
1	りんご	秋
2	みかん	冬
3	すいか	夏
4	いちご	春

テストDB : TEST.TBL_FRUIT

• 開発中にデータをいじったりしていた場合、ID = 15, FRUIT_NM = すいか2 のような適当に作ったゴミが混ざっていたりする
• 本番DBには ID = 4, FRUIT_NM = いちご のデータがあるが、テストDBには開発途中で消したり最新データが取ってこれていなかったりして、入っていない

ID	FRUIT_NM	SEASON
1	りんご	秋
2	みかん	冬
3	すいか	夏
15	すいか2	夏

1. 一方のテーブルからデータを追加したい方のテーブルのデータを一度全削除する

構文

DELETE FROM <データを入れたいほうのデータベース名>;

例

-- テストDBにログインしてSQLを実行
DELETE FROM TBL_FRUIT;

実行後のテーブル状態

テストDB : TEST.TBL_FRUIT
中身をDELETEしたので、構造は残るが中身はNULLになる

ID	FRUIT_NM	SEASON

本番DB : PROD.TBL_FRUIT
いじってないので中身そのまま

ID	FRUIT_NM	SEASON
1	りんご	秋
2	みかん	冬
3	すいか	夏
4	いちご	春

削除してしまうとテーブルからデータはなくなってしまうため、必要に応じて退避させてください

2. 一回COMMIT

3. 1でデータを全削除したテーブルに、DBリンクしている同構造のテーブルからデータを追加

構文

INSERT INTO <データを入れたいほうのデータベース名> SELECT * 
FROM <データを持ってきたいデータベース名>@<DBリンクしているデータベース名>;

例

-- テストDBにログインした状態でSQLを実行
INSERT INTO TBL_FRUIT SELECT * FROM TBL_FRUIT@PROD;

実行後のテーブル状態

テストDB : TEST.TBL_FRUIT
TEST.TBL_FRUIT に PROD.TBL_FRUIT のデータがそのまま入る

ID	FRUIT_NM	SEASON
1	りんご	秋
2	みかん	冬
3	すいか	夏
4	いちご	春

本番DB : PROD.TBL_FRUIT

ID	FRUIT_NM	SEASON
1	りんご	秋
2	みかん	冬
3	すいか	夏
4	いちご	春

4. もう一回COMMIT

完了！

🔗参考Link

なお、もちろんDBリンクを貼るのにも権限もろもろが必要なため、そのあたりは自分の環境を確認してください。