ORA-04031: unable to allocate 4032 bytes of shared memory
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: ORA-04031: unable to allocate 4032 bytes of shared memory

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    ORA-04031: unable to allocate 4032 bytes of shared memory

    hi,
    I tried to export my instance oracle:

    EXP SYSTEM/MANAGER@MYDB FULL=Y FILE=C:\MYDB\EXP.DMP LOG=C:\MYDB\ERROR_EXP.LOG


    but I get this error:

    xporting table WYTYPE 0 rows exported
    . exporting referential integrity constraints
    . exporting synonyms
    EXP-00008: ORACLE error 4031 encountered
    ORA-04031: unable to allocate 4032 bytes of shared memory ("large pool","unknown object","joxu heap init","ioc_allocate_pal")
    EXP-00000: Export terminated unsuccessfully


    How can I resolve this problem??

    Thanks in advance!

    Raf

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    Well there is this http://metalink.oracle.com/metalink/...l2_gui.startup
    on metalink.

    Can you view it?

    MH
    I remember when this place was cool.

  3. #3
    Join Date
    Jul 2002
    Posts
    228
    Originally posted by Mr.Hanky
    Well there is this http://metalink.oracle.com/metalink/...l2_gui.startup
    on metalink.

    Can you view it?

    MH
    I view metalink site, but what id number has my specific problem??

  4. #4
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    Your SGA has become fragmented ...
    Your will have to restart the database to clear up the fragmentation.

    Your should attempt to pin some of the objects in memory that
    are getting reloaded multiple times... See script below to view
    the reloads and sizes of objects...

    You also may need to look at resizing your SGA

    Another aid is the following undocumented init parameter in 8.1.7

    #### Work around for BAMIMA Buffer (4031) errors ####
    _db_handles_cached = 0


    To determine what large PL/SQL objects are currently loaded in the shared pool
    and are not marked 'kept' and therefore may cause a problem, execute the following:


    select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
    from v$db_object_cache
    where sharable_mem > 100
    and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
    or type = 'PROCEDURE')
    and kept = 'NO'
    order by owner,loads desc;


    --- Objects that have been reloaded ---

    select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
    from v$db_object_cache
    where loads > 1
    and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
    or type = 'PROCEDURE')
    and kept = 'NO'
    order by owner,loads desc;

    Hope this helps
    Gregg

  5. #5
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    In the metalink, you will find a lot of such thread. I picked up from there..
    Solution:

    Well, still, shared pool is not big enough.

    ORA-04031 unable to allocate string bytes of shared memory ("string","string","string","string")

    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 initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.
    There is always a better way to do the things.

  6. #6
    Join Date
    Jan 2001
    Posts
    3,131
    I remember when this place was cool.

  7. #7
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    If it seems unlikely that your SGA is undersized, you might be experiencing a bug from the early releases of 8.1.7

    Patching to 8.1.7.4 might be a solution.
    David Knight
    OCP DBA 8i, 9i, 10g

  8. #8
    Join Date
    Jul 2002
    Posts
    228
    I've this hardware and software:

    Pentium 4 1,80 Ghz
    521 Mb RAM
    O.S. Windows XP
    Oracle version: 9.0.1.0.1

    Actually the parameters are:

    resource_limit................ FALSE
    license_max_sessions............. 0
    license_sessions_warning.............. 0
    cpu_count....................... 1
    shared_pool_size................ 58720256
    sga_max_size.................... 126644216
    shared_pool_reserved_size....... 2306867
    large_pool_size................. 0
    java_pool_size.................. 33554432
    java_soft_sessionspace_limit..... 0
    java_max_sessionspace_size...... 0
    lock_sga........................ FALSE
    db_cache_size................... 33554432
    log_buffer...................... 524288
    transactions.................... 187
    undo_retention.................. 10800
    create_bitmap_area_size......... 8388608
    bitmap_merge_area_size.......... 1048576
    parallel_execution_message_size.. 2148
    hash_join_enabled............... TRUE
    hash_area_size................... 1048576
    max_dump_file_size.............. UNLIMITED
    oracle_trace_collection_size........ 5242880
    object_cache_optimal_size.......... 102400
    object_cache_max_size_percent....... 10
    sort_area_size.................. 1048576
    sort_area_retained_size................ 0
    optimizer_max_permutations.......... 2000
    optimizer_index_cost_adj.......... 100


    These parameters are correct?
    How can I undestanding which are the parameters correct with this hardware??

    Raf

  9. #9
    Join Date
    Oct 2000
    Posts
    467
    Originally posted by gbrabham
    Your SGA has become fragmented ...
    Your will have to restart the database to clear up the fragmentation.

    Not necessary to restart - flushing the shared pool will do the trick.
    Vinit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width