|
Category :
Database
Resources -> SQL and PLSQL
DB Version
:
Oracle 8i / 9i
OS Details :
Sun Solaris9
When
dealing with object collections from within PLSQL, there is a chance
for you to run in to this error:
ORA-04031: unable to
allocate 12345 bytes of shared memory ("large pool",'"unknown
object","sort subheap","sort key")
The
above error and a few other memory related errors can be caused by
user global area space not being released by unused collections. Even
if a collection is not being used, the memory space is taken up by
oracle as long as the collection has any values in it. As oracle keeps
adding more space to the memory, it will eventually run out of it. We
can overcome this issue by doing two things : (1) Re-Initialize the
collection and (2) Use dbms_session.free_unused_user_memory procedure.
An
example:
create or replace type varchar_type as object
(test varchar2(6));
/
create or replace type varchar_array as table of varchar_type;
/
-- Within the package / procedure the collection needs to be initialized and the memory freed :
begin
Loop
procarray_type := varchar_array(varchar_type(null));
dbms_session.free_unused_user_memory;
-- Populate the collection and use it !!
end loop;
end;
|
