How to Determine the Currently Opened Cursor in Oracle?

Technote (FAQ)
Problem
Determine the currently opened cursors in Oracle
Solution
You can exactly determine the currently opened cursors which are in use by a session using the following query:

SELECT sql_text, address, hash_value
FROM v$open_cursor a , v$session b
WHERE a.sid = b.sid
AND a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.status = 'ACTIVE'


You can use any Oracle's tool likes SQL*Plus or SQL*Plus Worksheet to run the above query. You must run the query using Oracle's userid SYSTEM (or any userid that has permission to read the tables v$open_cursor and v$session). For example:


sqlplus scott/tiger

SQL> SELECT sql_text, address, hash_value
2 FROM v$open_cursor a , v$session b
3 WHERE a.sid = b.sid
4 AND a.address = b.sql_address
5 AND a.hash_value = b.sql_hash_value
6 AND b.status = 'ACTIVE'
7 /

SQL_TEXT ADDRESS HASH_VALUE
------------------------------------------------------------ -------- ----------
SELECT sql_text, address, hash_value FROM v$open_ 05E1A5E0 1239165040












    Document Information

    Product categories: Software, Application Servers, Distributed Application & Web Servers, WebSphere Application Server
    Operating system(s): Multi-Platform
    Software version: 3.0.2.x, 3.5, 4.0.1
    Reference #: 1007158
    IBM Group: Software Group
    Modified date: 2001-11-27