Forum OpenACS Q&A: Response to ORA-01000: maximum open cursors exceeded ??

Thanks, Brian. From that FAQ, it sounds like the only ways I could be getting cursors left open when they should not be are:
  1. Bugs in the Oracle driver.
  2. Uncommitted transactions.
  3. 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 v$open_cursor.sql_text 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 ;