Re: ORA-04030 Out of Process memory

Satyendra,

Apparently the error is occurring inside a stored procedure named PS_MATCH. The memory shortfall is occurring inside the private process "heap", which points to either a "process global area" (PGA) workarea, such as the "sort area" (i.e. controlled by parameter SORT_AREA_SIZE), the "hash area" (i.e. controlled by parameter HASH_AREA_SIZE), or one of the "bitmap areas" (i.e. controlled by parameters BITMAP_MERGE_AREA_SIZE or BITMAP_CREATE_AREA_SIZE).

But I'm going to take a SWAG and deduct that this problem isn't happening often. If it was, then the culprit might well be one of these global parameters. But I'm going to guess that isn't the case...

Instead, another potential huge consumer of private process "heap" are PL/SQL tables. Please check the stored procedure in question and see if line number 1298 occurs where PL/SQL tables might be in use. PL/SQL tables can chew up a fantastic amount of memory. For example, if the elements of a PL/SQL table are datatype NUMBER, then each element will require 22 bytes to be certain that any data value can be accomodated. If the elements of the PL/SQL table are VARCHAR2(30), then each element will require 32 bytes (extra 2 bytes for length) to accomodate all possible data values. Push a data set of a few million rows into those, and you're chewing up some significant memory...

<Cliff-Clavin-mode attribute="thick-Boston-accent" comment="TV show Cheers"> It's a little-known fact that PL/SQL tables are a good way to consume all available virtual memory on your database server. With a 5-line PL/SQL procedure and no process limits on memory (i.e. "ulimit -a" returns "unlimited" on UNIX), I could crash your database server in a matter of minutes, faster than Norm here could finish his beer and order another. In fact, with a 20-line PL/SQL procedure I could not only crash your server but also disturb the orbit of the planet Earth, possibly causing it to swerve out of control, crashing into the planet Venus, thus extinguishing all life as we know it...
</Cliff-Clavin-mode>

Hope this helps...

-Tim

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, November 08, 2002 11:43 PM

> Please give some suggestions how to avoid this problem, it comes after 3
> to 4 hours when i run a process through form, and it terminates then i
> have to down the database and up again to restart that procedure..
>
> ORA-04030 Out of Process memory when trying to allocate 528 Bytes (call
> heap , user umc)
> ORA-06512 at OBJ.PS_MATCH, Line 1298
> ORA-06512 at line 1
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Satyendra K Khare
> INET: satyendrak_at_delhi.tcs.co.in
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).