存储过程执行问题- ORA-02019:找不到远程数据库的连接描述

Question

我有3个Oracle11g数据库，比如说DB-1 (CPSBK)、DB-2 (ORCL_CC)、DB-3 (DRCPSMGT)。在CPSBKDB-1上，我使用CPSRPTL用户登录，下面给出的过程属于这个用户。

我们有一个存储过程(如下所示)，在执行/运行时，会出现以下错误。此查询中的Delete语句在单独运行时工作良好。SELECT (包括UNION )语句也是如此。

但是，当尝试插入时，它会抛出一个错误，如下所示。我被困在这个，没有任何帮助，我可以在网上找到。

运行过程时出错的详细信息：

Connecting to the database CPSBK-10.227.23.239-LIVE.
ORA-02019: connection description for remote database not found
ORA-02063: preceding line from CPSBK
ORA-02063: preceding 2 lines from ORCL_CC
ORA-06512: at "CPSRPTL.PRC_MFS_CC_LOAD_DATA", line 19
ORA-06512: at line 2
Process exited.
Disconnecting from the database CPSBK-10.227.23.239-LIVE.

PL/SQL过程在下面(Oracle)；这是在CPSBK (DB-1)上创建的。

这个数据库中也有两个公共数据库链接，ORCL_CC & DRCPSMGT。

CREATE OR REPLACE PROCEDURE PRC_MFS_CC_LOAD_DATA
AS
BEGIN
  DELETE
  FROM CPS.MFS_CPS_CC_TASKS_REPORT@ORCL_CC
  WHERE data_date = (TO_CHAR(SYSDATE-1,'yyyymmdd'));
  INSERT INTO CPS.MFS_CPS_CC_TASKS_REPORT@ORCL_CC
  SELECT TO_CHAR(SYSDATE-1,'yyyymmdd') DATA_DATE,
    SYSDATE DATA_INSERTION_TIME,
    ABC.Task_No,
    ABC.Order_ID,
    ABC.Task_Priority,
    ABC.Task_Name,
    ABC.Creation_Hour,
    ABC.Create_Time,
    ABC.Completed_Time,
    ABC.Time_Difference,
    ABC.Initiator_Login,
    ABC.Role_Assigned_Maker,
    ABC.Checker_Login,
    ABC.Role_Assigned_Checker,
    ABC.Task_State,
    ABC.Task_Type,
    ABC.Task_Description
  FROM
    (SELECT t.taskid Task_No,
      T.ORDERID Order_ID,
      DECODE (T.PRIORITY , 50 , 'Low', 100 ,'Medium', 200 , 'High', 'Other') Task_Priority,
      T.TASKNAME Task_Name,
      TO_CHAR(t.createtime, 'HH24') Creation_Hour,
      T.CREATETIME Create_Time,
      T.COMPLETEDTIME Completed_Time,
      (to_timestamp(T.COMPLETEDTIME, 'yyyy-mm-dd hh24:mi:ss') - to_timestamp(T.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') ) Time_Difference,
      op1.user_name Initiator_Login,
      RD1.NAME Role_Assigned_Maker,
      op2.user_name Checker_Login,
      RD2.NAME Role_Assigned_Checker,
      T.TASKSTATE Task_State,
      t.tasktype Task_Type,
      T.DESCRIPTION Task_Description
    FROM cpsmgt.cps_tasktodo@DRCPSMGT t
    LEFT JOIN CPSMGT.CPS_OPERATOR@DRCPSMGT op1
    ON OP1.OPERATOR_ID = T.createid
    LEFT JOIN CPSMGT.CPS_OPERATOR@DRCPSMGT op2
    ON OP2.OPERATOR_ID = T.OWNERID
    LEFT JOIN CPSMGT.cps_role_operator@DRCPSMGT RO1
    ON op1.operator_id = ro1.operator_id
    AND op1.status    <> '06'
    LEFT JOIN CPSSYS.CPS_ROLE_DEF@DRCPSMGT RD1
    ON RD1.ROLE_ID = RO1.ROLE_ID
    AND RD1.STATUS = '30'
    LEFT JOIN CPSMGT.cps_role_operator@DRCPSMGT RO2
    ON op2.operator_id = ro2.operator_id
    AND op2.status    <> '06'
    LEFT JOIN CPSSYS.CPS_ROLE_DEF@DRCPSMGT RD2
    ON RD2.ROLE_ID    = RO2.ROLE_ID
    AND RD2.STATUS    = '30'
    WHERE T.TASKSTATE = 'Completed'
    AND T.CREATETIME BETWEEN to_date(TO_CHAR(sysdate-1, 'yyyy-mm-dd')
      || ' 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
    AND to_date(TO_CHAR(sysdate-1, 'yyyy-mm-dd')
      || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
    AND t.procdefid IN ( 'IC_EditP2PMSISDNWorkflow', 'IC_ResetCustomerPinWorkflow', 'IC_ChangeCustomerProductWorkflow', 'IC_EditG2PMSISDNWorkflow', 'IC_ChangeCustomerIdentityStatusWorkflow', 'TC_CancelRemittanceWorkflow', 'IC_ChangeCustomerMSISDNWorkflow', 'IC_ResetOrgOperatorPINWorkflow', 'IC_MigrateCustTrustL1Workflow', 'IC_MigrateCustTrustL2Workflow', 'IC_ChangeCustomerIdentityKYCWorkflow', 'IC_UnblockPaymentTransactionWorkflow', 'IC_ResetOrgOperatorPasswordWorkflow' )
    AND rd1.name    IN ('Complaints (Maker)','MFS 1344 Help Line','MFS 4444 Helpline','Operator user(back end user) maker', 'Complaints Checker','Operator user(back end user) completer')
    AND rd2.name    IN ('Complaints (Maker)','MFS 1344 Help Line','MFS 4444 Helpline','Operator user(back end user) maker', 'Complaints Checker','Operator user(back end user) completer')
    UNION ALL
    -- Incomplete Orders. from all dates.
    SELECT t.taskid Task_No,
      T.ORDERID Order_ID,
      DECODE (T.PRIORITY , 50 , 'Low', 100 ,'Medium', 200 , 'High', 'Other') Task_Priority,
      T.TASKNAME Task_Name,
      TO_CHAR(t.createtime, 'HH24') Creation_Hour,
      T.CREATETIME Create_Time,
      T.COMPLETEDTIME Completed_Time,
      (to_timestamp(T.COMPLETEDTIME, 'yyyy-mm-dd hh24:mi:ss') - to_timestamp(T.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') ) Time_Difference,
      op1.user_name Initiator_Login,
      RD1.NAME Role_Assigned_Maker,
      op2.user_name Checker_Login,
      RD2.NAME Role_Assigned_Checker,
      T.TASKSTATE Task_State,
      t.tasktype Task_Type,
      T.DESCRIPTION Task_Description
    FROM cpsmgt.cps_tasktodo@DRCPSMGT t
    LEFT JOIN CPSMGT.CPS_OPERATOR@DRCPSMGT op1
    ON OP1.OPERATOR_ID = T.createid
    LEFT JOIN CPSMGT.CPS_OPERATOR@DRCPSMGT op2
    ON OP2.OPERATOR_ID = T.OWNERID
    LEFT JOIN CPSMGT.cps_role_operator@DRCPSMGT RO1
    ON op1.operator_id = ro1.operator_id
    AND op1.status    <> '06'
    LEFT JOIN CPSSYS.CPS_ROLE_DEF@DRCPSMGT RD1
    ON RD1.ROLE_ID = RO1.ROLE_ID
    AND RD1.STATUS = '30'
    LEFT JOIN CPSMGT.cps_role_operator@DRCPSMGT RO2
    ON op2.operator_id = ro2.operator_id
    AND op2.status    <> '06'
    LEFT JOIN CPSSYS.CPS_ROLE_DEF@DRCPSMGT RD2
    ON RD2.ROLE_ID     = RO2.ROLE_ID
    AND RD2.STATUS     = '30'
    WHERE T.TASKSTATE <> 'Completed'
    AND T.CREATETIME   < to_date(TO_CHAR(sysdate, 'yyyy-mm-dd')
      || ' 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
    AND t.procdefid IN ( 'IC_EditP2PMSISDNWorkflow', 'IC_ResetCustomerPinWorkflow', 'IC_ChangeCustomerProductWorkflow', 'IC_EditG2PMSISDNWorkflow', 'IC_ChangeCustomerIdentityStatusWorkflow', 'TC_CancelRemittanceWorkflow', 'IC_ChangeCustomerMSISDNWorkflow', 'IC_ResetOrgOperatorPINWorkflow', 'IC_MigrateCustTrustL1Workflow', 'IC_MigrateCustTrustL2Workflow', 'IC_ChangeCustomerIdentityKYCWorkflow', 'IC_UnblockPaymentTransactionWorkflow', 'IC_ResetOrgOperatorPasswordWorkflow' )
    AND rd1.name    IN ('Complaints (Maker)','MFS 1344 Help Line','MFS 4444 Helpline','Operator user(back end user) maker', 'Complaints Checker','Operator user(back end user) completer')
    AND rd2.name    IN ('Complaints (Maker)','MFS 1344 Help Line','MFS 4444 Helpline','Operator user(back end user) maker', 'Complaints Checker','Operator user(back end user) completer')
    UNION ALL
    SELECT t.taskid Task_No,
      T.ORDERID Order_ID,
      DECODE (T.PRIORITY , 50 , 'Low', 100 ,'Medium', 200 , 'High', 'Other') Task_Priority,
      T.TASKNAME Task_Name,
      TO_CHAR(t.createtime, 'HH24') Creation_Hour,
      T.CREATETIME Create_Time,
      T.COMPLETEDTIME Completed_Time,
      (to_timestamp(T.COMPLETEDTIME, 'yyyy-mm-dd hh24:mi:ss') - to_timestamp(T.CREATETIME, 'yyyy-mm-dd hh24:mi:ss') ) Time_Difference,
      op1.user_name Initiator_Login,
      RD1.NAME Role_Assigned_Maker,
      op2.user_name Checker_Login,
      RD2.NAME Role_Assigned_Checker,
      T.TASKSTATE Task_State,
      t.tasktype Task_Type,
      T.DESCRIPTION Task_Description
    FROM cpsmgt.cps_taskhis@DRCPSMGT t
    LEFT JOIN CPSMGT.CPS_OPERATOR@DRCPSMGT op1
    ON OP1.OPERATOR_ID = T.createid
    LEFT JOIN CPSMGT.CPS_OPERATOR@DRCPSMGT op2
    ON OP2.OPERATOR_ID = T.OWNERID
    LEFT JOIN CPSMGT.cps_role_operator@DRCPSMGT RO1
    ON op1.operator_id = ro1.operator_id
    AND op1.status    <> '06'
    LEFT JOIN CPSSYS.CPS_ROLE_DEF@DRCPSMGT RD1
    ON RD1.ROLE_ID = RO1.ROLE_ID
    AND RD1.STATUS = '30'
    LEFT JOIN CPSMGT.cps_role_operator@DRCPSMGT RO2
    ON op2.operator_id = ro2.operator_id
    AND op2.status    <> '06'
    LEFT JOIN CPSSYS.CPS_ROLE_DEF@DRCPSMGT RD2
    ON RD2.ROLE_ID = RO2.ROLE_ID
    AND RD2.STATUS = '30'
    WHERE T.CREATETIME BETWEEN to_date(TO_CHAR(sysdate-1, 'yyyy-mm-dd')
      || ' 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
    AND to_date(TO_CHAR(sysdate-1, 'yyyy-mm-dd')
      || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
    AND t.procdefid IN ( 'IC_EditP2PMSISDNWorkflow', 'IC_ResetCustomerPinWorkflow', 'IC_ChangeCustomerProductWorkflow', 'IC_EditG2PMSISDNWorkflow', 'IC_ChangeCustomerIdentityStatusWorkflow', 'TC_CancelRemittanceWorkflow', 'IC_ChangeCustomerMSISDNWorkflow', 'IC_ResetOrgOperatorPINWorkflow', 'IC_MigrateCustTrustL1Workflow', 'IC_MigrateCustTrustL2Workflow', 'IC_ChangeCustomerIdentityKYCWorkflow', 'IC_UnblockPaymentTransactionWorkflow', 'IC_ResetOrgOperatorPasswordWorkflow' )
    AND rd1.name    IN ('Complaints (Maker)','MFS 1344 Help Line','MFS 4444 Helpline','Operator user(back end user) maker', 'Complaints Checker','Operator user(back end user) completer')
    AND rd2.name    IN ('Complaints (Maker)','MFS 1344 Help Line','MFS 4444 Helpline','Operator user(back end user) maker', 'Complaints Checker','Operator user(back end user) completer')
    ) ABC
  ORDER BY 1;
  COMMIT;
END PRC_MFS_CC_LOAD_DATA;

ORCL_CC数据库中的目标表定义如下：

desc CPS.MFS_CPS_CC_TASKS_REPORT@ORCL_CC

Name                  Null Type                         
--------------------- ---- ---------------------------- 
DATA_DATE                  CHAR(8)                      
DATA_INSERTION_TIME        DATE                         
TASK_NO                    VARCHAR2(22)                 
ORDER_ID                   VARCHAR2(22)                 
TASK_PRIORITY              VARCHAR2(6)                  
TASK_NAME                  VARCHAR2(256)                
CREATION_HOUR              VARCHAR2(2)                  
CREATE_TIME                DATE                         
COMPLETED_TIME             DATE                         
TIME_DIFFERENCE            INTERVAL DAY(9) TO SECOND(0) 
INITIATOR_LOGIN            VARCHAR2(32)                 
ROLE_ASSIGNED_MAKER        VARCHAR2(100)                
CHECKER_LOGIN              VARCHAR2(32)                 
ROLE_ASSIGNED_CHECKER      VARCHAR2(100)                
TASK_STATE                 VARCHAR2(20)                 
TASK_TYPE                  VARCHAR2(32)                 
TASK_DESCRIPTION           VARCHAR2(1024)               

Answer 1

在这个过程中没有发现任何问题，在考虑寻求解决方案的替代方法时，不如尝试以下方法：

1. 您能试着在删除和插入之间签入提交吗？
2. 可以看到插入块中使用的多个DBLINK，是否允许使用足够数量的打开链接？请检查open_links和open_links_per_instance(如果在RAC中)参数，并在需要时增加值，然后重试。
3. 同样，使用MERGE并合并DELETE和INSERT的单个语句在一起。