Max open_cursors exceeded

[Former Member]

Hi all,

I am running a consistency check tool, which then give me the ora01000 alert - maximum open_cursors exceeded.

I've tried to increase the parameter value, but seems to be futile.

My questions as below

1. When executing this:

alter system set open_cursors=500 scope=both

is there then a requirement to restart the whole instance including the database?

2. How can we check why there are so many open_cursors and how to "close" them?

Any advice is appreciated.

[former_member264034 · Accepted solution]

Hi,

I think you can try to increase the value of prameter OPEN_CURSORS

as mentioned in the note 398927.

As you may already know, "ORA-01000" can be caused when cursors are not

closed in time after an SQL has been executed. I guess it's just because the

consistency check reads a lot of ID objects this time.

Regards,

Aidan

[Former Member]

Anyway, I would like to gain more knowledge on this.

When I'm running this :

select user_name, status, osuser, machine, a.sql_text

from v$session b,

v$open_cursor a

where a.sid = b.sid

/

I'm getting a lot of "INACTIVE"... what does it mean by inactive? Meaning it can be removed?

USER_NAME STATUS OSUSER

-

---

-

---

-

---

MACHINE

-

---

SQL_TEXT

-

---

SAPSR3DB INACTIVE <sid>adm

<host>

SELECT * FROM "UME_ACL_PERM_MEM" WHERE "OWNERID" = :1 AND "P

Edited by: e.castro on Aug 23, 2011 2:47 PM

[mohsin_m3]

Hi

I am also getting same Error ...can u pls give me exact steps ,which u have executed .

How have u killed unused sessions or nay other action?

Regards

Mohsin

[Former Member]

Hi Mohsin,

I wish I would be able to help, but I just terminated some of the session created by my colleagues, and restarted the PCD tools, and it just work miraculously.

I have yet get any useful input from SAP, probably you can also try to raise an OSS message.

[mohsin_m3]

Hi,

Already I have raised OSS message ..SAP suggest to cahnge open_cursor value and commit .....but no luck

Can you please help me how to kill unused sessions ....

Thanks

Mohsin

[stefan_koehler]

Hello Castro,

is there then a requirement to restart the whole instance including the database?

Nope.

2. How can we check why there are so many open_cursors and how to "close" them?

Check the view V$OPEN_CURSOR

http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/dynviews_2083.htm#i1410256

Is this a consistency check tool provided by SAP? I guess not .. so you got some "bug" in your application.

Regards

Stefan

[Former Member]

Hi Stefan,

Unfortunately, it is the PCD Consistency Check tool provided by SAP. I have yet receive any reply from SAP regarding this.

[Former Member]

Hi Castro,

I imported and executed this tool previously. It is generating many cursors, on the database. When I take a look at in detail, I found out that it is related by number nodes in the PCD.

Best regards,

Orkun Gedik

[Former Member]

Hi Orkun,

Would you tell me what was your next action?

Did you then remove something from the PCD content via the PCD browser?

Hi Aidan,

Will check on the SAP Note as well. Thanks. 😃

Edited by: e.castro on Aug 22, 2011 11:19 PM

[Former Member]

Hi Castro,

Before I execute this tool, there were some inconsistencies on over the folders on PCd. After execute this tool it has been fixed the structural problems. Then, I was able to drop problematic folders and created them from the scratch.

Best regards,

Orkun Gedik

[Former Member]

I would want to fix the inconsistencies as well, the problem is that whenever I run check with the tools, it came out with the alert.

[Former Member]

Hi all,

Thanks for the input. The check tool just work magically today.

What I did was just killing some unused session.