ORACLE建立Database Link

本機資料庫資訊(schema: ABC, user: ABC, password: ABC)

0. 登入 oracle 帳號

1. 在 tnsnames.ora 檔案中, 加入要連線的遠端資料庫資料

REMOTE_DB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ora1)
    )
  )

2. 重新啟動資料庫(讓第1步驟設定生效)
** 實測結果有時好像不用重啟就可以生效

sqlplus '/as sysdba'
SQL> shutdown immediate
SQL> startup

3. 檢查user是否有建立 Database Link 的權限

SQL> select grantee, privilege from dba_sys_privs where grantee='ABC';

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
ABC                            CREATE TABLE
ABC                            CREATE SESSION
ABC                            CREATE SYNONYM
ABC                            UNLIMITED TABLESPACE

4. 新增user建立 Database Link 的權限

SQL> grant CREATE DATABASE LINK to ABC

5. 再檢查是否有權限

SQL> select grantee, privilege from dba_sys_privs where grantee='ABC';

GRANTEE                        PRIVILEGE
------------------------------ ----------------------------------------
ABC                            CREATE TABLE
ABC                            CREATE SESSION
ABC                            CREATE SYNONYM
ABC                            CREATE DATABASE LINK        <<<
ABC                            UNLIMITED TABLESPACE

6. 使用 ABC 帳號登入資料庫

SQL> connect ABC/ABC@ora1
SQL> create database link DB-LINK-Name connect to user_id identified by user_pw using 'REMOTE_DB';

6-1. 如果遠端DB是11g, 則密碼要用雙引號括住, 否則雖成功建立db-link, 下SQL時會出現login dennied的錯誤

SQL> create database link DB-LINK-Name connect to user_id identified by "user_pw" using 'REMOTE_DB';

說明:

create database link <link-name>  connect to <連線帳號> identified by <連線密碼> using '<link-name>';

ex:
  link-name => REMOTE_DB(必需為連線名稱及tnsnames.ora中定義的名稱, 兩者要相同), 實測後確認名稱可以自訂
  連線帳號 => user_id (遠端DB有權限讀取REMOTE_DB的帳號)
  連線密碼 => user_pw
  tnsname => 第1個步驟設定的名稱 REMOTE_DB, 記得此處要加單引號, 連線遠端資料庫的tnsname

7. 檢查是否已建立成功

在 ABC 環境下

select db_link, username, host from user_db_links;

在 sysdba 環境下

select db_link, username, host from dba_db_links;

8. 移除 user create database link 權限 (在sysdba環境下)

SQL> revoke CREATE DATABSE LINK from ABC

9. 查詢SQL確認可以正常連線並取得資料 (在 ABC 環境下)

SQL> SELECT * FROM users@REMOTE_DB where userid='admin';

說明: 在 table後加上@ + 第1步驟建立的tnsname



** 刪除 DATABASE LINK

SQL> DROP DATABASE LINK <database-link-name>