When developing complex applications, it is often neccessary to see the total number of opened cursors. With this simple method, you can find the total number of opened cursors for any particular user.
Suppose there is a user called 'TESTUSER'. For this user we can find the total number of opened cursor as follows:
select a.osuser, count(1) from v$session a, V$OPEN_CURSOR b where a.sid = b.sid and b.user_name = 'TESTUSER' group by a.osuser order by 2
Jason V. writes: This query doesn't always work. It will report inaccurate counts at times. Specifically, you can get an ORA-01000 error when the count returned by his query will be less than the allowed OPEN_CURSORS setting in init.ora (which doesn't make sense). It has to do with the close_cached_open_cursors (Oracle 7.3) or _close_cached_open_cursors (Oracle 8.x) setting in init.ora. Having spent hundreds of hours on this issue, I thought it might be of value to pass it along.
CREATE OR REPLACE FUNCTION f_get_open_cursor_count(s_username_in IN VARCHAR2 := Null) RETURN NUMBER IS CURSOR cur_open_cursor_count(s_username_in IN VARCHAR2) IS SELECT MAX(V$SESSTAT.VALUE) FROM V$SESSTAT, V$SESSION WHERE V$SESSION.SID = V$SESSTAT.SID AND V$SESSTAT.STATISTIC# = 3 AND V$SESSION.USERNAME = s_username_in AND V$SESSION.STATUS = 'ACTIVE'; ln_cursor_count NUMBER := 0; ls_user_name VARCHAR2(32) := Null; BEGIN ls_user_name := NVL(s_username_in,USER); OPEN cur_open_cursor_count(ls_user_name); FETCH cur_open_cursor_count INTO ln_cursor_count; CLOSE cur_open_cursor_count; RETURN ln_cursor_count; EXCEPTION WHEN OTHERS THEN IF (cur_open_cursor_count%ISOPEN) THEN CLOSE cur_open_cursor_count; END IF; RETURN -1; -- Error END f_get_open_cursor_count;
For More Information
- What do you think about this tip? E-mail the editor at email@example.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.