?
Solved

ORA-04030 out of process memory: related to repeatedly initializing record type?

Posted on 2012-08-13
4
Medium Priority
?
671 Views
Last Modified: 2012-08-21
Am developing a Pl/SQL program and the program is quick and successful when run for a small number (1000) of rows but fails after a long run (30 minutes) if scaled up to 10,000 rows. It fails with an out of memory ORA-04030 error.

What it does:
Program processes records in a loop, calling several functions along the way. Many of these functions populate a record type like the following example:

Declare
rMyRec MyRecType; -- a type we defined

cursor c is select * from blah;

For r in c loop
  MyFunction(rMyRec);
  -- do something with current record "r" and data in rMyRec
end loop;

MyFunction does this

  rMyRec := MyRecType(null, null, null, null, null, null);
then reads a record and puts data into rMyRec

Question is: does initializing this over and over without ever releasing it cause it to use up memory, so that running with larger number of records results in memory error? That is, does it use new memory every time I do:
     rMyRec := MyRecType(null, null, null, null, null, null);

If this is the problem should I be destroying the initialized record every time, or initialize it one time in the calling routine, or something else?

Thanks,

Terry
0
Comment
Question by:murphji
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
paquicuba earned 2000 total points
ID: 38290603
use BULK COLLECT with the LIMIT clause if you can. You can also try to use the DELETE method to empty the collection

rMyRec.DELETE;
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38291654
I've not done a lot with in-memory pl/sql tables but I would imagine that it will continue to allocate memory every time you initialize a new instance of the table.

I can also easily see it not freeing up old versions until the process ends and releases allocated memory.

It is just good programming to manaully clean up structures you no longer need and not rely on the system to do it for you.
0
 

Author Comment

by:murphji
ID: 38292149
regarding: "It is just good programming to manaully clean up structures you no longer need and not rely on the system to do it for you."

I agree, not good to rely on system to clean up memory. How do you free up the memory after initializing a record after it is no longer needed? Set the object to nothing? Is there a destructor?

Repeating pseudo-code, with question on how to release memory:

Declare
rMyRec MyRecType; -- a type we defined

cursor c is select * from blah;

For r in c loop
  MyFunction(rMyRec);
  -- do something with current record "r" and data in rMyRec
  -- QUESTION: MyFunction initialized rMyRec and I am now through with it.
  -- How do I release its memory?
  -- Or, if more efficient to use it again, how can MyFunction tell if the object was initialize (see below)
end loop;

MyFunction does this

QUESTION --
-- Can the function test if rMyRec is initialized and do this initialization _ONLY_ if necessary,
-- otherwise using the existing already-initialized record?
  rMyRec := MyRecType(null, null, null, null, null, null);
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38292187
>>How do you free up the memory after initializing a record after it is no longer needed?

paquicuba already posted it:  rMyRec.DELETE;

The doc link:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#CJAFGFIG


All the other questions are basically different ways of asking the same thing so I'll try to address it here:

You can do a COUNT to see how may entries are in a collection.

It is likely more efficient to delete and reinitialize the type each time in the function.  If the function creates it, it should destroy it.

What I'm not understanding with the code you posted, is if myRec is being passed in to the function, how is it created in the function?

Does the function need an in-memory table in the first place?
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question