Thursday, October 1, 2009

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



No comments:

Post a Comment