How to Determine the Currently Opened Cursor in Oracle?
 Technote (FAQ)
 
Question
Determine the currently opened cursors in Oracle
 
Answer
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
 
 
Cross Reference information
Segment Product Component Platform Version Edition
Application Servers Runtimes for Java Technology Java SDK
 
 


Document Information


Product categories: Software > Application Servers > Distributed Application & Web Servers > WebSphere Application Server
Operating system(s): Windows
Software version: 6.0.2
Software edition:
Reference #: 1007158
IBM Group: Software Group
Modified date: Dec 7, 2005