【Oracle】データベースリンク（DATABASE LINK）完全ガイド｜CREATE DATABASE LINK・リモート DB 参照・分散クエリ・管理方法まで解説

-- プライベートリンク（作成したユーザーのみが使える）
-- FIXED USER（固定ユーザー）: リンクを使うすべてのユーザーが指定したユーザーとして接続する
CREATE DATABASE LINK prod_link
CONNECT TO hr IDENTIFIED BY hr_password
USING 'prod_db';           -- tnsnames.ora のエントリ名 または Easy Connect 文字列

-- Easy Connect 形式で直接指定する（tnsnames.ora なしで使える）
CREATE DATABASE LINK prod_link_ezc
CONNECT TO hr IDENTIFIED BY hr_password
USING '//prod-server.example.com:1521/orcl';

-- CONNECTED USER（接続ユーザー認証）: 現在のセッションのユーザー名で接続する
-- ※ 接続先 DB に同名ユーザーが存在する必要がある
CREATE DATABASE LINK prod_link_cu
USING 'prod_db';           -- CONNECT TO 句なし → CONNECTED USER 認証

-- パブリックリンク（すべてのユーザーが使える・DBA 権限が必要）
CREATE PUBLIC DATABASE LINK pub_prod_link
CONNECT TO app_readonly IDENTIFIED BY app_password
USING '//prod-server.example.com:1521/orcl';

-- 必要な権限
-- プライベートリンク: CREATE DATABASE LINK システム権限
-- パブリックリンク: CREATE PUBLIC DATABASE LINK システム権限（DBA が付与）

-- リモートテーブルの参照（SELECT）
SELECT employee_id, last_name, salary
FROM employees@prod_link
WHERE department_id = 60;

-- リモートビューの参照
SELECT * FROM emp_summary_v@prod_link;

-- ローカルテーブルとリモートテーブルの結合（分散クエリ）
SELECT l.order_id, l.customer_id, r.product_name
FROM orders l
JOIN products@prod_link r ON l.product_id = r.product_id;

-- リモートテーブルへの INSERT・UPDATE・DELETE（分散トランザクション）
INSERT INTO orders@prod_link (order_id, customer_id, order_date)
VALUES (order_id_seq.NEXTVAL, 100, SYSDATE);

UPDATE employees@prod_link SET salary = salary * 1.1 WHERE department_id = 60;

DELETE FROM temp_log@prod_link WHERE created_at < SYSDATE - 30;

-- 分散トランザクションは 2フェーズコミット（2PC）が必要
-- ローカルとリモートの両方が COMMIT されるか、両方が ROLLBACK される
COMMIT;   -- 両方のサイトで一貫してコミットされる

-- リモートプロシージャの実行
EXEC update_orders@prod_link(order_id => 1001, status => 'SHIPPED');

-- リモートシーケンスの参照（12c 以降）
SELECT order_id_seq@prod_link.NEXTVAL FROM DUAL;

-- @prod_link を毎回書く代わりにシノニムを作成する
CREATE SYNONYM prod_employees FOR employees@prod_link;
CREATE SYNONYM prod_orders FOR orders@prod_link;

-- シノニム経由でアクセス（@prod_link が不要になる）
SELECT * FROM prod_employees WHERE department_id = 60;

-- PUBLIC シノニム（全ユーザーが使える）
CREATE PUBLIC SYNONYM prod_products FOR products@prod_link;

-- アプリケーション側からはリンクの存在を隠蔽できる
-- → 将来ローカルにテーブルが移動してもシノニムを変更するだけでよい

-- IN-DOUBT トランザクションを確認する
SELECT local_tran_id, global_tran_id, state, mixed, host, commit#
FROM DBA_2PC_PENDING;
-- STATE: prepared（コミット待ち）/ forced commit / forced rollback

-- IN-DOUBT トランザクションを手動で強制コミット / ロールバックする（DBA 権限が必要）
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id_here');
-- または
COMMIT FORCE 'local_tran_id_here';
ROLLBACK FORCE 'local_tran_id_here';
-- ※ IN-DOUBT トランザクションの解決は相手 DB の状態を確認してから行う

-- 自スキーマのリンクを確認する
SELECT db_link, username, host
FROM USER_DB_LINKS
ORDER BY db_link;
-- db_link: リンク名（大文字）
-- username: 接続ユーザー名（FIXED USER の場合）
-- host: tnsnames.ora の接続文字列または Easy Connect 文字列

-- すべてのリンク（DBA 権限が必要）
SELECT owner, db_link, username, host
FROM ALL_DB_LINKS
ORDER BY owner, db_link;

-- パブリックリンクを含むすべてのリンク
SELECT owner, db_link, username, host
FROM DBA_DB_LINKS
WHERE owner IN ('PUBLIC', 'HR', 'APP_USER')
ORDER BY owner, db_link;

-- 接続テスト（リンク先の DUAL テーブルを参照する）
SELECT 1 FROM DUAL@prod_link;

-- DATABASE LINK を削除する
DROP DATABASE LINK prod_link;
DROP PUBLIC DATABASE LINK pub_prod_link;   -- PUBLIC リンクは DBA 権限が必要

-- 現在のセッションでオープンされているリンクを確認する（V$DBLINK）
SELECT db_link, logged_on, open_cursors, in_transaction
FROM V$DBLINK;

-- GLOBAL_NAMES パラメータを確認する
SHOW PARAMETER global_names;
-- TRUE（デフォルト）: リンク名がリンク先の GLOBAL_NAME と一致する必要がある
-- FALSE: リンク名とリンク先の GLOBAL_NAME が一致しなくてもよい

-- リンク先 DB の GLOBAL_NAME を確認する（リンク先で実行）
SELECT * FROM GLOBAL_NAME;  -- orcl.example.com などのドメイン付き名前

-- 対処法①: リンク名を GLOBAL_NAME に合わせる
CREATE DATABASE LINK orcl.example.com   -- リンク名をリンク先の GLOBAL_NAME に合わせる
CONNECT TO hr IDENTIFIED BY hr_password
USING 'prod_db';

-- 対処法②: GLOBAL_NAMES を FALSE に設定する（開発環境では楽だが本番は要注意）
ALTER SYSTEM SET GLOBAL_NAMES = FALSE;
ALTER SESSION SET GLOBAL_NAMES = FALSE;  -- セッションレベルでも設定できる