Results 1 to 4 of 4
  1. #1
    Join Date
    May 2006
    Posts
    46

    Unanswered: ORA-04031 Shared Pool

    Hello,

    I am getting this error when trying to connect to Oracle instance.
    But after sometime i am getting the connection. When checked the params,
    Shared Pool is 160 Mb, Large Pool is 0. Should I have to increase the large pool size.

    Kindly help me to come out of this prob. I request to give me the solution considering this as an immediate request.

    Thanks

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    This means that there is fragmentation in the shared pool.
    What version of Oracle are you running ???

    There is a couple of things you can do depending on the version of Oracle

    Gregg

  3. #3
    Join Date
    May 2006
    Posts
    46

    Shared Pool Error

    Hello,

    I am working in ORacle 9i Release 2, once I shutdown and restart the error goes off. But I cannot do this every now and then.

    Pl. advice.

    Thanks

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You might try the undocumented parameter: _db_handles_cached = 0
    in your init file.

    You might also try pinning some of the SQL that is getting reloaded. Below is
    a little script to help find the sql that is reloaded and the size that it requires.



    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:

    COLUMN OWNER FORMAT A10
    COLUMN NAME FORMAT A32
    COLUMN sharable_mem FORMAT 9999999
    COLUMN LOADS FORMAT 9999999
    COLUMN EXECUTIONS FORMAT 9999999
    select substr(owner,1,10) OWNER ,substr(name,1,32) NAME , 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 ---
    COLUMN OWNER FORMAT A10
    COLUMN NAME FORMAT A32
    COLUMN sharable_mem FORMAT 9999999
    select substr(owner,1,10) OWNER ,substr(name,1,32) name, 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;

    HTH
    Gregg

Posting Permissions

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