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")
---------------------------------------------------------------------

Why applications JAVA need extra memory to twirl. How it is JAVA_POOL_SIZE and LARGE_POOL_SIZE?

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