Ask the Expert

Getting ORA-01000 error when all statements and result sets are closed

I am getting ORA-01000 error, although I have properly closed all the statements and result sets. Also OPEN_CURSOR is set to 500. I am using Oracle 8.1.7. Please help!

    Requires Free Membership to View

Check your code again; you may still have cursors and/or result sets that are not being closed. You can use this query to view the number of open cursors for a given user ("SCOTT" in this example):
select o.sid, osuser, machine, count(*) num_curs 
from v$open_cursor o, v$session s 
where s.username = 'SCOTT' and o.sid=s.sid 
group by o.sid, osuser, machine 
order by num_curs desc;
You can also see the SQL associated with open cursors for a given session (identified by session ID [SID] from the above query results):
select q.sql_text 
from v$open_cursor o, v$sql q 
where q.hash_value=o.hash_value and o.sid = <sid>;
If you are able to verify that no cursors or result sets are being left open, you must increase the value of OPEN_CURSORS in your initialization parameter file, then shut down and start up the database instance for the change to take effect.

This was first published in January 2006

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: