Oracle创建Database Link访问远程数据库

工作中有需求用到两台不同服务器下的数据库,从一台服务器读取另一台服务器数据库上的数据。需要用到Oracle数据库的Database link

CREATE DATABASE LINK "DBL_REMOTEDB"  
CONNECT TO "REMOTE_USERNAME" IDENTIFIED BY "REMOTE_PASSWORD"  
USING  
'(DESCRIPTION =  
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = REMOTE_IP)(PORT = 1521))
      )
      (CONNECT_DATA =
        (SERVICE_NAME = REMOTE_SERVICE_NAME)
      )
    )
';  

查看远程数据库表或视图

--查询远程表
SELECT * FROM REMOTE_TABLE@DBL_REMOTEDB;

--查询远程视图
SELECT * FROM REMOTE_VIEW@DBL_REMOTEDB;  

创建本地视图连接远程数据库表

CREATE VIEW LOCAL_VIEW AS SELECT * FROM REMOTE_TABLE@DBL_REMOTEDB;  

查询本地视图

SELECT * FROM LOCAL_VIEW;  

这样就能通过查询本地的LOCAL_VIEW直接查询到远程数据库的表数据。

comments powered by Disqus