一次处理ORA-06512的经验

本文详细记录了解决9.2.0.8生产数据库中dbms_stats pkg无法调用导致gather_table_stats函数失败的问题,通过执行特定的SQL脚本重建pkg并重新尝试,最终成功解决问题的过程。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一次有一个9.2.0.8的生产数据库遇到下面问题,怀疑可能是dbms_stats这个内部pkg无法调用这个原因导致的。
SQL> exec dbms_stats.gather_table_stats(ownname => 'dbmgr',tabname => 'factuser_list');
BEGIN dbms_stats.gather_table_stats(ownname => 'dbmgr',tabname => 'factuser_list'); END;
*
ERROR at line 1:
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.DBMS_STATS", line 10502
ORA-06512: at "SYS.DBMS_STATS", line 10516
ORA-06512: at line 1
于是找了一个9.2.0.6测试库的,重建一下就OK了,解决过程如下,分享一下
SQL> @?/rdbms/admin/dbmsstat.sql
Package created.
Synonym created.
Grant succeeded.
create role gather_system_statistics
            *
ERROR at line 1:
ORA-01921: role name 'GATHER_SYSTEM_STATISTICS' conflicts with another user or
role name
Grant succeeded.
Grant succeeded.
Library created.
SQL> @?/rdbms/admin/prvtstas.plb
Package created.
Package body created.
SQL> 
SQL> @?/rdbms/admin/prvtstat.plb
Package body created.
SQL> 
SQL> exec dbms_stats.gather_table_stats(ownname => 'dbmgr',tabname => 'factuser_list');
PL/SQL procedure successfully completed.
SQL
<think>我们正在分析存储过程P_TI_MESTOROLL_INSERT_1450中出现的错误。错误信息包括:-ORA-06512:表示错误发生的具体号(这里是47)-ORA-00001:唯一约束违反(具体约束为MES.PK_TI_MESTOROLL)-ORA-02063:由于dblink(BKD_1450)引起的错误根据错误信息,我们推测问题发生在循环rollList的过程中(因为报错47在循环体内)。具体分析如下:1.ORA-00001(唯一约束违反):错误表明在向表TI_MESTOROLL@bkd_1450插入数据时,违反了主键或唯一约束(约束名为PK_TI_MESTOROLL)。原因:插入的数据中,主键字段(或多个字段组合)的值在目标表中已经存在。2.ORA-02063(由于dblink引起的错误):该错误通常发生在使用dblink进远程操作时,可能是网络问题、远程数据库不可用、远程对象不存在或权限不足等。但结合ORA-00001,这里更可能是由于dblink操作(插入)引发了远程表的唯一约束违反,然后通过dblink传递回本地。3.ORA-06512:指出错误发生在存储过程的第47。根据提供的存储过程代码,我们需要定位第47对应的操作。由于代码号可能因环境不同有差异,我们根据代码结构推测:循环体内的第一个操作是检查dblink(重复的检查,可优化),然后是插入语句(插入远程表TI_MESTOROLL@bkd_1450)。报错47很可能对应插入语句(因为插入语句最可能违反唯一约束)。因此,我们关注循环体内的插入部分:```sqlinsertintoTI_MESTOROLL@bkd_1450(C_HR_UP_NO,C_ROLL_NO,...)--字段列表values(roll.C_HR_UP_NO,roll.C_ROLL_NO,...);```导致唯一约束违反的可能原因:(1)在远程表TI_MESTOROLL上,存在主键(或唯一约束)PK_TI_MESTOROLL,可能包含一个或多个字段(如C_ROLL_NO等)。(2)当前插入的记录,其主键字段的值在远程表中已存在。解决方案:1.在插入前检查远程表中是否已存在相同主键的记录。注意:由于通过dblink操作,直接查询远程表会有性能开销,但可以避免重复插入。修改循环体内的插入操作为:```sql--在插入前先检查远程表是否存在相同主键的记录(假设主键字段为C_ROLL_NO)DECLAREv_countNUMBER;BEGINSELECTCOUNT(*)INTOv_countFROMTI_MESTOROLL@bkd_1450WHEREC_ROLL_NO=roll.C_ROLL_NO;--根据实际主键字段调整IFv_count=0THEN--插入insertintoTI_MESTOROLL@bkd_1450...;ELSE--如果已经存在,则跳过插入,但记录日志或更新状态--例如:将本地表的状态更新为“重复”,避免下次再处理UPDATETI_MESTOROLL_1450SETc_msg_content='重复记录,跳过插入'WHEREc_msgid=roll.c_msgid;ENDIF;EXCEPTIONWHENOTHERSTHEN--处理可能的查询失败(如dblink问题)errorCode:=SQLCODE;errorName:=SQLERRM;--记录错误日志INSERTINTOT_BPES_ERROR(C_TABLE_NAME,C_ORL_CODE,C_ORL_NAME,D_CRT_TIME)VALUES('P_TI_MESTOROLL_INSERT_1450',errorCode,errorName,SYSDATE);--注意:这里只是跳过当前记录,继续处理下一个--也可以考虑根据错误严重程度决定是否退出END;```2.另一种方案(高效但需确保远程表有错误记录处理机制):使用DML错误日志记录功能(即使用`LOGERRORS`子句)。但这种方法需要先在远程表上创建错误日志表,并且通过dblink操作可能会复杂。3.避免重复处理:在循环开始前,从本地表TI_MESTOROLL_1450中只选取未处理且不重复的记录(但需知道哪些记录在远程表已存在,这需要跨库查询,性能可能差)。4.对循环体内的每一次插入进异常捕获(我们当前在循环体内已经有异常捕获,但捕获的是dblink检查的异常,并没有捕获插入操作的异常):在插入语句周围添加异常处理块,捕获ORA-00001错误。例如:```sqlBEGINinsertintoTI_MESTOROLL@bkd_1450...;EXCEPTIONWHENDUP_VAL_ON_INDEXTHEN--捕获ORA-00001--处理重复记录UPDATETI_MESTOROLL_1450SETc_msg_content='重复记录,跳过插入'WHEREc_msgid=roll.c_msgid;WHENOTHERSTHEN--处理其他异常errorCode:=SQLCODE;errorName:=SQLERRM;--记录错误日志INSERTINTOT_BPES_ERROR(C_TABLE_NAME,C_ORL_CODE,C_ORL_NAME,D_CRT_TIME)VALUES('P_TI_MESTOROLL_INSERT_1450',errorCode,errorName,SYSDATE);END;```这种方法可以直接捕获插入过程中出现的唯一约束违反,并进相应处理。考虑到性能(远程表查询可能慢)和代码简洁性,推荐使用第4种方法(捕获插入异常)。同时,针对ORA-02063错误(dblink问题),我们在插入操作外添加异常捕获处理(如上面的WHENOTHERS),避免因单条记录插入失败导致整个存储过程退出。因此,修改后的循环体内插入操作如下:```sql--发送卷号信息给二级BEGINinsertintoTI_MESTOROLL@bkd_1450...;--原来的插入语句--如果插入成功,更新状态为已发送updateTI_MESTOROLL_1450setc_msg_content='已发送',C_INTERFACE_TIME_R=sysdatewherec_msgid=roll.c_msgid;EXCEPTIONWHENDUP_VAL_ON_INDEXTHEN--ORA-00001--处理重复记录:更新本地状态,避免再次处理updateTI_MESTOROLL_1450setc_msg_content='重复记录,跳过插入'wherec_msgid=roll.c_msgid;WHENOTHERSTHEN--处理其他异常(包括ORA-02063等)errorCode:=SQLCODE;errorName:=SQLERRM;--记录错误到日志表INSERTINTOT_BPES_ERROR(C_TABLE_NAME,C_ORL_CODE,C_ORL_NAME,D_CRT_TIME)VALUES('P_TI_MESTOROLL_INSERT_1450',errorCode,errorName,SYSDATE);--更新本地状态为发送失败updateTI_MESTOROLL_1450setc_msg_content='发送失败:'||errorCode||'-'||errorNamewherec_msgid=roll.c_msgid;--注意:这里我们不中断循环,继续处理下一条记录END;```这样,在每次插入操作后立即更新本地记录状态(无论成功还是失败)。注意,在异常处理块中更新状态,避免重复插入。另外,注意存储过程中多处重复的dblink检查(在循环前、循环内、循环后都有检查)。这些检查可能会影响性能,且没有必要在每个循环中都检查多次。可以考虑在循环开始前检查一次即可。其他优化建议:-减少dblink的使用频率:将插入操作批量处理(例如使用FORALL或批量SQL)而不是逐提交。但批量操作需要收集一批记录,在远程表插入。如果使用批量,则批量操作可以一次性提交多,然后统一处理异常(使用SAVEEXCEPTIONS),但通过dblink批量操作可能性能提升有限且复杂。-由于当前代码是逐条处理,所以上述的逐条异常捕获是合适的。修改后的存储过程(仅修改循环体内插入操作部分)将能够处理重复记录和dblink相关的错误,并记录详细错误信息。注意:循环体内插入操作后立即更新状态,因此我们可以删除循环末尾的单独更新语句(现在在插入成功后就立即更新了)。最后,我们调整循环体如下(伪代码):```sqlFORrollINrollListLOOP--先检查dblink(如果保留的话,但建议只在循环外检查一次--此处可以保留一次检查,或者去掉以减少开销(因为循环外已检查)--然后开始插入操作并捕获异常BEGIN--插入INSERTINTOTI_MESTOROLL@bkd_1450(...)VALUES(...);--插入成功,更新本地记录状态为已发送UPDATETI_MESTOROLL_1450SETc_msg_content='已发送',C_INTERFACE_TIME_R=SYSDATEWHEREc_msgid=roll.c_msgid;EXCEPTIONWHENDUP_VAL_ON_INDEXTHEN--处理重复记录UPDATETI_MESTOROLL_1450SETc_msg_content='重复记录,跳过插入'WHEREc_msgid=roll.c_msgid;WHENOTHERSTHEN--其他错误处理errorCode:=SQLCODE;errorName:=SQLERRM;--记录错误日志INSERTINTOT_BPES_ERROR(C_TABLE_NAME,C_ORL_CODE,C_ORL_NAME,D_CRT_TIME)VALUES('P_TI_MESTOROLL_INSERT_1450',errorCode,errorName,SYSDATE);--更新状态为发送失败UPDATETI_MESTOROLL_1450SETc_msg_content='发送失败:'||errorCode||'-'||SUBSTR(errorName,1,200)WHEREc_msgid=roll.c_msgid;END;ENDLOOP;```同时,我们建议将循环之前的dblink检查保留(在存储过程开头),但循环内的多次检查可以移除(因为插入操作本身会抛出dblink错误,我们已经在异常处理中捕获)。另外,存储过程末尾的成分信息发送部分也可能遇到类似问题,建议用同样方法修改(插入前检查或捕获异常)。由于问题集中在卷信息发送的循环部分,我们先修改这部分。这样,当出现重复记录或dblink问题时,存储过程不会中断,而是记录错误并继续处理后续记录。最后统一提交(注意:在循环中更新了本地表,但是在一个事务中,直到存储过程末尾的COMMIT才提交。但是,如果在循环中记录错误时使用了COMMIT,则需要调整事务处理。注意:当前存储过程在循环中没有COMMIT,而是在循环结束后有一个COMMIT。但我们在循环中执了DML(更新本地表),这些更新会随着存储过程末尾的COMMIT一起提交。但是,如果在循环中插入错误日志(T_BPES_ERROR)后立即COMMIT,会导致事务断开,后续操作可能失败(如更新本地表状态)?而原存储过程在异常块中使用了ROLLBACK,会回滚整个事务,这可能不是我们想要的(因为希望处理一条失败后继续下一条)。原存储过程中,在异常块里使用了ROLLBACK,这会导致当前事务的所有操作回滚。但我们的目的是单条失败不影响整体,所以不应该在循环体内回滚整个事务。因此,我们需要调整事务处理方式:1.去除循环体内的ROLLBACK(在异常处理块中,我们不再回滚整个事务,而是记录错误并继续)。2.确保循环体内的更新操作(更新本地表状态)和错误日志记录是同一个事务的一部分,并在存储过程末尾统一提交。但是,如果在循环体内的操作中发生错误,我们希望当前记录的操作回滚(比如插入远程表失败,则不应该更新本地表为已发送),但是错误日志需要记录,同时本地表状态更新为“发送失败”可以保留(因为我们需要标记这条记录处理失败,避免下次再处理)。因此,我们在循环体内使用自治事务(autonomoustransaction)来记录错误日志,避免影响主事务。同时,对于本地表状态的更新,我们希望和主事务一起提交(这样如果整个存储过程失败,所有状态都不会改变)。但是,记录错误日志(T_BPES_ERROR)是独立于主事务的,即使后面存储过程失败,错误日志已经记录。更新本地表状态则随主事务在最后提交。我们可以这样修改:在循环体内的异常处理块中,使用自治事务来插入错误日志,而更新本地表状态属于主事务,在最后统一提交。但是,更新本地表状态的操作可能也会失败(例如唯一约束违反),所以我们希望每次循环中更新本地表状态的操作立即提交?这可以通过自治事务实现,但这样会导致频繁提交,破坏事务一致性。权衡后,我们选择:循环体内的操作(插入远程表、更新本地表)作为主事务的一部分,错误日志记录使用自治事务。修改循环体内的异常处理:```sqlEXCEPTIONWHENDUP_VAL_ON_INDEXTHEN--更新本地表状态(属于主事务)UPDATETI_MESTOROLL_1450SETc_msg_content='重复记录,跳过插入'WHEREc_msgid=roll.c_msgid;WHENOTHERSTHENerrorCode:=SQLCODE;errorName:=SQLERRM;--记录错误日志到T_BPES_ERROR(使用自治事务,独立提交)DECLAREPRAGMAAUTONOMOUS_TRANSACTION;BEGININSERTINTOT_BPES_ERROR(C_TABLE_NAME,C_ORL_CODE,C_ORL_NAME,D_CRT_TIME)VALUES('P_TI_MESTOROLL_INSERT_1450',errorCode,errorName,SYSDATE);COMMIT;EXCEPTIONWHENOTHERSTHENROLLBACK;END;--更新本地表状态(主事务)UPDATETI_MESTOROLL_1450SETc_msg_content='发送失败:'||errorCode||'-'||SUBSTR(errorName,1,200)WHEREc_msgid=roll.c_msgid;END;```这样,错误日志记录独立提交,而更新本地表状态的操作随主事务在存储过程末尾提交。但是,如果更新本地表状态的操作也失败了(例如ORA-00001或其他异常),则整个事务会回滚(到存储过程开头)。所以,我们必须确保更新本地表状态的语句不会失败(例如通过主键更新,且不违反约束)。由于更新本地表时使用的是主键(c_msgid),应该不会失败。因此,我们按上述方式修改。总结修改步骤:1.在循环体内插入远程表操作周围添加异常处理块,捕获DUP_VAL_ON_INDEX和其他异常。2.在捕获到重复记录时,更新本地表状态为“重复记录,跳过插入”。3.在捕获到其他异常时,使用自治事务记录错误日志,并更新本地表状态为“发送失败”。4.删除循环体内不必要的dblink检查(可以减少到一次在循环外)。5.确保循环体内的更新本地表操作属于主事务,在存储过程末尾统一提交。按照上述修改后,存储过程将能够处理ORA-00001ORA-02063错误,并继续执后续记录。</think> ## 分析及解决 ORA-06512ORA-00001ORA-02063 错误 根据错误信息和存储过程代码,定位到循环 `rollList` 中的核心问题: ### 错误原因分析 1. **ORA-00001 (唯一约束违反)** - 错误发生在第47附近(`insert into TI_MESTOROLL@bkd_1450`) - 根本原因:**重复插入违反唯一约束 `PK_TI_MESTOROLL`** - 触发场景: ```sql FOR roll IN rollList LOOP -- 遍历未处理记录 insert into TI_MESTOROLL@bkd_1450 (...) -- 向远程表插入 END LOOP; ``` **问题**:当同一卷号(`C_ROLL_NO`)在 `TI_MESTOROLL_1450` 表中存在多条记录时(如状态更新延迟),会导致重复插入远程表 2. **ORA-02063 (dblink 错误)** - 发生在 `insert into TI_MESTOROLL@bkd_1450` 操作时 - 直接原因:远程数据库连接不稳定或超时 - 加剧因素:**逐提交 + 频繁 dblink 检查**(每次循环都执3次 `P_DbLinkIsOk`) 3. **ORA-06512** - 前两个错误引发的调用栈跟踪,指向具体错误位置 ### 根本原因总结 | 问题 | 关键代码段 | 解决方案方向 | |------|------------|--------------| | **重复插入** | 逐插入远程表<br>无防重机制 | 添加唯一性检查<br>改用批量插入 | | **dblink 超时** | 每次循环3次连通性检查<br>网络开销大 | 减少 dblink 调用次数 | | **锁竞争** | 未提交事务占用资源<br>导致后续操作失败 | 优化事务提交策略 | --- ## 解决方案及代码修改 ### 1. 添加唯一性检查(解决 ORA-00001) 在插入前验证远程表是否已存在相同卷号: ```sql FOR roll IN rollList LOOP -- 检查远程表是否存在相同卷号(使用 dblink) DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM TI_MESTOROLL@bkd_1450 WHERE C_ROLL_NO = roll.C_ROLL_NO; -- 主键字段 IF v_count = 0 THEN -- 插入远程表(原47操作) insert into TI_MESTOROLL@bkd_1450 (...) values (...); ELSE -- 标记本地记录为重复 UPDATE TI_MESTOROLL_1450 SET c_msg_content = '重复记录' WHERE c_msgid = roll.c_msgid; END IF; EXCEPTION WHEN OTHERS THEN -- 处理 dblink 异常 errorCode := SQLCODE; errorName := SQLERRM; INSERT INTO T_BPES_ERROR (...) VALUES (...); END; END LOOP; ``` ### 2. 减少 dblink 调用频率(解决 ORA-02063) 优化策略: ```sql -- 存储过程开头只检查1次 dblink IF P_DbLinkIsOk('bkd_1450') = false THEN errorCode := sqlcode; errorName := 'dblink异常'; INSERT INTO T_BPES_ERROR (...); RETURN; -- 直接退出 END IF; -- 循环中移除所有 P_DbLinkIsOk 检查 FOR roll IN rollList LOOP -- 直接操作远程表(已确保连接可用) ... END LOOP; ``` ### 3. 批量插入优化(减少网络开销) ```sql -- 使用 FORALL 批量处理 DECLARE TYPE t_roll_tab IS TABLE OF TI_MESTOROLL_1450%ROWTYPE; v_rolls t_roll_tab; BEGIN SELECT * BULK COLLECT INTO v_rolls FROM TI_MESTOROLL_1450 WHERE c_msg_content IS NULL; FORALL i IN 1..v_rolls.COUNT INSERT INTO TI_MESTOROLL@bkd_1450 (...) VALUES (...); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- 批量处理重复记录 ... END; ``` ### 4. 添加异常专项处理 ```sql EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- 捕获ORA-00001 errorCode := SQLCODE; errorName := '唯一约束违反(PK_TI_MESTOROLL)'; ROLLBACK; -- 记录错误详细信息 INSERT INTO T_BPES_ERROR(...) VALUES ('P_TI_MESTOROLL_INSERT_1450', errorCode, errorName, SYSDATE); WHEN OTHERS THEN IF SQLCODE IN (-2081, -2082) THEN -- ORA-02081 errorName := 'dblink不可用'; END IF; -- ...其他错误处理 ``` --- ## 预防性措施 1. **索引优化**: ```sql CREATE UNIQUE INDEX IDX_ROLL_NO ON TI_MESTOROLL_1450(C_ROLL_NO); ``` 2. **异步重试机制**: ```sql -- 失败记录加入重试队列 UPDATE TI_MESTOROLL_1450 SET retry_count = NVL(retry_count,0) + 1, next_retry = SYSDATE + INTERVAL '5' MINUTE WHERE c_msgid = roll.c_msgid; ``` 3. **连接池配置**: ```sql ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE; ALTER SESSION SET DB_LINK_SESSION_TIMEOUT = 30; -- 30秒超时 ``` ---
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值