Hello,
I have issues with open_cursors and i am looking for the way how to get list of the open cursors. I have made some tests and I am little bit confused from the results.
Session 1:
{code}
declare
cursor a is select 1 cursor1 from dual;
cursor b is select 2 cursor2 from dual;
begin
open a;
close a;
open b;
--close b;
end;
/
{code}
Session2:
{code}
select * from v$open_cursorwhere
sid = 715;
{code}
I get both statements "select 1 cursor1 from dual;" and "select 2 cursor2 from dual" cursor type PL/SQL CURSOR CACHED even when I didn't close the second one. And there is even more confusing thing that the whole pl/sql block had cursor type OPEN in the results of the query using the v$open_cursor view. There is another statement with OPEN-RECURSIVE cursor type in the results.
When I execute in the session2:
{code}
select sysdate as snap_date, a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' and s.username is not null
order by a.value desc;
{code}
Session1 has value 2 so I expect that one query is my pl/sql block with cursor type OPEN and second is the OPEN_RECURSIVE statement.
I don't understand why these cursors stay open? I expect value 0 for "open cursors current". What is the best practice how to find open cursors in the db? Is the last statement executed against db always marked as OPEN? When cursor is closed on the client do oracle always decrease of the counter in the v$sesstat view and mark statements in the v$open_cursor in the different type then like %OPEN%?
Thank you for help,
Vita.