• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 765
  • Last Modified:

ORA-01000 error

Hi,

I am using Oracle 8i and I am using Oracle JDBC thin driver for the database connection.

After few queries to the database. I am getting ORA-01000 error(Maximum cursors open error).

at oracle.jdbc.ttc7.TTIoer.<processError>
at oracle.jdbc.ttc7.Oopen.<receive>
at oracle.jdbc.ttc7.TTC7protocol.<open>
at oracle.jdbc.driver.OracleStatement.<init>
at oracle.jdbc.driver.OraclePreparedStatement.<init>
ORA-01000: maximum open cursors exceeded

SQLCode : 72000

Can anyone suggest me some workaround.
Pls. cc your reply to swami@transparity.com

Cheers swamy

0
sswami
Asked:
sswami
1 Solution
 
sudhi022299Commented:
the program has opened more than the allotted number of cursors per user.
First is check whether the cursors are being closed in the program

Second in the init file of the database increase the value of the parameter OPEN_CURSORS , shutdown and restart your database.

Regards,
Sudhi.
0
 
HaukeCommented:
You must call the method 'close' on every statement object you create.

PreparedStatement stmt= db.prepareStatement( "select .."

ResultSet rs= stmt.executeQuery();

while (rs.next())
{
  ..
}

rs.close();

stmt.close();

If in this case any method call between 'prepareStatement' and 'close' raises an exception then the program doesn't reach the 'close' method and the statement handle is lost!

The default value of maximum open cursors is 50. This should be enough for a program without a handle leak.
0
 
vemulayugandharCommented:
Hi
First tune the private SQL areas,to tune this you should identify how many parse calls your application is making.

You run TKPROF or SQL_TRACE=true to see how many times parsing occurs on each and every sql statement.
for eg:if count statistic of PARSE is > than count statistic of EXECUTE,then try to increase the open_cursors or reduce parse calls with ORACLE PRECOMPILERS,OCI.

           count
PARSE      10
EXECUTE    2
FETCH
0
 
sswamiAuthor Commented:
ThanX a lot
Cheers swamy
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now