Barbara:
You need to:
1) Have the user log out, log back on, and try again. If that
fails ...
2) Bounce the instance. Before you restart, you should ...
3) Increase the size of your shared pool, and maybe ...
4) Increase the open_cursors parameter value. The Open_Cursors
parameter is a *session* parameter, not an *instance*
parameter, so this user may really be running out of cursors.
The message seems to indicate that it's trying to allocate
space for a hash join, and it cannot. You might check the
value of hash_area_size, and maybe increase that, too.
HTH,
Mike
-----Original Message-----
Sent: Thursday, February 14, 2002 4:04 PM
To: Multiple recipients of list ORACLE-L
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
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: Vergara, Michael (TEM)
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
