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 have tried the following:
ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM FLUSH SHARED_POOL; ALTER SYSTEM SET cursor_sharing = 'SIMILAR' SCOPE=BOTH;
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.