oralce DBLINK管理

1建立net 服务名

linux下 进入

cd $ORACLE_HOME/network/admin

vi tnsnames.ora

添加

CCPBS_19 =

  (DESCRIPTION =

    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.130.38.19)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = CCPBS)
    )
  )

 保存 退出 然后测试下

>tnsping ccpbs_19

TNS Ping Utility for Linux: Version 9.2.0.7.0 - Production on 02-JUL-2009 15:16:52

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.130.38.19)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = CCPBS)))

OK (20 msec)

测试成功

Windows下的参见

2、创建数据库链接，

/home/oracle/>sqlplus /nolog

SQL*Plus: Release 9.2.0.7.0 - Production on Thu Jul 2 16:01:00 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn /as sysdba

Connected.

进入系统管理员SQL>操作符下，运行命令：

密码用引号“”，不然11g版本不兼容。

>create public database link ecbm_19 connect to ecbm_userName identified by ecbm_pwd using 'CCPBS_19';

Database link created.

格式:

create public database link link_name connect to user identified by password using 'SID';

则创建了一个连接到10.130.38.19 ccpbs上以ecbm_userName 用户的连接

查询数据时带上@ecbm_19 就ok了 如select * from emp@ecbm_19;

删除 DROP PUBLIC DATABASE LINK ecbm_19;

3、建立同义词，为了使有关分布式操作更透明，ORACLE数据库里有同义词的对象synonym

SQL>create synonym bjscottemp for emp@beijing;

于是就可以用bjscottemp来替代带@符号的分布式链接操作emp@beijing。

create public synonym poh for po_headers_all@erp_danzheng_test;

create public synonym papf for per_all_people@erp_danzheng_test;

create public synonym pv for po_vendors@erp_danzheng_test;

4、查看所有的数据库链接，进入系统管理员SQL>操作符下，运行命令：

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

 参考 http://moonsoft.itpub.net/post/15182/125559