DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: ORA-04031: unable to allocate 8388608 bytes of shared memory ("large

  1. #1
    Join Date
    May 2003
    Posts
    16

    ORA-04031: unable to allocate 8388608 bytes of shared memory ("large

    Dear Users,

    When I run the query with contained nested tables , I am getting this error :

    ORA-04031: unable to allocate 8388608 bytes of shared memory ("large
    pool","unknown object","hash-join subh","QERHJ Bit vector")

    I tried to increase the sort area ,large pool and so on ,but I am encountering the same prob..


    I am pasting the .ora file content along with it , I am expectin a solution ...

    ##############################################################################
    # Copyright (c) 1991, 2001 by Oracle Corporation
    ##############################################################################

    ###########################################
    # Archive
    #######################################
    log_archive_dest_1='LOCATION=/home/oracle/OraHome1/oradata/ccms/archive'
    log_archive_format=%t_%s.dbf
    log_archive_start=true

    ###########################################
    # Cache and I/O
    ###########################################
    db_block_size=8192
    db_cache_size=67108864

    ###########################################
    # Cursors and Library Cache
    ###########################################
    open_cursors=300

    ###########################################
    # Diagnostics and Statistics
    ###########################################
    background_dump_dest=/home/oracle/OraHome1/admin/ccms/bdump
    core_dump_dest=/home/oracle/OraHome1/admin/ccms/cdump
    timed_statistics=TRUE
    user_dump_dest=/home/oracle/OraHome1/admin/ccms/udump

    ###########################################
    # Distributed, Replication and Snapshot
    ###########################################
    db_domain=""
    remote_login_passwordfile=EXCLUSIVE

    ###########################################
    # File Configuration
    ###########################################
    control_files=("/home/oracle/OraHome1/oradata/ccms/control01.ctl", "/home/oracle/OraHome1/oradata/ccms/control02.ctl", "/home/oracle/OraHome1/oradata/ccms/control03.ctl")

    ###########################################
    # MTS
    ###########################################
    dispatchers="(PROTOCOL=TCP)(SER=MODOSE)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)", "(protocol=TCP)"

    ###########################################
    # Miscellaneous
    ###########################################
    compatible=9.0.0
    db_name=ccms

    ###########################################
    # Network Registration
    ###########################################
    instance_name=ccms

    ###########################################
    # Pools
    ###########################################
    java_pool_size=147440512
    #large_pool_size=10485760
    shared_pool_size=53470912
    shared_pool_reserved_size=2000102
    #Above parameter added on 04/03/04
    #shared_pool_size = 200010212
    log_buffer=163840
    ###########################################
    # Processes and Sessions
    ###########################################
    processes=150


    # Redo Log and Recovery
    ###########################################
    fast_start_mttr_target=300

    ###########################################
    # Resource Manager
    ###########################################
    resource_manager_plan=SYSTEM_PLAN

    ###########################################
    # Sort, Hash Joins, Bitmap Indexes
    ###########################################
    #sort_area_size=536870912
    sort_area_size=5642880912

    ###########################################
    # System Managed Undo and Rollback Segments
    ###########################################
    undo_management=AUTO
    undo_tablespace=UNDOTBS

    event="04031 trace name context forever, level 10"




    Thanks and Regards

    Ramesh

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: ORA-04031: unable to allocate 8388608 bytes of shared memory ("large

    So do you have a large pool or not?

    In your init.ora you have:

    #large_pool_size=10485760

    Run:

    show parameter large

    and

    select * from v$version; and post here the output
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Nov 2001
    Location
    Planet Earth
    Posts
    116
    the key is shared_pool_size and restart the DB once you change it unlesss you're using spfile.

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by zainalj
    the key is shared_pool_size and restart the DB once you change it unlesss you're using spfile.
    The error message points rather to the large pool than the shared pool.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Nov 2001
    Location
    Planet Earth
    Posts
    116
    than, increase large_pool_size.. once changed check back either it take effect or not
    SQL> show parameter large_pool_size

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    which is exactly what Julian said .....

  7. #7
    Join Date
    May 2003
    Posts
    16
    Thank you julian and zainalj for your valuable feedback , I ran what all you said me to ,and I am pasting the output over there...

    I uncommented the large_pool_size parameter .. I restarted the database , but still I am getting the error :

    what your feedback soon....


    SQL> show parameter large_pool_size

    unknown SHOW option "parameter"
    unknown SHOW option "large"



    SQL> select * from v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
    PL/SQL Release 9.0.1.0.0 - Production
    CORE 9.0.1.0.0 Production
    TNS for Linux: Version 9.0.1.0.0 - Production
    NLSRTL Version 9.0.1.0.0 - Production

    Regards
    Ramesh

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: ORA-04031: unable to allocate 8388608 bytes of shared memory ("large

    Originally posted by rameshbabumande

    ###########################################
    # MTS
    ###########################################
    dispatchers="(PROTOCOL=TCP)(SER=MODOSE)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)", "(protocol=TCP)"

    ###########################################
    # Sort, Hash Joins, Bitmap Indexes
    ###########################################
    #sort_area_size=536870912
    sort_area_size=5642880912
    Why are u killing ur SGA? ..

    Make it dedicated type, u will not see the error ..

    Note :

    * Is sort area size of 5GB really necessary ?
    * Is Shared Pool Reserved Space of 20M necessary ?

    Abhay.
    Last edited by abhaysk; 03-08-2004 at 05:57 AM.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    May 2003
    Posts
    16
    I have only 1GB space , since

    ORA-04031: unable to allocate 8388608 bytes of shared memory ("large
    pool","unknown object","hash-join subh","QERHJ Bit vector")

    is coming , I increased the sort area , how can I make it dedicated type . Can u give me the instruction in detail..so that I can change in my .ora file ...

    Waiting for your help...

    Ramesh

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Code:
    ###########################################
    # MTS
    ###########################################
    
    # Comment below to go for dedicated server type.
    
    #dispatchers="(PROTOCOL=TCP)(SER=MODOSE)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.GiopServer)", "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)", "(protocol=TCP)"
    
    ###########################################
    # Sort, Hash Joins, Bitmap Indexes
    ###########################################
    
    # 50M of sort area is enough i guess
    
    sort_area_size=52428800
    
    # Also is shared pool reserved required 
    
    shared_pool_reserved_size=2000102
    Please see Slimdave's Signature..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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