Forum OpenACS Q&A: Response to ORA-01000: maximum open cursors exceeded ??
- Bugs in the Oracle driver.
- Uncommitted transactions.
- Failing to close cursors in PL/SQL.
Option 3 (PL/SQL) seems the likely culprit to me... Probably exacerbated by any big, long-running transactions.
I did not actually determine where my open cursors were coming from, but I did review the static-pages, site-wide-search, and acs-content-repository ACS packages, and fixed un-closed cursors in the following files:
site-wide-search/sql/content-revision-sws.sql site-wide-search/sql/search-tables.sql acs-content-repository/sql/content-perms.sql acs-content-repository/sql/content-item.sql acs-content-repository/sql/content-schedule.sql acs-content-repository/sql/doc-package.sql
These are pretty trivial fixes, and I haven't really tested them at all yet on my system, but if somebody wants patches against ACS 4.2 holler and I'll get myself organized and provide them.
Also, it sure is annoying the way
only shows you the first 60 characters of the SQL statement. I tried,
but failed, to get the full text of the SQL from
v$sqltext.piece in the way you can with this query:
-- What are users currently doing? set linesize 180 select s.username, s.serial#, sql.sql_text from v$session s, v$sqltext sql where sql.address = s.sql_address and sql.hash_value = s.sql_hash_value --and upper(s.username) like 'USERNAME%' order by s.username ,s.sid ,s.serial# ,sql.piece ;