oracle database link

作用:将多个oracle数据库逻辑上看成一个数据库,也就是说在一个数据库中可以操作另一个数据库中的对象.

简易语法:

CREATE [PUBLIC] DATABASE LINK dblinkname CONNECT TO user IDENTIFIED BY password USING ‘connect_string’;

DROP [PUBLIC] DATABASE LINK dblink;

注意:你必须有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的权限,另外,在你要连接的数据库上,你必须有CREATE SESSION的权限.

语法解释:
    dblinkname:给dblink起的名字,以后在sql语句中使用的连接名.
    user和password:要连接的数据库的合法用户名和密码
    connect_string:可以是经过Net Manager配置且经测试联通的服务命名,例如:orcl2,不过容易出问题,老提示出错:无法解析字符串.最好写成tnsnames.ora中的形式 (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) )
1.创建

create public database link localto2 connect to ad1234 identified by ad4321 using 'orcl2';

注意:10g通过dblink访问11g时，密码自动大写问题，把密码用双引号包起来

CREATE PUBLIC DATABASE LINK localto2
CONNECT TO ad1234
IDENTIFIED BY "ad4321"
USING '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.2) (PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))';

2.删除

DROP PUBLIC DATABASE LINK localto2;

3.查询

select owner,object_name from dba_objects where object_type='DATABASE LINK';

4.使用
在配置dblink的数据库上查询192.168.1.2的数据库

select * from table1@localto2;

注意:使用同义词

create public synonym t1 for table1@localto2;
select * from t1;

5.使用dblink导数
单表

insert into table1 select * from table1@localto2

schemas,使用impdb高级特性(将192.168.1.2上面名为'adadmin'的schemas导到orcl的名为'bdadmin'的schemas)

impdb ad1234/ad4321@ORCL network_link=localto2 schemas=adadmin remap_schema=adadmin:bdadmin