Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am running Oracle 11g R1 on my local development machine.

I have been trying to run an anonymous block to test the FORALL statement in Oracle to insert 25,000 records.

I get the following error:

ORA-04031: unable to allocate 4708660 bytes of shared memory ("shared pool","DECLARE
  -- temp_rec temp%R...","PL/SQL SOURCE","PLD: Source Heap")
04031. 00000 -  "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
*Cause:    More shared memory is needed than was allocated in the shared
           pool.
*Action:   If the shared pool is out of memory, either use the
           dbms_shared_pool package to pin large packages,
           reduce your use of shared memory, or increase the amount of
           available shared memory by increasing the value of the
           INIT.ORA parameters "shared_pool_reserved_size" and
           "shared_pool_size".
           If the large pool is out of memory, increase the INIT.ORA
           parameter "large_pool_size".

I tried looking here and here but wasn't able to resolve this issue.

I have tried the following:

ALTER SYSTEM FLUSH BUFFER_CACHE;  
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM SET cursor_sharing = 'SIMILAR' SCOPE=BOTH;

Please help.

EDIT

The code I am using is as follows:

DECLARE
  TYPE t_varchar IS TABLE OF varchar(512 char);
  biz_hierarchy t_varchar := t_varchar();
  project_team t_varchar := t_varchar();
  user_roles t_varchar := t_varchar();
  username t_varchar := t_varchar();
  sso t_varchar := t_varchar();
BEGIN
  SELECT *
  BULK COLLECT INTO biz_hierarchy, project_team, user_roles, username, sso
  FROM ... -- Query fetches 25000 records

  FORALL i IN biz_hierarchy.FIRST..biz_hierarchy.LAST
    INSERT INTO temp VALUES (biz_hierarchy(i), project_team(i),
                              user_roles(i), username(i), sso(i));

END;
/

The error returned by Oracle does not mention the line number. If I try loading 5000 records, the code block runs successfully. Fails when I try with 25000.

share|improve this question
    
25,000 records doesn't sound like much. Could you post your SQL statement, please? –  Frank Schmitt Jul 19 '13 at 8:28
    
@Frank Schmitt: Added the code. Please check. –  Rachcha Jul 19 '13 at 9:00
    
4.5GB of shared memory for one statement is huge. First I would get 11.2.0.3. 11.1 series are old and you may be hitting some bug. –  Mindaugas Riauba Jul 19 '13 at 10:14
    
It actually needs 4708660 bytes, not kilobytes, which is why I'm wondering how come Oracle can't handle this. 4.5 MB of shared memory should not be a problem. I just don't know how to change the settings. –  Rachcha Jul 19 '13 at 10:59
    
It actually complains about 4708660 additional bytes - I guess it already allocated much more before running into this. –  Frank Schmitt Jul 19 '13 at 13:18
show 1 more comment

2 Answers 2

up vote 1 down vote accepted

I'd recommend limiting the number of rows you fetch in your BULK COLLECT and wrapping the whole thing inside a loop:

 -- create our output table
 create table temp as
 select 
   object_type as biz_hierarchy, 
   object_name as project_team, 
   object_name as user_roles,
   object_name as username, 
   object_name as sso
 from all_objects where 1=0;
/

DECLARE
  TYPE t_varchar IS TABLE OF varchar(512 char);
  biz_hierarchy t_varchar := t_varchar();
  project_team  t_varchar := t_varchar();
  user_roles    t_varchar := t_varchar();
  username      t_varchar := t_varchar();
  sso           t_varchar := t_varchar();
  cursor cur is
    select object_type as biz_hierarchy,
           object_name as project_team,
           object_name as user_roles,
           object_name as username,
           object_name as sso
      from all_objects
     where rownum <= 25000;
BEGIN
  open cur;
  loop
    fetch cur BULK COLLECT
      INTO biz_hierarchy,
           project_team,
           user_roles,
           username,
           sso limit 1000;
    FORALL i IN biz_hierarchy.FIRST .. biz_hierarchy.LAST
      INSERT INTO temp
      VALUES
        (biz_hierarchy(i),
         project_team(i),
         user_roles(i),
         username(i),
         sso(i));
    exit when cur%notfound;  
  end loop;

END;

see Asktom on large bulk collect for further info

share|improve this answer
add comment

There can be 2 issues here either you are running out of memory while using a bulk collect or while using a forAll ( try one at a time to figure out the issue). I recommend approach by Frank. it is really real way to work with bulk collects

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.