測試結論
1,建立資料庫db link有2種方式，一則為透過配置tnsnames.ora訪問遠端資料庫
二則為不配置tnsnames.ora訪問遠端資料庫

2，透過配置tnsnames.ora訪問遠端資料庫的建立DB LINK的語法如下

create database link target_user_zxy connect to user_zxy identified by system using 'tns_esbdb';

（注：tns_esbdb為netmgr建立的net service name）

3，為不配置tnsnames.ora訪問遠端資料庫建立DB LINK的語法如下
SQL> create database link target_user_zxy connect to user_zxy identified by system
2 using '(DESCRIPTION =
3 (ADDRESS_LIST =
4 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521))
5 )
6 (CONNECT_DATA =
7 (SERVICE_NAME = esbdb)
8 )';

Database link created.

（注：一定要注意格式，否則即使建立成功，DB LINK也使用不了）

4，db link有2種型別，一則為private,二則為public

5,private db link只能建立資料庫使用者使用這個db link

6,public db link可以所有資料庫使用者使用這個db link

7,刪除db link的語法
drop database link target_user_zxy;

8,刪除public db link語法

drop public database link target_user_zxy;

9,private db link即使透過授權其它資料庫使用者強大的權力或者透過同義詞，其它的資料庫使用者仍舊不能使用訪問private db link

10,透過tnsnames.ora配置建立db link,如下字典對應的host為net service name
SQL> select owner,db_link,username,host,created from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ ------------------------------ ---------------------------------------- -------------------------------------------------- ---------
SYSTEM TARGET_USER_ZXY USER_ZXY tns_esbdb 12-JUN-17

11，不透過tnsnames.ora配置建立db link,如下字典對應的host為如下

SQL> set linesize 300
SQL> col username for a40
SQL> col db_link for a30
sql> col host for a50
SQL> select owner,db_link,username,host,created from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ ------------------------------ ---------------------------------------- -------------------------------------------------- -------------------
SYSTEM TARGET_USER_ZXY USER_ZXY (DESCRIPTION = 2017-06-12 18:16:10
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.3
9)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = esbdb)
)

12，不通透過db link對遠端資料庫進行DDL操作，否則報錯
SQL> grant select on syn_t_test to user_zxy;
grant select on syn_t_test to user_zxy
*
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

[oracle@mygirl admin]$ oerr ora 2021
02021, 00000, "DDL operations are not allowed on a remote database"
// *Cause: An attempt was made to use a DDL operation on a remote database.
// For example, "CREATE TABLE tablename@remotedbname ...".
// *Action: To alter the remote database structure, you must connect to the
// remote database with the appropriate privileges.

測試明細
1，db link使用方之資料庫版本
SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2，db link使用方之資料庫例項名稱以及IP地址
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string mygirl

[root@mygirl ~]# ifconfig eth0
eth0 Link encap:Ethernet HWaddr 08:00:27:E6:9A:3B
inet addr:10.0.0.5 Bcast:10.255.255.255 Mask:255.0.0.0
inet6 addr: fe80::a00:27ff:fee6:9a3b/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:532 errors:0 dropped:0 overruns:0 frame:0
TX packets:287 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:48493 (47.3 KiB) TX bytes:38727 (37.8 KiB)

3,db link提供方之資料庫版本以及資料庫例項名稱以及IP地址和資料庫使用者名稱稱

SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string esbdb

SQL> select username from dba_users where username='USER_ZXY';

USERNAME
------------------------------
USER_ZXY

SQL> select tname from tab;

no rows selected

SQL> create table t_test(a int);

Table created.

SQL> insert into t_test values(1);

1 row created.

SQL> commit;

Commit complete.

suse11:~ # ifconfig eth0
eth0 Link encap:Ethernet HWaddr 08:00:27:81:B6:5A
inet addr:10.0.0.39 Bcast:10.0.0.255 Mask:255.255.255.0
inet6 addr: fe80::a00:27ff:fe81:b65a/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:774 errors:0 dropped:0 overruns:0 frame:0
TX packets:449 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:69213 (67.5 Kb) TX bytes:59159 (57.7 Kb)

4,db link提供方之資料庫監聽執行狀態
oracle@suse11:~> lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 12-JUN-2017 18:01:52

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 12-JUN-2017 18:01:40
Uptime 0 days 0 hr. 0 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File /oracle/diag/tnslsnr/suse11/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=suse11)(PORT=1521)))
Services Summary...
Service "esbdb" has 1 instance(s).
Instance "esbdb", status READY, has 1 handler(s) for this service...
Service "esbdbXDB" has 1 instance(s).
Instance "esbdb", status READY, has 1 handler(s) for this service...
The command completed successfully

5，在db link使用方直接透過不配置資料庫TNSNAMES.ORA訪問遠端資料庫

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

SQL> select owner,db_link,username,host,created from dba_db_links;

no rows selected

SQL> show user
USER is "SYSTEM"

SQL> create database link target_user_zxy connect to user_zxy identified by system
2 using '(DESCRIPTION =
3 (ADDRESS_LIST =
4 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521))
5 )
6 (CONNECT_DATA =
7 (SERVICE_NAME = esbdb)
8 )';

Database link created.

SQL> conn /as sysdba
Connected.

SQL> set linesize 300
SQL> col username for a40
SQL> col db_link for a30
sql> col host for a50
SQL> select owner,db_link,username,host,created from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ ------------------------------ ---------------------------------------- -------------------------------------------------- -------------------
SYSTEM TARGET_USER_ZXY USER_ZXY (DESCRIPTION = 2017-06-12 18:16:10
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.3
9)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = esbdb)
)

SQL>

6,在DB LINK使用方驗證DB LINK是否正常
SQL> select count(*) from t_test@target_user_zxy;
select count(*) from t_test@target_user_zxy
*
ERROR at line 1:
ORA-02019: connection description for remote database not found

SQL> select count(*) from user_zxy.t_test@target_user_zxy;
select count(*) from user_zxy.t_test@target_user_zxy
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified

為了診斷上述的錯誤，嘗試採用反推方式即透過圖形化netgmr
[oracle@mygirl admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

TNS_ESBDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = esbdb)
)
)

[oracle@mygirl admin]$ sqlplus user_zxy/system@tns_esbdb

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 12 18:31:02 2017

Copyright (c) 1982, 2013, Oracle. All rights reserved.

SQL> drop database link target_user_zxy;

Database link dropped.

SQL> create database link target_user_zxy connect to user_zxy identified by system
2 using '(DESCRIPTION =
3 (ADDRESS_LIST =
4 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.39)(PORT = 1521))
5 )
6 (CONNECT_DATA =
7 (SERVICE_NAME = esbdb)
8 )
9 )';

Database link created.

SQL> select * from t_test@target_user_zxy;

A
----------
1

7,在db link使用方直接透過配置資料庫TNSNAMES.ORA訪問遠端資料庫

SQL> show user
USER is "SYSTEM"

SQL> drop database link target_user_zxy;

Database link dropped.

SQL> create database link target_user_zxy connect to user_zxy identified by system using 'tns_esbdb';

Database link created.

SQL> select * from t_test@target_user_zxy;

A
----------
1

SQL> set linesize 300
SQL> col username for a40
SQL> col db_link for a30
SQL> col host for a50
SQL> select owner,db_link,username,host,created from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
------------------------------ ------------------------------ ---------------------------------------- -------------------------------------------------- ---------
SYSTEM TARGET_USER_ZXY USER_ZXY tns_esbdb 12-JUN-17

SQL>

8,可見在某個資料庫使用者建立的db link，其它資料庫使用者則不能使用它
SQL> conn /as sysdba
Connected.
SQL> select * from t_test@target_user_zxy;
select * from t_test@target_user_zxy
*
ERROR at line 1:
ORA-02019: connection description for remote database not found

SQL> conn user_zxy/system
Connected.
SQL> select * from t_test@target_user_zxy;
select * from t_test@target_user_zxy
*
ERROR at line 1:
ORA-02019: connection description for remote database not found

SQL> select username from dba_users where account_status='OPEN';

USERNAME
----------------------------------------
SYSTEM
SYS
USER_MOTHER
USER_ZXY
USER_FATHER
USER_OBJ

6 rows selected.

9,可見建立public database db link,建立db link的資料庫使用者與非資料庫使用者皆可訪問使用db link

SQL> conn system/system
Connected.
SQL> drop database link target_user_zxy;

Database link dropped.

SQL> create public database link target_user_zxy connect to user_zxy identified by system using 'tns_esbdb';

Database link created.

SQL> show user
USER is "SYSTEM"
SQL> select * from t_test@target_user_zxy;

A
----------
1

SQL>
SQL>
SQL> conn /as sysdba
Connected.
SQL> select * from t_test@target_user_zxy;

A
----------
1

SQL>
SQL> conn user_zxy/system
Connected.
SQL> select * from t_test@target_user_zxy;

A
----------
1

10,可見非public database db link只能建立自己的資料庫使用者訪問，即使授權了其它資料庫使用者更強大的權利，還是不成功；
透過同義詞同上，亦不成功
SQL> conn system/system
Connected.
SQL> drop database link target_user_zxy;
drop database link target_user_zxy
*
ERROR at line 1:
ORA-02024: database link not found

SQL> drop public database link target_user_zxy;

Database link dropped.

SQL> create database link target_user_zxy connect to user_zxy identified by system using 'tns_esbdb';

Database link created.

SQL> select * from t_test@target_user_zxy;

A
----------
1

SQL> conn user_zxy/system
Connected.
SQL> select * from t_test@target_user_zxy;
select * from t_test@target_user_zxy
*
ERROR at line 1:
ORA-02019: connection description for remote database not found

SQL> conn /as sysdba
Connected.

SQL> select distinct privilege from dba_sys_privs where lower(privilege) like '%link%';

PRIVILEGE
----------------------------------------
DROP PUBLIC DATABASE LINK
CREATE DATABASE LINK
CREATE PUBLIC DATABASE LINK

SQL> select distinct privilege from dba_tab_privs where lower(privilege) like '%link%';

no rows selected

SQL> conn system/system
Connected.

SQL> create synonym syn_t_test for t_test@target_user_zxy;

Synonym created.

SQL> select * from syn_t_test;

A
----------
1

SQL> grant select on syn_t_test to user_zxy;
grant select on syn_t_test to user_zxy
*
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

[oracle@mygirl admin]$ oerr ora 2021
02021, 00000, "DDL operations are not allowed on a remote database"
// *Cause: An attempt was made to use a DDL operation on a remote database.
// For example, "CREATE TABLE tablename@remotedbname ...".
// *Action: To alter the remote database structure, you must connect to the
// remote database with the appropriate privileges.

SQL> select distinct privilege from dba_sys_privs where lower(privilege) like '%syno%';

PRIVILEGE
----------------------------------------
CREATE SYNONYM
DROP ANY SYNONYM
CREATE ANY SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM

SQL> select distinct privilege from dba_tab_privs where lower(privilege) like '%syno%';

no rows selected

SQL> conn /as sysdba
Connected.

SQL> grant select any table to user_zxy;

Grant succeeded.

SQL> conn user_zxy/system
Connected.

SQL> select * from system.syn_t_test;
select * from system.syn_t_test
*
ERROR at line 1:
ORA-02019: connection description for remote database not found

SQL> conn /as sysdba
Connected.
SQL> grant dba to user_zxy;

Grant succeeded.

SQL> conn user_zxy/system
Connected.
SQL> select * from system.syn_t_test;
select * from system.syn_t_test
*
ERROR at line 1:
ORA-02019: connection description for remote database not found

oracle 11.2.0.4之oracle database db link之測試明細之一

相關文章