Home » Other » General » open cursors recurring errors (Oracle 10g R2, 10.2.0.4.0, Solaris 10 )
open cursors recurring errors [message #467288] Fri, 23 July 2010 03:36 Go to next message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member
We've been encountering these errors for the past 3 days now. The dB is newly created (just about 2 weeks ago) and the "ORA-01000: maximum open cursors exceeded" error keeps bugging us. Currently, the dB and the APPS have not been connected due to network problem.

The default open_cursors value is set to 300 during manual dB creation. I adjusted the value of the open_cursors twice, just this Wednesday it reached to 378 so I changed it to 2,000 and now it reach to 2,089, re:
SQL> show parameter open_curs

NAME TYPE VALUE
------------------------------------ ----------- --------------
open_cursors integer 2000

SQL> select count(*) from v$open_cursor;

COUNT(*)
----------
2089

What's happening here?

Thanks for your help.

Report message to a moderator

Re: open cursors recurring errors [message #467291 is a reply to message #467288] Fri, 23 July 2010 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The main reason is: the application does not close the cursors it uses.
So the main solution is: fix the application code to close the cursor it opens.

Regards
Michel

Report message to a moderator

Re: open cursors recurring errors [message #467294 is a reply to message #467288] Fri, 23 July 2010 03:43 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
How can you have more cursor open then open_curs Specified?

Can you please run following query and copy/Paste the results

   select s.username, max(a.value)
    from v$sesstat a, v$statname b, v$session s
    where a.statistic# = b.statistic#
    and s.sid (+)= a.sid
    and b.name = 'opened cursors current'
   group by s.username
/

Report message to a moderator

Re: open cursors recurring errors [message #467298 is a reply to message #467294] Fri, 23 July 2010 04:02 Go to previous messageGo to next message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member
Thanks for your prompt respond, as per your request here it is:

SQL> select s.username, max(a.value)
2 from v$sesstat a, v$statname b, v$session s
3 where a.statistic# = b.statistic#
4 and s.sid (+)= a.sid
5 and b.name = 'opened cursors current'
6 group by s.username;

USERNAME MAX(A.VALUE)
------------------------------ ------------
2000
SYS 1

Report message to a moderator

Re: open cursors recurring errors [message #467301 is a reply to message #467298] Fri, 23 July 2010 04:09 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
1/ So there are NO 2089 Open Cursors.

2/ As soon as your application Hit 2000 Open cursors you got the "ORA-01000: maximum open cursors exceeded"

3/ Now as Michel Said Please fix the Application code to close teh cursors when processing is done.

Report message to a moderator

Re: open cursors recurring errors [message #467303 is a reply to message #467301] Fri, 23 July 2010 04:15 Go to previous messageGo to next message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member
The APPS and dB have not been connected.

They're currently fixing those connections.

Report message to a moderator

Re: open cursors recurring errors [message #467304 is a reply to message #467303] Fri, 23 July 2010 04:19 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
Ok. So once db is available fix the application cursors.

Report message to a moderator

Re: open cursors recurring errors [message #467305 is a reply to message #467304] Fri, 23 July 2010 04:25 Go to previous messageGo to next message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member
dB is running well...
The problem is that the Application Server (Apache) cannot communicate with the dB server.

I'm just wondering why it's happenning.

The dB and the applications have no interface yet (connections) but the open cursors keeps on adding.

Thanks.

Report message to a moderator

Re: open cursors recurring errors [message #467307 is a reply to message #467305] Fri, 23 July 2010 04:34 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
reym21 wrote on Fri, 23 July 2010 04:25
dB is running well...


Ok.

reym21 wrote on Fri, 23 July 2010 04:25

The problem is that the Application Server (Apache) cannot communicate with the dB server.

I'm just wondering why it's happenning.


Check with your System Admin.

reym21 wrote on Fri, 23 July 2010 04:25

The dB and the applications have no interface yet (connections) but the open cursors keeps on adding.

Thanks.


Check with Sys Admin

Report message to a moderator

Re: open cursors recurring errors [message #467320 is a reply to message #467307] Fri, 23 July 2010 05:37 Go to previous messageGo to next message
cookiemonster
Messages: 13958
Registered: September 2008
Location: Rainy Manchester
Senior Member
Cursors don't open by magic. Something is running on the db to open them.
Check v$session to see what's connected to the DB.

Report message to a moderator

Re: open cursors recurring errors [message #467322 is a reply to message #467320] Fri, 23 July 2010 05:53 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
Hi - you didn't format the results of that query you ran, so it is hard to read; but it looks as though the session with 2000 open cursors is a background process, with no username?
Can you run this instead, to see which process it is:
select v.value, s.sid, s.username, s.program 
from v$sesstat v,v$session s
where v.statistic#=4 and s.sid=v.sid
order by 1
/

I remember a couple of bugs with checkpoint process opening zillions of cursors, you might want to look it up on Metalink.

Report message to a moderator

Re: open cursors recurring errors [message #467553 is a reply to message #467322] Sun, 25 July 2010 22:32 Go to previous messageGo to next message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member
Good day.
Running the code, the only user registered here is SYS.
     VALUE      SID USERNAME         PROGRAM
---------- -------- ---------------- ----------------------------
         0       27                  oracle@bir-dbs (MMNL)
         0      115                  oracle@bir-dbs (MMAN)
         0       29                  oracle@bir-dbs (DBW3)
         0       51                  oracle@bir-dbs (q001)
         0       54                  oracle@bir-dbs (MMON)
         0       55                  oracle@bir-dbs (CKPT)
         0       56                  oracle@bir-dbs (DBW2)
         0       57                  oracle@bir-dbs (DBW0)
         0       58                  oracle@bir-dbs (PSP0)
         0       79 SYS              sqlplus@bir-dbs (TNS V1-V3)
         0       84                  oracle@bir-dbs (q000)
         0       85                  oracle@bir-dbs (CJQ0)
         0       86                  oracle@bir-dbs (LGWR)
         0       87                  oracle@bir-dbs (PMON)
         0      112                  oracle@bir-dbs (QMNC)
         0      113                  oracle@bir-dbs (SMON)
         0      114                  oracle@bir-dbs (DBW1)
         0       28                  oracle@bir-dbs (RECO)

18 rows selected.

[Mod-Edit: Frank added code-tags to improve readability]

[Updated on: Mon, 26 July 2010 00:35] by Moderator

Report message to a moderator

Re: open cursors recurring errors [message #467555 is a reply to message #467553] Sun, 25 July 2010 23:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
select username, machine, count(*)
from v$session
where sid in (select sid from v$open_cursor)
group by username, machine
order by 3
/

Report message to a moderator

Re: open cursors recurring errors [message #470998 is a reply to message #467555] Wed, 11 August 2010 22:46 Go to previous messageGo to next message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member
Running your code, this is the result:

USERNAME MACHINE COUNT(*)
------------------------------ ---------------------------------------------------------------- ----------
SYS bir-dbs 1
bir-dbs 3

Btw, this 'open cursor error' is still recurring currently.

Report message to a moderator

Re: open cursors recurring errors [message #470999 is a reply to message #470998] Wed, 11 August 2010 22:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Btw, this 'open cursor error' is still recurring currently.
Application still contains flawed implementation.

Report message to a moderator

Re: open cursors recurring errors [message #471005 is a reply to message #470999] Wed, 11 August 2010 23:52 Go to previous messageGo to next message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member
What do you mean of 'flawed implementation'? It's been 2 months since I created & open-up the dB, but 'til now, they (Apps Team) can't figure it out how to connect the Oracle db using PHP/Apache... there's no connection yet (interface) between db & Application, but the open cursor is the main culprit in the alertlogs.

Report message to a moderator

Re: open cursors recurring errors [message #471006 is a reply to message #471005] Wed, 11 August 2010 23:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Oracle responds to client requests.
Simply put, either you or Oracle is mistaken.
Since no other site on the planet is reporting similar behavior, I suggest the problem is localized to your site.
If you are certain, Oracle is mis-reporting reality (Oracle bug) then submit Service Request with MOS.

Report message to a moderator

Re: open cursors recurring errors [message #471008 is a reply to message #471006] Thu, 12 August 2010 00:03 Go to previous message
reym21
Messages: 241
Registered: February 2010
Location: Philippines
Senior Member
Thanks for your advice.

Report message to a moderator

Previous Topic: C# .NET with oracle packages
Next Topic: java.sql.SQLException: ORA-01001: invalid cursor:ORA-02089: COMMIT is not allowed in a subordinate s
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Nov 22 16:07:55 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.