【Oracle】DBリンクの作成方法完全ガイド｜CREATE DATABASE LINK・PUBLIC/PRIVATE・Easy Connect・パフォーマンス・エラー対処まで解説

-- 構文
CREATE [PUBLIC] DATABASE LINK link_name
CONNECT TO remote_user IDENTIFIED BY remote_password
USING 'connect_string';

-- connect_string: tnsnames.ora のエントリ名、または Easy Connect 記法

-- tnsnames.ora に REMOTE_DB というエントリがある場合
CREATE DATABASE LINK remote_link
CONNECT TO app_user IDENTIFIED BY secret123
USING 'REMOTE_DB';

-- 作成後の使い方
SELECT * FROM employees@remote_link;

-- Easy Connect: //host:port/service_name
CREATE DATABASE LINK remote_link
CONNECT TO app_user IDENTIFIED BY secret123
USING '//192.168.1.100:1521/ORCL';

-- ホスト名でも可
CREATE DATABASE LINK remote_link
CONNECT TO app_user IDENTIFIED BY secret123
USING '//db-server.example.com:1521/PRODDB';

-- PUBLIC リンク: 全ユーザーが使用可能
CREATE PUBLIC DATABASE LINK shared_remote
CONNECT TO app_readonly IDENTIFIED BY readonly_pw
USING '//192.168.1.100:1521/PRODDB';

-- 任意のユーザーが使用可能
SELECT * FROM orders@shared_remote;

-- CONNECT TO CURRENT_USER: リモート側にも同名ユーザーが必要
CREATE DATABASE LINK remote_currentuser
CONNECT TO CURRENT_USER
USING '//192.168.1.100:1521/ORCL';

-- HR ユーザーが実行すると、リモート側の HR ユーザーの権限で接続
-- SCOTT ユーザーが実行すると、リモート側の SCOTT ユーザーの権限で接続

-- SELECT: リモートテーブルを参照
SELECT * FROM employees@remote_link;

-- SELECT: ローカルとリモートを結合
SELECT l.order_id, r.customer_name
FROM local_orders l
JOIN customers@remote_link r ON l.customer_id = r.customer_id;

-- INSERT: リモートテーブルにデータを挿入
INSERT INTO audit_log@remote_link (action, action_date)
VALUES ('SYNC', SYSDATE);

-- UPDATE: リモートテーブルを更新
UPDATE products@remote_link
SET price = price * 1.1
WHERE category = 'FOOD';

-- DELETE: リモートテーブルから削除
DELETE FROM temp_data@remote_link WHERE created_at < SYSDATE - 30;

COMMIT;  -- リモートへの DML も COMMIT が必要

-- リモートのテーブル構造+データをローカルにコピー
CREATE TABLE local_employees AS
SELECT * FROM employees@remote_link;

-- 条件付きコピー
CREATE TABLE local_recent_orders AS
SELECT * FROM orders@remote_link
WHERE order_date >= DATE '2025-01-01';

-- リモートテーブルへのシノニムを作成
CREATE SYNONYM remote_employees FOR employees@remote_link;

-- @remote_link なしで使える
SELECT * FROM remote_employees;

-- PUBLIC シノニムにすれば全ユーザーが使える
CREATE PUBLIC SYNONYM remote_employees FOR employees@remote_link;

-- デフォルト: ローカル側で結合（リモートから全データを転送してくる）
SELECT l.order_id, r.customer_name
FROM local_orders l
JOIN customers@remote_link r ON l.customer_id = r.customer_id;

-- DRIVING_SITE でリモート側で結合を実行させる
SELECT /*+ DRIVING_SITE(r) */
    l.order_id, r.customer_name
FROM local_orders l
JOIN customers@remote_link r ON l.customer_id = r.customer_id;
-- ローカルの local_orders をリモート側に送り、リモートで結合
-- customers が大テーブルの場合に効果的

-- 自分が作成した PRIVATE リンク
SELECT db_link, username, host, created FROM user_db_links;

-- 全スキーマの PRIVATE + PUBLIC リンク（DBA 権限が必要）
SELECT owner, db_link, username, host FROM dba_db_links ORDER BY owner;

-- 接続テスト
SELECT * FROM dual@remote_link;
-- ORA エラーなく '1 row selected' が返れば接続成功

-- PRIVATE リンクを削除
DROP DATABASE LINK remote_link;

-- PUBLIC リンクを削除（DBA 権限が必要）
DROP PUBLIC DATABASE LINK shared_remote;

-- DB Link 経由で開いたリモートセッションを手動で閉じる
-- （通常はセッション終了時に自動クローズされる）
ALTER SESSION CLOSE DATABASE LINK remote_link;

-- 現在開いている DB Link セッションを確認
SELECT db_link, in_transaction, update_sent
FROM v$dblink;

-- (1) tnsping でネットワーク疎通を確認
-- $ tnsping REMOTE_DB

-- (2) SQL*Plus でリモート DB に直接ログイン
-- $ sqlplus app_user/secret123@REMOTE_DB

-- (3) DB Link 経由で dual を SELECT（最小限の接続テスト）
SELECT * FROM dual@remote_link;

-- (4) リモート側でテーブルと権限を確認
-- リモートに接続して:
-- SELECT * FROM all_tables WHERE table_name = 'EMPLOYEES';
-- SELECT * FROM user_tab_privs WHERE table_name = 'EMPLOYEES';