[DB] [Oracle] SQL statement for querying the number of active database cursors

SQL statement for querying the number of active database cursors

Select All allcursor cursor, myallcursor my cursor count, myopencursor my open cursor count, mycachecursor my session cache cursor count (myallcursor-myopencursor-mycachecursor) my other cursors from (select count (*) from V $ open_cursor A) allcursor, (select count (*) from V $ open_cursor A where. user_name = 'telsoft _ r_01 ') myallcursor, (select sum (AA. value) from (select. value from V $ sesstat A, V $ statname B, V $ session s where. statistic # = B. statistic # and S. SID =. sid and B. name = 'session cursor cache count' and S. username = 'telsoft _ r_01 ') aa) mycachecursor, (select sum (AA. value) from (select. value from V $ sesstat A, V $ statname B, V $ session s where. statistic # = B. statistic # and S. SID =. sid and B. name = 'opened cursors current 'and S. username = 'telsoft _ r_01 ') aa) myopencursor from dual); -- query the total number of open cursors of each user (including cache cursors) Select. user_name, count (*) Total number of open cursors for each user from V $ open_cursor a group by. user_name; -- query the number of cache cursors of each database user terminal. Select AA. username, AA. machine, sum (AA. value) Number of cached cursors from (select. value, S. machine, S. username from V $ sesstat A, V $ statname B, V $ session s where. statistic # = B. statistic # and S. SID =. sid and B. name = 'session cursor cache count') AA group by AA. username, AA. machine order by AA. username, AA. machine; -- find the number of open cursors for each terminal of the database user select AA. username, AA. machine, sum (AA. value) Open the number of cursors from (select. value, S. machine, S. username from V $ sesstat A, V $ statname B, V $ session s where. statistic # = B. statistic # and S. SID =. sid and B. name = 'opened cursors current ') AA group by AA. username, AA. machine order by AA. username, AA. machine;