ORA-00600 psdtyfnd_with_suffix

ENG: ORA-00600 psdtyfnd_with_suffix

Windows 2003 R2 SE x64 SP2 + Oracle 10.2.0.4

При компиляции тела пакета в alert.log ошибка (так же подобное может происходить при попытке:

Fri Jul 26 12:11:05 2013
Errors in file e:\oracle\product\10.2.0\admin\rp01\udump\rp01_ora_4308.trc:
ORA-00600: internal error code, arguments: [psdtyfnd_with_suffix], [], [], [], [], [], [], []

Кусок файла rp01_ora_4308.trc.

Dump file e:\oracle\product\10.2.0\admin\rp01\udump\rp01_ora_4308.trc
Wed Jan 30 11:55:32 2013
ORACLE V10.2.0.4.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the OLAP, Data Mining and Real Application Testing options
Windows NT Version V5.2 Service Pack 2
CPU                 : 8 - type 8664, 2 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:10115M/28665M, Ph+PgF:21869M/42135M
Instance name: rp01

Redo thread mounted by this instance: 1

Oracle process number: 198

Windows thread id: 4308, image: ORACLE.EXE (SHAD)

*** 2013-01-30 11:55:32.604
*** ACTION NAME:() 2013-01-30 11:55:32.573
*** MODULE NAME:(XXX) 2013-01-30 11:55:32.573
*** SERVICE NAME:(rp01) 2013-01-30 11:55:32.573
*** SESSION ID:(625.2804) 2013-01-30 11:55:32.573
opitsk: network error occurred while two-task server trying tosend break; error code = 12571
Dump file e:\oracle\product\10.2.0\admin\rp01\udump\rp01_ora_4308.trc
Thu Jan 31 11:06:57 2013
ORACLE V10.2.0.4.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the OLAP, Data Mining and Real Application Testing options
Windows NT Version V5.2 Service Pack 2
CPU                 : 8 - type 8664, 2 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:459M/28665M, Ph+PgF:21757M/42135M
Instance name: rp01

Redo thread mounted by this instance: 1

Oracle process number: 245

Windows thread id: 4308, image: ORACLE.EXE (SHAD)

*** 2013-01-31 11:06:57.786
*** ACTION NAME:() 2013-01-31 11:06:57.661
*** MODULE NAME:() 2013-01-31 11:06:57.661
*** SERVICE NAME:() 2013-01-31 11:06:57.661
*** SESSION ID:(688.13670) 2013-01-31 11:06:57.661
opitsk: network error occurred while two-task server trying tosend break; error code = 12571
Dump file e:\oracle\product\10.2.0\admin\rp01\udump\rp01_ora_4308.trc
Fri Jul 26 12:11:05 2013
ORACLE V10.2.0.4.0 - 64bit Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the OLAP, Data Mining and Real Application Testing options
Windows NT Version V5.2 Service Pack 2
CPU                 : 8 - type 8664, 2 Physical Cores
Process Affinity    : 0x0000000000000000
Memory (Avail/Total): Ph:623M/28665M, Ph+PgF:23934M/46199M
Instance name: rp01

Redo thread mounted by this instance: 1

Oracle process number: 33

Windows thread id: 4308, image: ORACLE.EXE (SHAD)

*** ACTION NAME:() 2013-07-26 12:11:05.234
*** MODULE NAME:(TOAD 9.0.1.8) 2013-07-26 12:11:05.234
*** SERVICE NAME:(rp01) 2013-07-26 12:11:05.234
*** SESSION ID:(710.28086) 2013-07-26 12:11:05.234
*** 2013-07-26 12:11:05.234
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [psdtyfnd_with_suffix], [], [], [], [], [], [], []
Current SQL statement for this session:
ALTER PACKAGE "HEP" COMPILE SPECIFICATION REUSE SETTINGS
check trace file e:\oracle\product\10.2.0\db_1\rdbms\trace\rp01_ora_0.trc for preloading .sym file messages
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp+663           CALL???  ksedst+55            003C878B8 000000000 03B38B0F8
000000000
ksfdmp+19            CALL???  ksedmp+663           000000003 516431030 000000001
003CACC80
kgerinv+158          CALL???  ksfdmp+19            000000000 100000000 03B38C928
000000000
kgesinv+33           CALL???  kgerinv+158          000000000 000000017 000000006
03B38CBD9
ksesin+122           CALL???  kgesinv+33           000000000 495940620 000000000
8823E61716E2
psdtyfnd_with_suffi  CALL???  ksesin+122           000000006 400000000 000000000
x+421                                              49ADC1F98
psdtydrp+376         CALL???  psdtyfnd_with_suffi  017975660 0041A1014 00000AFE6
x+421                000000000
phpcmp+7233          CALL???  psdtydrp+376         000000000 000000000 000000000
000000000
pcicms2+449          CALL???  phpcmp+7233          03B38D998 41711D440 000000000
000000000
pcicmp2+78           CALL???  pcicms2+449          03B38D998 41711D440 000000000
000000000
kkxcmp0+834          CALL???  pcicmp2+78           481F988A8 00001E650 00001E650
000000030
rpiswu2+517          CALL???  kkxcmp0+834          00524A861 27F00001FB3
000000000 000000000
kkxcmp+352           CALL???  rpiswu2+517          4A5863178 88230000021B
03B38E158 300000002
kkpalt+1192          CALL???  kkxcmp+352           516431030 03B3906D8 481F988A8
000000000
opiexe+13500         CALL???  kkpalt+1192          36B1EC2B8 000000003 000000102
000000000
opiosq0+3558         CALL???  opiexe+13500         000000004 000000000 03B391BD8
000000000
opiosq+12            CALL???  opiosq0+3558         500000003 50000000F 03B3931A0
03B390000
opiodr+1136          CALL???  opiosq+12            000000000 000000000 000000000
000000000
rpidrus+230          CALL???  opiodr+1136          00000004A 00000000F 03B3931A0
000000002
rpidru+112           CALL???  rpidrus+230          03B392990 03B392A00 000000002
00001E658
rpiswu2+517          CALL???  rpidru+112           000000000 000000000 000000000
000000000
rpidrv+1611          CALL???  rpiswu2+517          4A5863178 00000021B 03B393060
000000002
rpisplu+413          CALL???  rpidrv+1611          000000002 00000004A 03B3931A0
00000000C
rpispl+43            CALL???  rpisplu+413          03B393320 0009C59BB 000000000
000000064
kqlvld+2940          CALL???  rpispl+43            516431030 003DCBCD4 003DD13C8
03B3936C0
kglgob+2572          CALL???  kqlvld+2940          516431030 03B394420 400000001
400000000
kgldpo0+971          CALL???  kglgob+2572          516431030 03B394710 000000002
000000002
kgldpo+107           CALL???  kgldpo0+971          000000138 012C4AFE8 516430D78
516430D70
kgldon+292           CALL???  kgldpo+107           516431030 000000000 516430D30
000000000
pkldon+91            CALL???  kgldon+292           516430EA8 516430F50 516430EC0
000004DF0
pkloud+330           CALL???  pkldon+91            03B394E30 000000000 000004DD8
000002000
phnnrl_name_resolve  CALL???  pkloud+330           012C2069C 000000000 0751FB228
_by_loading+567                                    000000000
phnlbn_load_by_name  CALL???  phnnrl_name_resolve  03B3950E8 080000003 000000000
+198                          _by_loading+567      03B394BB0
ph2bod+406           CALL???  phnlbn_load_by_name  03B394EA0 000000200 012C4B650
+198                 080000000
ph2uni+3634          CALL???  ph2bod+406           000001020 005F6D57F
8823D0B54592 000000001
ph2dr2+245           CALL???  ph2uni+3634          012C71058 03B396098 44B51AA90
03B3950E8
ph2drv+277           CALL???  ph2dr2+245           516431030 03B395228 01796DF98
000000000
phpsem+42            CALL???  ph2drv+277           03B396098 44B51AA90 000000000
00569E444
phpcmp+1650          CALL???  phpsem+42            200000000 300000000 000000000
000000000
pcicms2+449          CALL???  phpcmp+1650          03B396098 44B51AA90 0751CB262
000004D9C
pcicmp2+78           CALL???  pcicms2+449          03B396098 44B51AA90 0751CB262
000004D9C
kkxcmp0+834          CALL???  pcicmp2+78           003CACC80 0000000FF 5164309F0
000000000
rpiswu2+517          CALL???  kkxcmp0+834          00001E650 00045BA02 012BD20C8
000000000
kkxcmp+352           CALL???  rpiswu2+517          4A2875D80 88230000021B
03B396858 000000002
kkpcrt+1361          CALL???  kkxcmp+352           516431030 03B399D78 4194E7788
44B51AA90
opiexe+13203         CALL???  kkpcrt+1361          000000061 500000003 500000102
000000000
opiosq0+3558         CALL???  opiexe+13203         000000004 000000000 03B39B238
000000000
kpooprx+339          CALL???  opiosq0+3558         000000003 00000000E 03B39B3C8
0000000A4
kpoal8+894           CALL???  kpooprx+339          516445B10 000000018 0041AE700
000000001
opiodr+1136          CALL???  kpoal8+894           00000005E 000000017 03B39E868
000000000
ttcpip+5146          CALL???  opiodr+1136          50000005E 000000017 03B39E868
882300000000
opitsk+1818          CALL???  ttcpip+5146          516445B10 7FFFFF86000
000000018 078EF3CB7
opiino+1129          CALL???  opitsk+1818          000000000 000000000 000000000
000000000
opiodr+1136          CALL???  opiino+1129          00000003C 000000004 03B39FB20
000000000
opidrv+815           CALL???  opiodr+1136          00000003C 000000004 03B39FB20
000000000
sou2o+52             CALL???  opidrv+815           50000003C 000000004 03B39FB20
7FF7FC48580
opimai_real+131      CALL???  sou2o+52             000000002 000000000
7FFFFF87258 078EF4D1C
opimai+96            CALL???  opimai_real+131      7FF7FC48580 7FFFFF86000
0001F0003 000000000
OracleThreadStart+6  CALL???  opimai+96            03B39FEF0 03105FF3C 03B39FCC0
40                                                 7FF7FC48580
0000000078D6B6DA     CALL???  OracleThreadStart+6  03105FF3C 000000000 000000000
40                   03B39FFA8

--------------------- Binary Stack Dump ---------------------

Сначала попадаю в ORA-600 [psdtyfnd_with_suffix] (Doc ID 1229473.1) из неё в ORA-600 [psdtyfnd_with_suffix] (Doc ID 1237934.1).

Проблема возникает из-за того что пакет содержит pipelined функцию и версия Oracle ниже 11g.

Вообще то лучшая рекомендация для этого случая — перейти на Oracle 11g (уже давно пора). Но если это затруднительно — то можно попробовать исправить проблему.

Лечение. В статье описано без примеров, поэтому немного непонятно. С примерами лучше.

1) Находим id пакета (в моем примере пакет при перекомпилции которого возникает ORA-00600 называется ‘HEP’). Именно пакета, хотя не компилируется его тело.

SELECT object_name, object_type, object_id
FROM dba_objects
WHERE object_name = 'HEP'
AND object_type = 'PACKAGE'
;

OBJECT_NAME|OBJECT_TYPE|OBJECT_ID
HEP|PACKAGE|45030

2) Проблема в объектах БД в имени которых id нашего проблемного пакета и префикс _2. Нужно удалить такие объекты.

select object_name, object_type from dba_objects
where object_name like '%45030%';

OBJECT_NAME|OBJECT_TYPE
SYS_PLSQL_45030_DUMMY_1|TYPE
SYS_PLSQL_45030_9_1|TYPE
SYS_PLSQL_45030_24_1|TYPE
SYS_PLSQL_45030_24_2|TYPE
SYS_PLSQL_45030_DUMMY_2|TYPE
SYS_PLSQL_45030_9_2|TYPE

3) Удаляем такие объекты из БД

select 'drop type '||object_name||';' from dba_objects
where object_name like '%45030%_2';

'DROPTYPE'||OBJECT_NAME||';'
drop type SYS_PLSQL_45030_24_2;
drop type SYS_PLSQL_45030_DUMMY_2;
drop type SYS_PLSQL_45030_9_2;

4) Выполняем перекомпиляцию пакета

ALTER PACKAGE XXX.HEP COMPILE;

5) Или всё будет ОК. Или вы получите ошибку ORA-04043: object SYS_PLSQL_<object_id>_x_2 does not exist. В случае такой ошибки нужно выявить все синонимы на этот объект, удалить их и попробовать перекомпилировать еще раз. Мне повезло — такой ошибки не было.

select owner, object_name, object_type
from dba_objects
where name like 'SYS_PLSQL@_45030@_%'
order by object_name;

Запись ORA-00600 psdtyfnd_with_suffix впервые появилась Dmitry Bobrovsky Blog

— Author: Dmitry Bobrovsky Google

Запись опубликована в рубрике !RUS, Errors, ORACLE с метками . Добавьте в закладки постоянную ссылку.