Session - Open Cursors

So, something else I learned recently...its about Open cursors in Session.

V$open_cursor – Represents a set of cached cursors that server has.  Here is a quick test to demonstrate that.

scott@10GR2> SELECT *
  2  FROM v$open_cursor
  3  WHERE sql_text LIKE '%CURSOR_TEST%';

no rows selected

scott@10GR2> declare
  2  cursor c1 is select * from dual cursor_test;
  3  l_rec c1%rowtype;
  4  begin
  5     open c1;
  6     fetch c1 into l_rec;
  7     close c1;
  8  end;
  9  /

PL/SQL procedure successfully completed.

scott@10GR2> SELECT *
  2  FROM v$open_cursor
  3  WHERE sql_text LIKE '%CURSOR_TEST%'
  4  /

no rows selected

scott@10GR2> create or replace procedure test_proc as
  2  cursor c1 is select * from dual cursor_test;
  3  l_rec c1%rowtype;
  4  begin
  5     open c1;
  6     fetch c1 into l_rec;
  7     close c1;
  8  end test_proc;
  9  /

Procedure created.

scott@10GR2> exec test_proc;

PL/SQL procedure successfully completed.

scott@10GR2> SELECT sid,sql_id,sql_text
  2  FROM v$open_cursor
  3  WHERE sql_text LIKE '%CURSOR_TEST%';

 SID         SQL_ID          SQL_TEXT
 --------   ----------         -----------------------------
 159         9y8yp8fh22fxz   SELECT * FROM DUAL CURSOR_TEST

Even I closed the cursor Explicitly it’s still shown up in V$open_Cursor (due to set of cached cursors in server).

To get the current open cursors ( Cursor leaking ) across session

scott@10GR2> select sum(a.value), b.name,a.sid
  2  from v$sesstat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  group by b.name,a.sid
  6  having  sum(a.value) > 0
  7  /

SUM(A.VALUE) NAME                                  SID
------------ ------------------------------ ----------
          32 opened cursors current                140
           5 opened cursors current                138
           5 opened cursors current                132
           4 opened cursors current                162
          81 opened cursors current                154
          29 opened cursors current                161
          11 opened cursors current                134
          23 opened cursors current                136
          55 opened cursors current                144
          25 opened cursors current                149

10 rows selected.

open_cursors - its purely related to Session, say if you set Open_cursor 1000, so there could be 10,000 of them if you have 10 sessions.