create database link远程数据库相互调用查询

 

 1）DROP DATABASE LINK "otcuat" ;

 

 2） create database link "otcuat" connect to adsr identified by "adsr" using '(DESCRIPTION = (ADDRESS_LIST = 

      (ADDRESS =  

        (PROTOCOL = TCP)

        (HOST = 192.168.104.10)

        (PORT = 1521)

      )

    )

    (CONNECT_DATA = 

      (SERVICE_NAME = otcuat)

    )

  )';

  

在当前db connect otcsit 环境查询otcuat的数据用于将sit环境的数据改为同uat一致。

SELECT * FROM SDS_INSTRUMENT@otcuat;

 

用update select 语句同时更新多列       

update SDS_INSTRUMENT i set (i.underlying_stock , i.ko_price, i.eng_name) = (SELECT inst.underlying_stock , inst.ko_price, inst.eng_name FROM SDS_INSTRUMENT@otcuat inst WHERE inst.instrument_id = i.instrument_id and inst.type = i.type)

WHERE i.instrument_id  IN ('AC1507',

'AC1664',

'AC1451',

'DC2006',

'DC2043',

'AC2481',

'AC2502',

'AC2457',

'DC1251',

'DC2267',

'DC2335',

'DC2336',

'AC2645');