DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: ORA-04031 - Whats happening.

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    ORA-04031 - Whats happening.

    I've created several 8.1.5 databases (on WIn NT4) using the Creation Wizard but on two of them I occasionally get this message:


    Error accessing package DBMS_APPLICATION_INFO
    ERROR:
    ORA-04031: unable to allocate 4096 bytes of shared memory ("shared pool","BEGIN
    DBMS_APPLICATION_INFO....","PL/SQL MPCODE","BAMIMA: Bam Buffer")

    What could be different in these databases to cause this?

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Probably your shared pool became too fragmented to find enough contiguous space to load DBMS_APPLICATION_INFO package. Few options that you have to avoid this:

    - Use bind variables in your applications (if they are not used allready)
    - Pin larger packages (the ones you actaully use) immediately uppon database startup
    - Increase your shared pool (if it's not unreasonably large allready)
    - ALTER SYSTEM FLUSH SHARED_POOL; when you encounter ORA-4031 (this is more of a temporary workaround than a real sollution)
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Solution

    Hi,

    I faced simillar error in the Past....Bamima Buffer...

    Its related to Shared Pool Size....

    We had a Package of file size 385 KB.We spilt that in to several Smaller Packages(7 or 8 rather as one Package to compile) of the file size as per oracle standard not exceeding 50 KB.

    There is Solution 2 ways either increasing the Shared Pool Size or Properly reorganizing the Package affecting the Shared Pool taking the total resource to compile as one Package.

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    how about increase shared pool reserved size?

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    There's also bugs in versions before 8.1.7.2 that can cause this error...
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    But I got these errors when starting up SQL*Plus. No application or packages were executing.

    Is it a SHARED_POOL issue then? Which begs me to ask: How do you properly estimate the size of the shared pool?

    (And how can I tell if an application uses bind variables?)

  7. #7
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658
    Hi,

    Since you say some of the other db created simillarly didnt have problem...

    1)Can you kindly have check whether all have Same Patch Sets Installed

    Since as you have told this Problem is arising when you login this issue may not be related with Increasing shared pool Size at all.

    Also read this Detailed document in Metalink with seacrh word 146599.1 ....Throws more high light on the same error...

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  8. #8
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by JMac
    But I got these errors when starting up SQL*Plus. No application or packages were executing.

    Is it a SHARED_POOL issue then? Which begs me to ask: How do you properly estimate the size of the shared pool?

    (And how can I tell if an application uses bind variables?)
    Your database configured for MTS???
    -nagarjuna

  9. #9
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Nope - all are at same patchset.
    And Nope - No MTS.

    Will check out the metalink doc, Pando, thanks.

    Can I repeat the question about checking if SQL uses bind variables?

  10. #10
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    Originally posted by JMac
    Nope - all are at same patchset.
    And Nope - No MTS.

    Will check out the metalink doc, Pando, thanks.

    Can I repeat the question about checking if SQL uses bind variables?
    Then it is a shared_pool issue. Your shared pool is fragmented very much. Try reducing shared_pool as much as possible, but make sure that your library cache hit ratio does not fall below the required level (It depends on your SLA). It means, you need to have down time to change shared_pool parameter.

    About bind variables.. Using bind variables and cursor_sharing=force will increase the probability of shared SQLs. That is, SQLs are not reloaded again. They are found in memory.
    -nagarjuna

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