1.环境说明
192.168.56.150 hostname为node01
192.168.56.151 hostname为node02

需要从node01服务器dblink到node02服务器

2.给hbhe用户授权

[oracle@node01:/ogg/dirchk]$ sqlplus / as sysdba
sys@CXMTDB 11:28:23> grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to hbhe;

Grant succeeded.

3.查看是否授权成功

[oracle@node01:/ogg/dirchk]$ sqlplus hbhe/wwwwww
sys@CXMTDB 11:30:23> set linesize 100
sys@CXMTDB 11:30:30> set pagesize 100
sys@CXMTDB 11:31:19> column username for a20;
sys@CXMTDB 11:31:19> column PRIVILEGE for a30;
hbhe@CXMTDB 11:49:18> select * from user_sys_privs where privilege like upper('%DATABASE LINK%');

USERNAME               PRIVILEGE              ADM COM INH
------------------------------ ------------------------------ --- --- ---
HBHE                   CREATE PUBLIC DATABASE LINK    NO  NO  NO
HBHE                   DROP PUBLIC DATABASE LINK      NO  NO  NO

4.创建dblink
在node01下创建dblinkhttp://www.cndba.cn/hbhe0316/article/4761http://www.cndba.cn/hbhe0316/article/4761

create public database link NODE02DBLINK    
connect to hbhe identified by wwwwww  
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.56.151)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = cxmtdb)))';

NODE02DBLINK 为dblink名字，
connect to 需要连接的用户名 identified by 密码
HOST=需要连接数据库的ip
SERVICE_NAME=数据库实例

5.查看是否创建成功

sys@CXMTDB 11:55:16> select * from dba_db_links;
OWNER
--------------------------------------------------------------------------------------------------------------------------------
DB_LINK
--------------------------------------------------------------------------------------------------------------------------------
USERNAME
--------------------------------------------------------------------------------------------------------------------------------
HOST
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATED  HID SHA VAL INT
-------- --- --- --- ---
SYS
SYS_HUB

SEEDDATA
01:12:06 NO  NO  YES NO

PUBLIC
NODE02DBLINK
HBHE
(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.56.151)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = cxmtdb)))
11:53:27 NO  NO  YES NO


Elapsed: 00:00:00.00
sys@CXMTDB 11:55:17> select owner,object_name from dba_objects where object_type='DATABASE LINK';

OWNER
--------------------------------------------------------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS
SYS_HUB

PUBLIC
NODE02DBLINK

6.查看dbLink过来的数据http://www.cndba.cn/hbhe0316/article/4761

sys@CXMTDB 12:00:05> select count(*) from hbhe.t1@NODE02DBLINK;

  COUNT(*)
----------
      1200

7.删除dblink

drop  public database link  NODE02DBLINK

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle,linux