To list all the open Oracle cursors you can run the following SQL against the Oracle database:
SELECT s.sid, q.users_executing, q.sql_text
FROM v$session s, v$sql q
WHERE s.sid IN( 24,27,30 ) AND q.address = s.sql_address
ORDER BY s.sid;
The above SQL will list all the open cursors which belong to the sids 24, 27, and 30. To get the full list with no filtering, run:
SELECT s.sid, q.users_executing, q.sql_text, q.hash_value
FROM v$session s, v$sql q
WHERE q.address = s.sql_address
ORDER BY s.sid;
Be aware this query can return 100s or 1000s of results on a busy system.
