Currently some jobs that import lots of data have crashed.
I got an 100+ MB .trc in bdump directory, containing numerous times information like below snippet:
/usr/local/oracle/admin/PROD/bdump/prod_j001_5205.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /usr/local/oracle/product/9.2.0
System name: Linux
Node name: oracle.X.X
Release: 2.6.9-89.ELsmp
Version: #1 SMP Mon Apr 20 10:34:33 EDT 2009
Machine: i686
Instance name: PROD
Redo thread mounted by this instance: 1
Oracle process number: 34
Unix process pid: 5205, image: oracle@oracle.X.X (J001)
*** 2010-10-20 16:49:57.854
*** SESSION ID:(45.2871) 2010-10-20 16:49:57.816
*********START PLSQL RUNTIME DUMP************
***Got ORA-6540 while running PLSQL***
ANONYMOUS BLOCK:
library unit=a2a5dc78 line=1 opcode=228 static link=0 scope=0
FP=b04387c PC=9cf83a5f Page=0 AP=0 ST=b043b60
DL0=b7625ebc GF=b7625f08 DL1=b7625edc DPF=b7625f00 DS=99e8aa64
DON library unit variable list instantiation
------ ------------ ------------- -------------
0 a2a5dc78 b7625f08 b76784f8
1
2
3
4
5
6
7
scope frame
-------- --------
2 0
1 b04387c
package variable address size
---------------- -------- --------
0 b7625f34 172
1 b7625fe0 172
2 b762608c 172
3 b7626138 172
4 b76261e4 172
5 b7626290 172
6 b762633c 172
7 b76263e8 172
8 b7626494 172
9 b7626540 172
10 b76265ec 172
version=43123476 instantiation size=2008
line pcode offset
-------- ------------
1 27
1 70
1 86
1 103
1 117
1 140
1 154
1 171
1 175
1 218
1 234
1 251
1 265
1 288
1 302
1 319
1 323
1 366
1 382
1 399
1 413
1 437
1 451
1 469
1 473
1 516
1 532
1 549
1 563
1 587
1 601
1 619
1 623
1 666
1 682
1 699
1 713
1 737
1 751
1 769
1 780
***********END PLSQL RUNTIME DUMP************
Unfortunatly I have little experience regarding interpreting these kind of dumps...am googling and searching on it now, but perhaps somebody here has done this numerous times already...
I'm finding out what's the best way to use the information from the dump and locate at what point in the code during processing the error occurs.
Unfortunatly this happens during extensive dynamic SQL processing, and the 'custom error handling' fails as well, so I have no other information than like the snippet above. I think I know what package errors out and what procedure, but is there a way to map the information above to the correct line number and/or package variable? Can this information linked to the DataDictionary for example?
That would be far more efficient than debugging this legacy 'code'...
Regarding the error code:
ORA-06540: PL/SQL: compilation error
Cause: A pl/sql compilation error occurred. However, the user generally will not see this error message. Instead, there will be accompanying PLS-nnnnn error messages.
Action: See accompanying PLS-nnnnn error messages.
Unfortunatly I haven't found any accompanying PLS messages....
http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/e4100.htm#sthref2034
Thanks in advance for pointers/hints/tips.
edit
Found MOS doc [ID 3658039.8], and given the dynamic SQL code + lots of data to crunch (and thus killing the shared pool) I'm wondering if it could be that something comparable is going on here...especially since the jobs do not crash when importing much smaller files.
Edited by: hoek on Oct 22, 2010 2:15 PM