Thanks, Brian. From that FAQ, it sounds like the only ways I could be
getting cursors left open when they should not be are:
- 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 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 ;