ora-04031 error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ora-04031 error

  1. #1
    Join Date
    Dec 2001
    Posts
    120

    ora-04031 error

    hi,

    while running the following query through a jdbc call i am getting an ora-04031 error. However this query runs fine when i run it on sql prompt or throufg TOAD. There are many such similar queries and all of them except this one are running fine through the jdbc calls made from our java based application. I am giving the exact error message at the bottom of my mail for better understanding of the problem. I don't understand why it requires extra space in large_pool. At present large_pool_size is 1M and shared_pool_size is 175M & DB_CACHE_SIZE IS 150M. Pls give your valued inputs...

    thanks

    Parijat Paul

    ======================================================================
    SELECT
    *
    FROM
    (SELECT RS0.objectid,
    updatesequence,
    araccount,
    invoicedate,
    amount,
    invoicestatusref,
    draftcommandsref,
    draftinvreferencenum,
    creationdate ,
    updatedate FROM ( select a.objectid,
    b.objectname araccount ,
    a.creationdate,
    a.updatedate,
    a.updatesequence from transaction_objects a ,
    names_objects b
    WHERE
    a.objecttypeid = 20128 and
    a.forobjecttypeidlevel1 = b.objecttypeid and
    a.forobjectidlevel1 = b.objectid and
    (( b.objectname < 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ' ) or ( b.objectname = 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ' and
    a.objectid >= 0 )) ORDER BY araccount DESC, objectid ASC )RS0, ( select objectid, transactiondate invoicedate from transaction_objects a where objecttypeid = 20128 )RS1, ( select objectid, value amount from transaction_objects a where objecttypeid = 20128 )RS2, ( select a.objectid, b.objectname invoicestatusref from transaction_objects a , names_objects b where a.objecttypeid = 20128 and
    a.statusobjecttypeid1 = b.objecttypeid and
    a.statusobjectid1 = b.objectid )RS3, ( select a.objectid, b.objectname draftcommandsref from transaction_objects a , names_objects b where a.objecttypeid = 20128 and
    a.statusobjecttypeid2 = b.objecttypeid and
    a.statusobjectid2 = b.objectid )RS4, ( ( select propertyid,objectid,valueproperty draftinvreferencenum from properties_text_unique a where objecttypeid = 20128 and
    propertyid = 712 ) )RS5 WHERE RS0.objectid = RS1.objectid AND
    RS1.objectid = RS2.objectid AND
    RS2.objectid = RS3.objectid AND
    RS3.objectid = RS4.objectid AND
    RS4.objectid = RS5.objectid and
    RS0.objectid > 0 ) ORDER BY araccount DESC, objectid ASC
    ======================================================================

    ------------------------THE ERROR MESSAGE----------------------------

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

  2. #2
    Join Date
    Dec 2000
    Location
    Brazil / São Paulo
    Posts
    97
    Why applications JAVA need extra memory to twirl. How it is JAVA_POOL_SIZE and LARGE_POOL_SIZE?
    Márcio de Souza Almeida
    DBA Oracle / SQLServer / PostgreSQL
    Rua Cupa, 139 Apto 85 A
    Penha - São Paulo - SP
    03640-000 - Brasil
    http://www.directory.com.br

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530

    Re: ora-04031 error

    Hi,
    Increase the LARGE_POOL_SIZE and then try it out.This error is occuring because the shared pool has become fragmented and there is no contignous memory available for ur query to execute.

    Regards,
    Rohit Nirkhe,Oracle/Apps DBA,OCP 8i
    oracle-support@indiatimes.com
    Thanks and Regards
    Rohit S Nirkhe
    rohitsnirkhe@rediffmail.com

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