欢迎光临
一个有态度、有温度的分享型博客

Oracle创建DATABASE LINK使两个数据库之间可以互相访问

有两个远程数据库,分别为HDPI_DEV和dtcenter,这两个数据库的连接的tnsnames配置,分别如下:

HDPI_DEV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.158.176.51)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )


DTCENTER =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp)(HOST = 10.158.188.106)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dtcenter)
    )
  )

默认情况下这两个数据库之间的表是不能互相调用的,也就是我在我自己机器的PL/SQL远程连接到其中一个数据库HDPI_DEV之后,默认是不能对数据库DTCENTER中的表进行查询操作的,现在我通过PL/SQL远程连接数据库HDPI_DEV之后,在数据库HDPI_DEV上面创建一个DATABASE LINK,实现对数据库DTCENTER中的表的访问。创建过程如下:

CREATE DATABASE LINK connect_to_dtcenter 
   CONNECT TO comm IDENTIFIED BY password
   USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp)(HOST = 10.158.188.106)(PORT = 1521))
      (LOAD_BALANCE = yes)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dtcenter)
    )
  )';

其中,connect_to_dtcenter为DATABASE LINK名称,comm为数据库DTCENTER的用户,password为用户comm对应的密码。

创建DATABASE LINK的前提是登录的用户具有创建DATABASE LINK的权限,而且创建的DATABASE LINK是单向的,在这里数据库HDPI_DEV可以访问数据库DTCENTER中的表,反过来不行。

使用示例:

SELECT SUM(A.POWER_GEN) FDL_DR, B.ENTITY_ID
          FROM datacenter.GRIDPOWER_FCT@connect_to_dtcenter A , datacenter.HDPI_SET@connect_to_dtcenter B
         WHERE A.SET_ID = B.SET_ID
           AND A.CALENDAR_ID = '20101227'
         GROUP BY B.ENTITY_ID

参考文章:

  1. https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm;
  2. http://www.cnblogs.com/zmlctt/p/3749029.html;
  3. http://www.cnblogs.com/sumsen/archive/2013/03/04/2943471.html;

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址