Pacita_Tibay
asked on
How to resolve an ORA-06500:PL/SQL: storage error with ORA-06512, ORA-04088
Hi,
Our Developer is running a script of inserting many rows of data and he is getting the following error:
SQL> INSERT INTO TAKEOFF_DTLS_REF
2 (TAKEOFF_HDR_SEQ, TAKEOFF_TYPE,TAKEOFF_CAT,I
3 TO_QTYS, TO_UOM, PROJ_NO, PO_NO, BID_ITEM,
4 TOT_PCT, MI_PCT_1, MI_PCT_2, MI_PCT_4, MI_PCT_3, MI_PCT_5, MI_PCT_6, MI_PCT_7, MI_PCT_8,
5 SCR_NO, SCR_REV_NO,
6 REF_COL_01, REF_COL_02, REF_COL_03, REF_COL_04, REF_COL_05, REF_COL_06, REF_COL_07,
7 REF_COL_08, REF_COL_09, REF_COL_10, REF_COL_11, REF_COL_12, REF_COL_13, REF_COL_14, REF_COL_15,
8 SYSTEM_NO, TEST_PKG )
9 SELECT TAKEOFF_HDR_SEQ,TAKEOFF_TY
10 TO_QTYS,TO_UOM,PROJ_NO,
11 '1312008500CH-0001',
12 'ZZZZ.'||SUBSTR(BID_ITEM,6
13 TOT_PCT, MI_PCT_1, MI_PCT_2,MI_PCT_4,MI_PCT_3
14 SCR_NO, SCR_REV_NO,
15 REF_COL_01, REF_COL_02, REF_COL_03, REF_COL_04,REF_COL_05, REF_COL_06, REF_COL_07,
16 REF_COL_08, REF_COL_09, REF_COL_10, REF_COL_11,REF_COL_12,REF_
17 SYSTEM_NO,TEST_PKG
18 FROM TAKEOFF_DTLS_REF
19 WHERE PROJ_NO = '1312008500'
20 AND PO_NO = '1312008500PR-0001'
21 /
INSERT INTO TAKEOFF_DTLS_REF
*
ERROR at line 1:
ORA-06500: PL/SQL: storage error
ORA-06512: at "CIR_2000.TAKEOFF_DTLS_REF
ORA-06512: at "CIR_2000.T_IB_TAKEOFF_DTL
ORA-04088: error during execution of trigger 'CIR_2000.T_IB_TAKEOFF_DTL
I searched for the ORA-06500 error and found this info:
ORA-06500: PL/SQL: storage error
Cause: PL/SQL was unable to allocate additional storage. This message normally appears with an ORA-4030 or ORA-4031 error which gives additional information. Sometimes this error can be caused by runaway programs.
Action: 1) Ensure there are no issues or bugs in your PL/SQL program which are causing excessive amounts of memory to be used. 2) Programmatically cause unused objects to be freed (e.g. by setting them to NULL). 3) Increase the amount of shared or process memory (as appropriate) available to you.
How do I found that the issue is due to #3? Should there be an error in the alert log? Developer thinks that the issue is due to not enough rollback segments. But if it, then should there be an error in the log file too?
Any info is greatly appreciated.
Thanks.
Our Developer is running a script of inserting many rows of data and he is getting the following error:
SQL> INSERT INTO TAKEOFF_DTLS_REF
2 (TAKEOFF_HDR_SEQ, TAKEOFF_TYPE,TAKEOFF_CAT,I
3 TO_QTYS, TO_UOM, PROJ_NO, PO_NO, BID_ITEM,
4 TOT_PCT, MI_PCT_1, MI_PCT_2, MI_PCT_4, MI_PCT_3, MI_PCT_5, MI_PCT_6, MI_PCT_7, MI_PCT_8,
5 SCR_NO, SCR_REV_NO,
6 REF_COL_01, REF_COL_02, REF_COL_03, REF_COL_04, REF_COL_05, REF_COL_06, REF_COL_07,
7 REF_COL_08, REF_COL_09, REF_COL_10, REF_COL_11, REF_COL_12, REF_COL_13, REF_COL_14, REF_COL_15,
8 SYSTEM_NO, TEST_PKG )
9 SELECT TAKEOFF_HDR_SEQ,TAKEOFF_TY
10 TO_QTYS,TO_UOM,PROJ_NO,
11 '1312008500CH-0001',
12 'ZZZZ.'||SUBSTR(BID_ITEM,6
13 TOT_PCT, MI_PCT_1, MI_PCT_2,MI_PCT_4,MI_PCT_3
14 SCR_NO, SCR_REV_NO,
15 REF_COL_01, REF_COL_02, REF_COL_03, REF_COL_04,REF_COL_05, REF_COL_06, REF_COL_07,
16 REF_COL_08, REF_COL_09, REF_COL_10, REF_COL_11,REF_COL_12,REF_
17 SYSTEM_NO,TEST_PKG
18 FROM TAKEOFF_DTLS_REF
19 WHERE PROJ_NO = '1312008500'
20 AND PO_NO = '1312008500PR-0001'
21 /
INSERT INTO TAKEOFF_DTLS_REF
*
ERROR at line 1:
ORA-06500: PL/SQL: storage error
ORA-06512: at "CIR_2000.TAKEOFF_DTLS_REF
ORA-06512: at "CIR_2000.T_IB_TAKEOFF_DTL
ORA-04088: error during execution of trigger 'CIR_2000.T_IB_TAKEOFF_DTL
I searched for the ORA-06500 error and found this info:
ORA-06500: PL/SQL: storage error
Cause: PL/SQL was unable to allocate additional storage. This message normally appears with an ORA-4030 or ORA-4031 error which gives additional information. Sometimes this error can be caused by runaway programs.
Action: 1) Ensure there are no issues or bugs in your PL/SQL program which are causing excessive amounts of memory to be used. 2) Programmatically cause unused objects to be freed (e.g. by setting them to NULL). 3) Increase the amount of shared or process memory (as appropriate) available to you.
How do I found that the issue is due to #3? Should there be an error in the alert log? Developer thinks that the issue is due to not enough rollback segments. But if it, then should there be an error in the log file too?
Any info is greatly appreciated.
Thanks.
Devinder Singh Virdi
Please post the coding of trigger 'CIR_2000.T_IB_TAKEOFF_DTL
ASKER
Here's the trigger.
T-IB-TAKEOFF-DTLS-REF.sql
T-IB-TAKEOFF-DTLS-REF.sql
ASKER
Hi,
I'd like to mention that the select statement results to 56,426 rows to be inserted into the same table,; hence, the trigger fires also.
Thanks.
I'd like to mention that the select statement results to 56,426 rows to be inserted into the same table,; hence, the trigger fires also.
Thanks.
>> How do I found that the issue is due to #3?
>> Should there be an error in the alert log?
>> Developer thinks that the issue is due to not enough rollback segments. But if it, then should there be an error in the log file too?
1. Line 212 in trigger is TOH.OLD_REF_COL_12 := :OLD.REF_COL_12; Just check datatype and length.
2. You an check Trace file in user dump directory, You will get something.
3. Since memory issue is loged in Alert file, you can check for it.
In your trigger, you defined many cursor, which actually don't need. Instead you can directly use
select count(*) into variable ..... therefore you are gaining performance as well as reducing resource usage.
for every row you are using if condition :NEW.REF_COL_12:=UPPER(:NE
I would suggest you to remove if and directly put this :NEW.REF_COL_12:=UPPER(:NE
If condition is satisfied, then that will be called two times, first for if and second for assign.
This trigger also calls many procedures and I we cannot say anything about that.
This code is very old and I belive its time to implement this piece of code in frontend rather than backend.
ie convert every field to upper case, prepare insert for TOH, calling of TAKEOFF_DTLS_REF_PKG.ADD_T
>> Should there be an error in the alert log?
>> Developer thinks that the issue is due to not enough rollback segments. But if it, then should there be an error in the log file too?
1. Line 212 in trigger is TOH.OLD_REF_COL_12 := :OLD.REF_COL_12; Just check datatype and length.
2. You an check Trace file in user dump directory, You will get something.
3. Since memory issue is loged in Alert file, you can check for it.
In your trigger, you defined many cursor, which actually don't need. Instead you can directly use
select count(*) into variable ..... therefore you are gaining performance as well as reducing resource usage.
for every row you are using if condition :NEW.REF_COL_12:=UPPER(:NE
I would suggest you to remove if and directly put this :NEW.REF_COL_12:=UPPER(:NE
If condition is satisfied, then that will be called two times, first for if and second for assign.
This trigger also calls many procedures and I we cannot say anything about that.
This code is very old and I belive its time to implement this piece of code in frontend rather than backend.
ie convert every field to upper case, prepare insert for TOH, calling of TAKEOFF_DTLS_REF_PKG.ADD_T
ASKER
I asked the Developer to run the script again to see if it will generate an error in the log file. This time the script completed its job. I checked the \udump folder in the database server and the only trace file that could see was when the Developer encountered the error the first time he ran the script. The trace file shows this:
*** 2010-05-26 08:46:04.973
*** SESSION ID:(11.7955) 2010-05-26 08:46:04.769
*********START PLSQL RUNTIME DUMP************
***Got ORA-4030 while running PLSQL***
TRIGGER CIR_2000.T_IB_TAKEOFF_DTLS
library unit=22509d4c line=79 opcode=228 static link=0 scope=0
FP=7fb9a700 PC=22502b19 Page=0 AP=0 ST=7fb9f4a0
DL0=7fb994d0 GF=7fb99584 DL1=7fb99524 DPF=7fb9957c DS=22509140
Dump of static Frame info:
scope frame
-------- --------
2 0
1 7fb9a700
Dump of dependent info:
DON library unit variable list instantiation
--- ------------ ------------- -------------
0 22509d4c 7fb99584 7fb8858c
1
2
3
I don't see an error in the alert log file.
Any idea?
Thanks.
*** 2010-05-26 08:46:04.973
*** SESSION ID:(11.7955) 2010-05-26 08:46:04.769
*********START PLSQL RUNTIME DUMP************
***Got ORA-4030 while running PLSQL***
TRIGGER CIR_2000.T_IB_TAKEOFF_DTLS
library unit=22509d4c line=79 opcode=228 static link=0 scope=0
FP=7fb9a700 PC=22502b19 Page=0 AP=0 ST=7fb9f4a0
DL0=7fb994d0 GF=7fb99584 DL1=7fb99524 DPF=7fb9957c DS=22509140
Dump of static Frame info:
scope frame
-------- --------
2 0
1 7fb9a700
Dump of dependent info:
DON library unit variable list instantiation
--- ------------ ------------- -------------
0 22509d4c 7fb99584 7fb8858c
1
2
3
I don't see an error in the alert log file.
Any idea?
Thanks.
>> Got ORA-4030 while running PLSQL
Try to increase sort area size
show parameter sort
sort_area_size integer 65536 ---- increase to 65K more.
http://www.dba-oracle.com/t_ora_04030_out_process_memory.htm
Try to increase sort area size
show parameter sort
sort_area_size integer 65536 ---- increase to 65K more.
http://www.dba-oracle.com/t_ora_04030_out_process_memory.htm
ASKER
Sort_area_size parameter is 65K. Did not change anything but problem of developer disappeared.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.