ERR: ORA-04031 unable to allocate

From: Baker, Barbara <bbaker_at_denvernewspaperagency.com>
Date: Thu, 14 Feb 2002 16:29:47 -0800
Message-ID: <F001.0041077B.20020214160332@fatcity.com>

Oracle 8.0.5
Solaris 2.6

List:
One of our users is getting this error message running a query:

ERROR:
ORA-04031: unable to allocate 4194304 bytes of shared memory ("shared pool","unknown object","cursor work he","KKRH Hash Table")

The query is very simple (see below). Happens consistently. Error occurs after about 1 minute of execution. No other problems in the database (no errors in alert, etc.)

I'm seeing latch wait this for the sid involved:

SID EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3

----- ---------------- ---------- ----------- -------- -------- --------
------
  409 latch free       address     2147519876 number         59 tries
0
    1 pmon timer       duration           300                 0
0
    6 smon timer       sleep time         300 failed          0

and then I see this for the same sid in v$lock (resource is type+id1+id2, query is from Steve Adams' site)

RESOURCE NSID SID HOLDING WANTING SECONDS

-------------------- ----- ---- ------- ------- ----------
CU--1595636348-0       409  409       X                  0
RT-1-0                   4 LGWR       X                  0
TS-1-8388610             6 SMON      SX              55604

According to Anjo Kolk's description, a CU is a Bind enqueue. Does this mean I'm running out of open_cursors?? (And if so, why are no other processes affected?)

Anyone seen this one before?

Thanks for any help!
Barb

Here's the query:

select distinct

    v.sales_id,
    a.receiver,
    a.adno,
    a.unet,
    a.vno,
    a.enddate
from advdb.ad a,
     advdb.pub p,
     advdb.voluntary_reps v

where a.adno = p.adno

    and a.vno = p.vno
    and a.receiver = v.name
    and p.state = 'VAR'
    and p.vnoflag = 'Y'

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Baker, Barbara
INET: bbaker_at_denvernewspaperagency.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Feb 14 2002 - 18:29:47 CST