| ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #264043] |
Fri, 31 August 2007 09:59  |
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
|
Junior Member |
|
|
Hi,
i am using Oracle9i database where the database integrated with JAVA front end application. now i am getting "ORA-01000: maximum open cursors exceeded " error while open the java front end screen.
How can i resovle the this problem in my database?
Any one can help me.
Regards,
Siva.P
Bangalore
|
|
|
|
|
|
| Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #264051 is a reply to message #264043] |
Fri, 31 August 2007 10:20  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Hello,
Whenever you open a Java ResultSet, Oracle openes a cursor, so close the ResultSet after you are done with it.
You can check the maximum number of cursors a session is allowed to open with a dba user :
SELECT * FROM v$parameter WHERE NAME = 'open_cursors';
and check the cursors that are currently open with :
SELECT * FROM v$open_cursor
|
|
|
|
|
|
| Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266548 is a reply to message #264043] |
Tue, 11 September 2007 02:10 |
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
|
Junior Member |
|
|
Hi Sanjay,
Thanks for your reply, i tried to increase the open cursor value in initora File, The Database designer set the open cursor value is 1000 for my database. But my database has reached the maximum value(1000).So, we are not able to increase the open cursor value against the default value.
Can you tell me the other solution for this issue...? it will be help full for me.
Thanks in Advance...
Thanks,
Siva.P
Bangalore
India.
[Updated on: Tue, 11 September 2007 02:12] Report message to a moderator |
|
|
|
|
|
|
|
| Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266695 is a reply to message #266687] |
Tue, 11 September 2007 07:41 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
There are no other options.
Look at the open cursors and find the ones that are not closed right, and close them in the application code.
The select
SELECT SQL_TEXT, Count(*) FROM v$open_cursor
GROUP BY sql_text
ORDER BY Count(*) DESC;
Should give you the open cursors by SQL in descending order, so you should be able to find the cursor(s) that are the most problematic.
|
|
|
|
| Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266725 is a reply to message #266695] |
Tue, 11 September 2007 08:54 |
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
|
Junior Member |
|
|
Hi Thomas G,
i ran the query which you sent, its giving the Sql query and no of open cursor at the moment.
for example SQL Text from the sql query output
SQL_TEXT:
UPDATE FCMT_TRADESPHERE_GW20 SET import_status = 'C',
This is the Query in my Database procedure
UPDATE FCMT_TRADESPHERE_GW20 SET import_status = 'C', update_date = SYSDATE, updated_by = lv_updatedby WHERE gw20_trans_id = i.gw20_trans_id;
Where i.gw20_trans_id is the cursor variable which is declared in begin of the procedure, it is the main cursor using all PL/SQL block in the procedure
So How can i fix the issue...?
See the oupt put file as an attachement.
Thanks,
Siva.P
Bangalore
India.
|
|
|
|
| Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266751 is a reply to message #266725] |
Tue, 11 September 2007 09:44 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
If the procedure is called from Java close the prepared statement after it is done. ( Preferably in an "finally" block after the try/catch that does execute the statement )
But the cursor is only open 13 times, which might be OK if about that number of people are working with that part of the application at that time.
How many cursors are open at the moment per session? Are you sure "open_cursors" is set to 1000?
|
|
|
|
| Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266758 is a reply to message #266751] |
Tue, 11 September 2007 10:03 |
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
|
Junior Member |
|
|
Yes Thomas G,
In my application number of people are working in JAVA side and DB side also, My Database has set the open cursor value is 1000
open_cursors integer 1000
open_links integer 4
open_links_per_instance integer 4
read_only_open_delayed boolean FALSE
session_max_open_files integer 10
select * from v$sysstat where name = 'opened cursors current';
see the attached file for current open cursor(its not constant value...) its varying time by time depends upon the user.
Thanks,
Siva.P
Bangalore.
|
|
|
|
|
|
|
|
| Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266771 is a reply to message #266769] |
Tue, 11 September 2007 10:48 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
OK, ~50 open cursors for a session look pretty normal.
So I guess there is some specific place in the application where a cursor is opened (maybe even multiple times inside a loop) and not closed. You will have to find that specific place.
You can either run the query that lists the open cursors per SID now and then, and when the cursor count rises abnormally investigate the open cursors for that SID.
Or you wait until the error happens again, and investigate that specific session then.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
