看trace文件是在一个Trigger的Insert语句时报错
这个库之前历经10.2.0.4.0/11.2.0.1.0/11.2.0.3.0 Linux X86三个版本都没报ORA-00600，唯独换成Linux X86-64后Trigger出了问题
Dump continued from file: /u01/product/diag/rdbms/faka/faka/trace/faka_ora_29413.trc
ORA-00600: 瞻繙糧癒聶羅罈~瞼N翻X, 瞻?翹?: [ktbesc_plugged], [], [], [], [], [], [], [], [], [], [], []
========= Dump for incident 46829 (ORA 600 [ktbesc_plugged]) ========
*** 2012-02-01 16:44:02.940
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=fz1fdg74aq59b) -----
INSERT INTO ECARD_POS_BLACKCARD(CARDID,BLACKDAY,BLACKDESP) SELECT CARDID, SYSDATE, '20' FROM ECARD_COM_EMPCARD WHERE EMPID=:B2 AND :B1 ='3' AND CARDID NOT IN (SELECT CARDID FROM ECARD_POS_BLACKCARD)
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name
0xbeb80dc8 7 ECARD_PCEBG_YT.ECARD_HRMS_EMP_TOTAL_TRIG
0x6dd3afc0 65 procedure ECARD_PCEBG_YT.ECARD_HRMS_EMP_MOVIN_TOPRODUCT
原始Trigger：
DROP TRIGGER ECARD_PCEBG_YT.ECARD_HRMS_EMP_TOTAL_TRIG;
CREATE OR REPLACE TRIGGER ECARD_PCEBG_YT.ecard_hrms_emp_total_TRIG
BEFORE INSERT or update ON
ecard_hrms_emp_total REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare cardno number;
BEGIN
if Inserting then
select ecard_hrms_emp_total_SEQ.nextval into :new.empid from dual;
elsif Updating('empstatusid') then
begin
insert into ecard_pos_blackcard(cardid,blackday,blackdesp)
select cardid, sysdate, '20' from ecard_com_empcard
where empid=:NEW.empid and :NEW.empstatusid='3'
and cardid not in (select cardid from ecard_pos_blackcard);
exception when others then null;
end;
。。。。
看那个Insert语句确实有些别扭，尝试改写一下Trigger成如下：
DROP TRIGGER ECARD_PCEBG_YT.ECARD_HRMS_EMP_TOTAL_TRIG;
CREATE OR REPLACE TRIGGER ECARD_PCEBG_YT.ecard_hrms_emp_total_TRIG
BEFORE INSERT OR UPDATE
ON ECARD_PCEBG_YT.ECARD_HRMS_EMP_TOTAL REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
cardno number;
BEGIN
IF INSERTING
THEN
SELECT ecard_hrms_emp_total_SEQ.NEXTVAL INTO :new.empid FROM DUAL;
ELSIF UPDATING ('empstatusid')
THEN
BEGIN
IF :NEW.empstatusid = '3'
THEN
INSERT INTO ecard_pos_blackcard (cardid, blackday, blackdesp)
SELECT cardid, SYSDATE, '20'
FROM ecard_com_empcard
WHERE empid = :NEW.empid
AND cardid NOT IN
(SELECT cardid FROM ecard_pos_blackcard);
END IF;
。。。。
明天再找开发确认一下。。。
ORA-00600工具没找到，搜索Bug库总算找到它了
因为我们是用传输表空间把32位 11.2.0.3切换到64位，这正好符合下面所说的状况
trace文件基本也能对上
Call Stack Trace = ktbesc -> kdiescpin -> kdifind
Ora-600 [Ktbesc_plugged] Error On Insert Or Delete [ID 1372941.1]
修改时间:2011-11-23类型:PROBLEM状态:MODERATED优先级:3
注释 (0) 转到底部
In this Document
Symptoms
Changes
Cause
Solution
References
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 11.2.0.3 to 11.2.0.3 - Release: 11.2 to 11.2
Information in this document applies to any platform.
Symptoms
Receiving the following error on insert or delete against one particular table.
ORA-00600: internal error code, arguments: [ktbesc_plugged], [], [], [], [], [], [], [], [], [], [], []
Call Stack Trace = ktbesc -> kdiescpin -> kdifind -> kdiblTestPrefixUniqueness -> kdiblLockPiece -> kdiblLockRange
Changes
Table is located on a recently plugged in tablespace into an 11.2.0.3 database.
Cause
Bug:12919564 where this problem was introduced in 11.2.0.3.
Solution
We successfully used a temporary workaround of moving the problem table to another tablespace. Once moved, we were able to insert or delete without issue.
alter table move tablespace