今天开发提供了一个insert语句在开发库上执行的时候居然报错: ORA-00600: 内部错误代码，参数: [qerrmOFBu1], [24347], [], [], [], [], [], [] 有些诧异。

先描述一下环境：

生产环境：

服务器：AIX Version 7.1

数据库：Oracle Database 11g Release 11.2.0.3.0 - 64bit

开发环境：

服务器：未知没有登陆权限

数据库： Oracle9i Enterprise Edition Release 9.2.0.1.0

情况详细说明：

这句insert语句中使用了一个视图VIEW_HAITUO_CUST，而这个视图是在生产环境中，然后通过dblink进行查询在插入到SSK_NEWSYS表中。

这里有一个细节，视图链接的dblink 读取的是dg2灾备库，具体情况是这样我画图说明一下：

报错信息：

执行的sql语句：

点击(此处)折叠或打开

INSERT INTO SSK_NEWSYS (SSBH, FZBH, KH,YWYH,SSMC,GSBH,JGDM,YGFS,GB,ZCDZ,YZBM,ZGZS,SXDB,/*SSDZ,LXRM,*/SSDH,E_MAIL,SSCZ,DWXZ)
select i.SSBH,i.FZBH,i.KH,i.YWYH,i.SSMC,i.GSBH,i.JGDM,i.YGFS,i.GB,i.ZCDZ,i.YZBM,i.ZGZS,i.SXDB,/*i.SSDZ,i.LXRM,*/i.SSDH,i.E_MAIL,i.SSCZi.DWXZ
from fwsb.VIEW_HAITUO_CUST@DB_shengchan_LINKI

视图的内容为：

点击(此处)折叠或打开

CREATE OR REPLACE VIEW VIEW_HAITUO_CUST AS

WITH ORTEMP AS (SELECT MAX(O.ORDER_ID) AS ORDERID, O.CUST_ID AS CUST_ID

FROM CS_ORDER O

WHERE EXISTS (SELECT 1 FROM CS_ORDER_ITEM I WHERE I.ORDER_ID = O.ORDER_ID AND I.OI_STATE = 4 AND I.EXECU_SUPP_ID = 999) GROUP BY O.CUST_ID)

SELECT

BCUST.BUSI_CUST_NO AS SSBH,ACCE.ACCE_NO AS FZBH,BO.CODE_ AS KH

,USERINFO.YWYH AS YWYH,BCUST.BUSI_CUST_NAME AS SSMC,

CUST.TRA_IND_REG_NO AS GSBH,CUST.ORGA_CODE ASJGDM,

DECODE(CON.WAY_LAB, 1, \'W\', 2, \'K\', CON.WAY_LAB) AS YGFS,

CUST.NATIONALITY AS GB,

CUST.HEAD_OFFICE_REG_NAME AS ZCDZ,

BCUST.OFFICE_ADDR_POST_CODE AS YZBM,

NVL(CO.ZGZS, 0) AS ZGZS,

CUST.STA_REP AS SXDB,

BCUST.OFFICE_ADDR AS SSDZ,

CC.CONT_NAME AS LXRM,

BCUST.CUST_TEL AS SSDH,

BCUST.EMAIL AS E_MAIL,

BCUST.COM_FAX AS SSCZ,

CUST.COM_TYPE AS DWXZ

FROM ORTEMP OD

JOIN CS_CUST_INFO CUST

ON CUST.CUST_ID = OD.CUST_ID

JOIN CS_BUSI_CUST BCUST

ON BCUST.CUST_ID = CUST.CUST_ID

JOIN (SELECT translate(BU.LOGIN_NAME,\'0123456789\'||BU.LOGIN_NAME,\'0123456789\') AS YWYH,

BUR.BASE_USER_ROLE_ID,

BUR.ORG_ID,

BR.ROLE_ID

FROM BASE_USER BU

LEFT JOIN BASE_USER_ROLE BUR

ON BUR.USER_ID = BU.USER_ID

LEFT JOIN BASE_ROLE BR

ON BR.ROLE_ID = BUR.ROLE_ID) USERINFO

ON BCUST.BUSI_REP_ID = USERINFO.BASE_USER_ROLE_ID

JOIN BASE_ORGANIZATION BO

ON BO.ORG_ID = USERINFO.ORG_ID

AND BO.ORG_STATE = 1

JOIN CS_CUST_CON CON

ON CON.CUST_ID = CUST.CUST_ID

AND CON.CS_DEP_CUST_ID = BCUST.BUSI_CUST_ID

AND CON.IS_EFFECT = 1

AND CON.CON_STATE = 3

JOIN CS_CON_ACCE ACCE

ON ACCE.CON_ID = CON.CUST_CON_ID

AND ACCE.IS_EFFECT = 1

LEFT JOIN (SELECT COUNT(DISTINCT O.UNIQ_NO) AS ZGZS, O.CON_ACCE_ID

FROM CS_ORDER O

GROUP BY O.CON_ACCE_ID) CO

ON CO.CON_ACCE_ID = ACCE.CON_ACCE_ID

LEFT JOIN (SELECT CUST_ID, CONT_NAME, CON_ACCE_ID

FROM (SELECT RANK() OVER(PARTITION BY CON_ACCE_ID ORDER BY ROWNUMS) AS RK,

CUST_ID,

CONT_NAME,

CON_ACCE_ID

FROM (SELECT CUST_ID,

CONT_NAME,

CON_ACCE_ID,

ROWNUM AS ROWNUMS

FROM (SELECT C.CUST_ID,

C.CONT_NAME,

AC.CON_ACCE_ID,

C.WHE_MAI_CON,

C.BUOP_ID

FROM BD_CRM_CONT C

JOIN CS_CUST_INFO CU

ON C.CUST_ID = CU.CUST_ID

JOIN BD_CRM_QUO_INFO QUO

ON QUO.BUOP_ID = C.BUOP_ID

JOIN CS_CON_ACCE AC

ON QUO.QUO_INFO_ID = AC.QUO_ID

GROUP BY C.CUST_ID,

C.CONT_NAME,

AC.CON_ACCE_ID,

C.WHE_MAI_CON,

C.BUOP_ID

ORDER BY C.WHE_MAI_CON DESC)))

WHERE RK = 1) CC

ON CC.CUST_ID = CUST.CUST_ID

AND CC.CON_ACCE_ID = ACCE.CON_ACCE_ID

WHERE USERINFO.ROLE_ID IN (1426, 9831);

然后第一个想法就是在开发库中把dblink的视图创建成临时表方式，看看是否可行。

没有问题：

后来想到开发环境的数据库是9I的会不会是9I数据库和11G的dblink有不兼容的地方？

然后我又找了一个测试环境环境的数据库版本为11G和生产相同，进行执行同样的操作，结果没有报错。

结论看来是数据库版本不同的原因了，解决这个问题的方法只能在开发环境创建一个临时表然后在进行插入了，但是这个问题实际上是 oralce的BUG 还是版本不同不兼容呢？

知道原因的大大们希望能给我解释一下。

ITPub博客

oracle查询远程视图进行insert语句报错：ORA-00600: 错误 [qerrmOFBu1], [24347], []

最新文章