oracle 异库查询的方法

oracle 具有异库查询的功能. 可以通过建立dblink,从而可以在不同的数据库之间执行sql脚本

 

方法如下:

 1, 建立dblink

DROP PUBLIC DATABASE LINK "NET3_TO_ECP.US.ORACLE.COM";

CREATE PUBLIC DATABASE LINK "NET3_TO_ECP.US.ORACLE.COM"
 CONNECT TO NET3_TEST
 IDENTIFIED BY <PWD>//这里替换成要连接的数据库密码
 USING '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 131.1.2.82)(PORT = 1521))
    )
    (CONNECT_DATA =
(SERVICE_NAME = HOTYEDB)
    )
  )';

 2. 由此可以通过dblink的方式查询异库的数据

select * from point@NET3_TO_ECP.US.ORACLE.COM

 

通过dblink查找异库数据并循环插入到本地的pl/sql语句

DECLARE
BEGIN
   FOR aa IN (SELECT hu.user_name, pj.institute_trace
                FROM point_journal@net3_to_ecp.us.oracle.com pj, hotye_user@net3_to_ecp.us.oracle.com hu, user_point@net3_to_ecp.us.oracle.com UP
               WHERE hu.user_id = UP.user_id
                 AND UP.ID = pj.user_point_id
                 AND pj.institute_trace IS NOT NULL
                 AND pj.trans_time BETWEEN TO_DATE ('2008-10-01',
                                                    'yyyy-mm-dd')
                                       AND TO_DATE ('2008-10-12',
                                                    'yyyy-mm-dd'))
   LOOP
      UPDATE t_transline
         SET useraccount = aa.user_name
       WHERE systemtrace = aa.institute_trace;
   END LOOP;

   COMMIT;
END;
/