ORACLE DBLINK

Oracle database link概述
oracle 使用database link(数据库连接)来创建到其他数据库中对象的访问路径。一旦创建了数据库连接，就可以像在本地Oracle数据库一样，对远程(或本地)数据库发出DML(Data Manipulation Language)命令，但大部分数据库连接都不允许DDL(Data Definition Language)操作。数据库连接分为私有数据库连接和公有数据库连接两种。

创建dblink
1.使用已经定义的网络服务名

网络服务名写在tnsnames.ora文件中
create public database link to234db
connect to userName identified by password
using ‘orcl’
其中：
public 表示创建公有的dblink；to234db为dblink的名字；userName和password分别为要连接的目标数据 库用户名和密码；orcl为tnsnames.ora文件中定义的一个网络服务名,如：
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.8.234)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = WF4PPDB.ACON.COM)
)
)

2、指定网络服务描述
create database link to234db
connect to userName identified by password
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.8.234)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)';
其中：
host为数据库所在ip地址，service_name是数据库sid。

使用database link
select * from tableName@to234db;
insert into tableName@to234db values(…);
update tableName@to234db set…
delete tableName@to234db where …
其中tableName是目标数据库中已经存在的表

查看已经定义的database link
select * from dba_db_links;这种方式可以查看到database link定义信息
select * from dba_objects where object_type like 'DATABASE LINK';

删除database link
drop [public] database link to234db;其中public 表示删除公有的database link;
我也测试了下

SQL> create public database
  2  link gtoi connect to sys
  3  identified by woaini using 'win9i';

Database link created

SQL> select * from pp@gtoi;

select * from pp@gtoi

ORA-12154: TNS:could not resolve the connect identifier specified

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
PP

SQL> show parameter global_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string     
global_names                         boolean     FALSE

SQL> select db_link from dba_db_links;

DB_LINK
--------------------------------------------------------------------------------
GTOI

SQL> select * from pp@gtoi;

select * from pp@gtoi

ORA-12154: TNS:could not resolve the connect identifier specified

SQL> create database link test
  2  connect to system identified by woaini
  3  using '(description=
  4  (adderss_list=
  5  (adderss=(protocol=tcp)(host=23.36.2.159)(port=1521))
  6  )
  7  (connect_data=
  8  (service_name=pp))
  9  )';

Database link created

SQL> select * from pp@test;

select * from pp@test

ORA-12533: TNS:illegal ADDRESS parameters

SQL> drop dblink test;

drop dblink test

ORA-00950: invalid DROP option

SQL> drop database link test;

Database link dropped

SQL>
SQL> create database link test
  2  connect to system identified by woaini
  3  using '(description=
  4  (address_list=
  5  (address=(protocol=tcp)(host=23.36.2.159)(port=1521))
  6  )
  7  (connect_data=
  8   (service_name=pp))
  9  )';

Database link created

SQL>
SQL> select * from test@test;

                                     IT
---------------------------------------
                                      1
                                      1