How to see open cursors
23 November 2009
534 views
No Comment
Accessible Cursors
SELECT oc.user_name, st.sql_text
FROM v$open_cursor oc,v$sqltext st
WHERE oc.address = st.address
AND oc.hash_value = st.hash_value
ORDER BY oc.user_name, st.piece
FROM v$open_cursor oc,v$sqltext st
WHERE oc.address = st.address
AND oc.hash_value = st.hash_value
ORDER BY oc.user_name, st.piece
For performance reasons, pl/sql cursors are cached for future reuse. So once you use them up they will be added to LRU list of cursor table. Hence you can see them in v$open_cursor.
Actual Open Cursors
select sum(a.value), b.name
from v$sesstat a,v$statname b
where a.statistic# = b.statistic#
and b.name = ‘opened cursors current’
group by b.name
Google Search: How to see open cursors
DB Error Code: How to see open cursors
Leave your response!