oracle 11.2.0.4之oracle database db link之测试明细之一

测试结论
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