V$OPEN_CURSOR

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

Oracle data dictionary views

Oracle dynamic performance views