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.
Related Q&A from Paul Baumgartel
I am updating 80% of the rows in a 30-column (6 VC  and a blob) table. I have the STD 10% pctfree set up. I am getting fair throughput,...continue reading
We are starting to upgrade to Oracle 10g and support a PeopleSoft application on AIX5L. Which initialization parameters do you consider important for...continue reading
Can I install the Oracle 8i client and the Oracle 10g client on Windows XP?continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.