V$OPEN_CURSOR lists cursors that each user session currently has opened and parsed.
| Column | Datatype | Description |
| SADDR | RAW(4 | 8) | Session address |
| SID | NUMBER | Session identifier |
| USER_NAME | VARCHAR2(30) | User that is logged in to the session |
| ADDRESS | RAW(4 | 8) | Used with HASH_VALUE to uniquely identify the SQL statement being executed in the session |
| HASH_VALUE | NUMBER | Used with ADDRESS to uniquely identify the SQL statement being executed in the session |
| SQL_ID | VARCHAR2(13) | SQL identifier of the SQL statement being executed in the session |
| SQL_TEXT | VARCHAR2(60) | First 60 characters of the SQL statement that is parsed into the open cursor |
| LAST_SQL_ACTIVE_TIME | DATE | Time when this cursor was last executed |
| SQL_EXEC_ID | NUMBER | If the open cursor is executing, then the SQL execution identifier for that execution (see V$SQL_MONITOR) |
Note:
1. V$OPEN_CURSOR related queries:
| · Count open cursors by username and SID select user_name, SID, count(*) cursors from V$OPEN_CURSOR group by User_Name, SID order by User_Name, SID; |
| · displays information on cursor usage for the current session select * from V$SESSION_CURSOR_CACHE; |
| · displays information on cursor usage for the system. select * from V$SYSTEM_CURSOR_CACHE; |
| · list current users and the sql executing select SID, User_Name, SQL_Text from V$OPEN_CURSOR order by SID, User_Name, SQL_Text; |
| · list details of current open cursors select user_name,to_char(sysdate,'hh24:mi:ss') col1, sql_text from V$OPEN_CURSOR; |
| · query the subtotal and total usage of open cursors select user_name, count(*) from v$open_cursor group by rollup(user_name) order by count(*); |
| · find out the transaction id for a particular DML statement that a user is running; this query can be used to find out the DML statement that a user is currently running select t.XIDUSN || '.' || t.XIDSLOT || '.' || t.XIDSQN TRANSACTION_ID, q.sql_text from v$transaction t, v$session s, v$open_cursor o, v$sqltext q where t.addr = s.taddr and s.saddr = o.saddr and s.sid = o.sid and o.address = q.address and o.hash_value = q.hash_value and s.username = 'USERNAME' order by q.address, q.hash_value, q.piece; |
2. Useful joins with V$OPEN_CURSOR
| · SID: Join to V$SESSION.SID · SADDR: Join to V$SESSION.SADDR · (ADDRESS,HASH_VALUE): Join to V$SQL.(ADDRESS,HASH_VALUE) · (ADDRESS,HASH_VALUE): Join to V$SQLTEXT.(ADDRESS,HASH_VALUE) |
| |
3. Parameter related: OPEN_CURSORS
4. Oracle error related:
| ORA-1000: maximum open cursors exceeded ------------------------------------------------------------------------ Cause: A host language program attempted to open too many cursors. The initialization parameter OPEN_CURSORS determines the maximum number of cursors per user. Action: Modify the program to use fewer cursors. If this error occurs often, shut down Oracle, increase the value of OPEN_CURSORS, and then restart Oracle. |
| Last updated: Thursday, October 1, 2009 |
