<keesdebie_at_gmail.com>
> In a session sometimes cursors are been opend but for some reason not
> parsed and closed.
> Mostly it happens with applications where sometimes the statement
> crashed and the cursor will be a ghost.
> With the script below you can asked the rdbms how many lost cursors
> there are.
> Output script looks like:
> DOC> colomn 1 date
> DOC> colomn 2 showed all cursors which are opend not parsed of closed
> DOC> colomn 3 showed the current open cursors
> DOC> colomn 4 showed the difference off the ghost cursors
> DOC>#
>
> missing
> or
> open
> cursors
> not
> current parsed
> open or
> date ALL_INCLUDING_MISSING cursors closed
> -------------------- --------------------- ---------- ----------
> 14-jun-2006 16:00:46 89 283 -194
>
> This query runs in 10g REL2 and give the above strainges values.
> Running in < 10g rel2
> the output give a total open cursors and open cursoren and lost
> cursoren(if there are some)
> total open cursors including missing = open cursors + lost cursors
> What has been changed in 10g REL2?
>
> Script:
> set lin 200
> doc
> colomn 1 date
> colomn 2 showed all cursors which are opend not parsed of closed
> colomn 3 showed the current open cursors
> colomn 4 showed the difference off the ghost cursors
> #
> col alle_including_missing heading "all|including|missing"
> col current_cursors heading "current|open|cursors"
> col lost heading "missing|or|open|cursors|not|parsed|or|closed"
> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') "date"
> , s.all_including_missing
> , oc.current_cursors
> , s.all_including_missing-oc.current_cursors lost
> from (select value all_including_missing
> from v$sysstat
> where statistic#=3
> ) s
> , (select count(1) current_cursors
> from v$open_cursor
> ) oc
> ;
> Regards,
> Kees
>
Received on Wed Jun 14 2006 - 19:36:00 CDT
