Q

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!

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!
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

Dig deeper on Oracle error messages

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close