DBLink

--创建从 bps 到 css 的DBLink
--需要根据现网替换主机、端口、SID 以及 css用户名/密码

-- Create database link

create database link MSP_CSS.ORACLE.COM
  connect to #css数据库用户名# identified by #css数据库密码#
  using '(DESCRIPTION =    (ADDRESS_LIST =      (ADDRESS = (PROTOCOL = TCP)(HOST = #主机IP#)(PORT = 1521))    )    (CONNECT_DATA =      (SERVICE_NAME = #SID名称#)    )  )';

 

 

--用法

 

--添加业务信息的历史记录数据
Insert into ServInfo_hist(hisid,operatetype,operatetime,spid,servId,servName,servAttr,onlineTime,feeType,fee,freePro,status)
 select seq_ServInfo_HIST.Nextval,'A',a.LUPDDate,c.OSPCode,a.ServiceCode,f.CateName,'G',a.createdate,e.ChargeType,e.Fee,p.freePro,a.ProductStatus
  from OM_Product a,OM_Product_Channel b,OM_OSP c,OM_Product_Charge d,OM_Charge_Rule e,om_service_category f ,
   (select servid, substr(max(sys_connect_by_path(provinceid,';')),2) freepro
          from (select a.*,row_number()over(partition by servid order by provinceid) rn from t_prov_serv_map@MSP_CSS.ORACLE.COM a )
          group by servid
          start with rn=1
          connect by rn-1=prior rn and servid=prior servid) p
  where a.ProductID=b.ProductID and b.IsChargeChannel=1 and b.OSPID=c.OSPID and d.ProductID=a.ProductID and d.ChargeID=e.ChargeID and f.CategoryID=a.ServCateL2ID and p.servid = a.serviceCode;

 

t_prov_serv_map ： 为css 库中表名称