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