Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> ORA-04031 with Collections and SGA Settings

ORA-04031 with Collections and SGA Settings

From: Dereck L. Dietz <>
Date: Thu, 29 Jun 2006 21:04:35 GMT
Message-ID: <DfXog.57561$>

This is a two part question and I hope I've included enough information to elicit responses.

I am working with a data warehouse using an Oracle 10g R1 database.

I attempted a quick test of collections to see if it would work and how much of a time savings I could achieve. My steps for the test were as follows:

  1. I used a BULK COLLECT to read 978,218 rows into collection #1.
  2. I then created collection #2 from data directly in collection #1 and from concatenations and conversions of collection #1 data.
  3. I then used a FORALL to insert collection #2 into the final database table.
  4. I only was able to insert 450,561 rows before I ran out of memory and received the ORA-04031 error stating the system was unable to allocate 225432 bytes of shared memory.

I ran this test twice. Once with deleting collection #1 after I had finished creating collection #2 and once without deleting the collection. Both times I received the ORA-04031 error.

I should also add that the rows in collection #1 are 449 bytes in size (for a total size of about 422MB) and rows in collection #2 are 446 bytes in size (for a total size of about 417MB).

When I checked how the SGA was configured these were the settings:

SHARED POOL: 752 MB BUFFER CACHE: -1191182336 B LARGE POOL: 208 MB JAVA POOL: 64 MB TOTAL SGA: 3988.108 MB SGA MAX SIZE: 5904 MB The database server is also configured as a shared server.

The questions I would like to ask are these:

  1. I should be able to use Oracle collections to retrieve, modify and save data, correct?
  2. Do the settings for the SGA raise any questions/comments/concerns? If so, what would better settings be?
Received on Thu Jun 29 2006 - 16:04:35 CDT

Original text of this message