V$SQLTEXT

V$SQLTEXT displays the text of SQL statements belonging to shared SQL cursors in the SGA.

Column	Datatype	Description
ADDRESS	RAW(4 | 8)	Used with HASH_VALUE to uniquely identify a cached cursor
HASH_VALUE	NUMBER	Used with ADDRESS to uniquely identify a cached cursor
SQL_ID	VARCHAR2(13)	SQL identifier of a cached cursor
COMMAND_TYPE	NUMBER	Code for the type of SQL statement (SELECT, INSERT, and so on)
PIECE	NUMBER	Number used to order the pieces of SQL text
SQL_TEXT	VARCHAR2(64)	A column containing one piece of the SQL text

Note:

1. Check what session has locked a PL/SQL package

select x.sid from v$session x, v$sqltext y where x.sql_address = y.address and y.sql_text like '%%';

2. Check the users who logged in on a specific time

select b.logon_time, b.username, b.sid, b.program, a.sql_text from V$SQLTEXT a,V$SESSION b where a.address=b.sql_address and b.username is not null and b.status = 'ACTIVE' and logon_time > TO_DATE('2009-07-05', 'YYYY-MM-DD');

-- sid and serial# can be used to kill the session that is causing the problem -- ALTER SYSTEM KILL SESSION 'SID,SERIAL#' select a.sid, a.serial#, a.username, b.sql_text from V$SESSION a, V$SQLTEXT b where a.username is not null and a.status = 'ACTIVE' and a.sql_address = b.address;

Oracle data dictionary views

Oracle dynamic performance views

Last updated: July 6, 2009