I have recentely joined as member in this forum, even thouh i am a regular guest. This is my first query.
I would like to know all the sql statements executed in a particular active session. And also what is the difference in V$sql, V$sqlarea views.
Thanks in Advance.
Welcome to a great forum .Enjoy your stay here.i bet it would be a great learning experience.and now to ur answer
V$SQLAREA first 80 characters of the sql statements
V$SQLTEXT full sql text
to know all the sql statement executed in particular session use thsi query
select nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')'||DECODE(Ses.AUDSID,userenv('SESSIONID'),'**','') UNAM,
, ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
|| ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
|| ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09')) RUNT
from V$SESSION SES
, V$SQLTEXT_WITH_NEWLINES SQL
where SES.USERNAME is not null
and SES.SQL_ADDRESS = SQL.ADDRESS
and SES.SQL_HASH_VALUE = SQL.HASH_VALUE
and ses.sid = &&Session_ID
order by sql.piece
you could also first search thsi forum before posting a query.who knows your question might have laready been answered.
Thanks for ur reply.
This query given by u is giving the latest query executed in that session. Suppose i have executed 5 queries in a session, i want to see all the 5 sql statements executed. can i see this from any table/view. Will audting help in this, if so how can i do it.
you cant, you can only see the last 2 sql stmts
how can see atleast the 2 latest sql statements. Can u throw some light on this.
Click Here to Expand Forum to Full Width