oracle创建dblink
原创
©著作权归作者所有:来自51CTO博客作者wx62e28ac222a20的原创作品,请联系作者获取转载授权,否则将追究法律责任
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下创建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=数据库实例
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过来的数据
sys@CXMTDB 12:00:05> select count(*) from hbhe.t1@NODE02DBLINK;
COUNT(*)
----------
1200
7.删除dblink
drop public database link NODE02DBLINK