實現 INSERT INTO 本地表 SELECT * FROM 遠端表@dblink 跨庫資料同步。
若僅在客戶端配置,資料庫執行 DB Link 時會報 ORA-12154: TNS:could not resolve the connect identifier specified。
# $ORACLE_HOME/network/admin/tnsnames.ora
REMOTEDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = remote_host)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = remote_svc))
)
CURDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = local_host)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = local_svc))
)
CREATE PUBLIC DATABASE LINK remotedb
CONNECT TO remote_user IDENTIFIED BY remote_pwd
USING 'REMOTEDB';
CREATE PUBLIC DATABASE LINK curdb
CONNECT TO local_user IDENTIFIED BY local_pwd
USING 'CURDB';
INSERT INTO local_tbl@curdb (col1, col2)
SELECT col1, col2 FROM remote_tbl@remotedb WHERE rownum <= 3;
| 錯誤 | 原因 | 解決 |
|---|---|---|
| ORA-12154 | 服務端 tnsnames.ora 缺少別名 | 在 $ORACLE_HOME/network/admin 新增條目並重載 listener |
| ORA-02019 | 遠端用戶權限不足 | GRANT CREATE SESSION, SELECT ON remote_tbl TO remote_user |
| ORA-02070 | 遠端資料庫不支援分散式交易 | 檢查遠端 init.ora: distributed_transactions > 0 |
| 效能差 | 大量資料跨網路拉取 | 使用並行 DML、分批提交、或改用 GoldenGate/物化視圖同步 |
-- 私有 (僅建立者可用)
CREATE DATABASE LINK priv_link CONNECT TO u IDENTIFIED BY p USING 'tns';
-- 當前用戶 (需 SSL/Enterprise User Security)
CREATE DATABASE LINK cdb_link CONNECT TO CURRENT_USER USING 'tns';