Find the total number of opened cursors for any particular user

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

Reader Feedback

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 protected] 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.