Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: 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
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    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

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    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

  4. #4
    Join Date
    Aug 2003
    Posts
    5

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

    Originally posted by raf
    ORA-04031: unable to allocate 4032 bytes of shared memory ("large pool","unknown object","joxu heap init","ioc_allocate_pal")
    Its asking for large pool, and its sized at 0, not sure why `exp` would need it ... try 4Mb for starters?

    We usually add 'buffer=512000' for most exports, and stay away from 'direct=Y' as well.

  5. #5
    Join Date
    Aug 2003
    Posts
    3
    You can increase java_pool_size parameter and execute the following command while running the exp :
    SQL> alter system flush shared_pool;

    Execute the command above more than once while the export is running.

Posting Permissions

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