1.环境说明
192.168.56.150 hostname为node01
192.168.56.151 hostname为node02http://www.cndba.cn/hbhe0316/article/4761http://www.cndba.cn/hbhe0316/article/4761

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

2.给hbhe用户授权http://www.cndba.cn/hbhe0316/article/4761

[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.查看是否授权成功http://www.cndba.cn/hbhe0316/article/4761

[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下创建dblink

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=数据库实例http://www.cndba.cn/hbhe0316/article/4761http://www.cndba.cn/hbhe0316/article/4761

5.查看是否创建成功http://www.cndba.cn/hbhe0316/article/4761

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