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

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

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

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

https://docs.oracle.com/cd/E16338_01/server.112/b56299/statements_5005.htm

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

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

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

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

``` :構文
SELECT * FROM ALL_DB_LINKS;
```
<br>

**ALL_DB_LINKSについて**

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

https://docs.oracle.com/cd/E16338_01/server.112/b56311/statviews_1067.htm

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2632896/575dae15-0ef6-f629-921c-2954623db15f.png)


<br>

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

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


<br>

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

```:構文 
DROP DATABASE LINK <データベースリンク名>;
```
:::note warn
- パブリックデータベースリンク (=全てのユーザがアクセス可能なデータベースリンク) を削除する時は「DROP **PUBLIC** DATABASE LINK」で削除します
:::

<br>

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

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

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

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

**本番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 | いちご | 春 |

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

### 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

https://docs.oracle.com/cd/E16338_01/server.112/b56311/statviews_1067.htm

https://oracle-chokotto.com/ora_databaselink.html

https://qiita.com/hakuaneko/items/f988e0052e3b8f692482

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

<br>