List of sql statements for a session

[V6163]

Hello Friends,
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.

[hrishy]

Hello

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,
SQL.SQL_TEXT STMT
, 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.

regards
Hrishy

[V6163]

Hi Hrishy
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.

Thank u.

[pando]

you cant, you can only see the last 2 sql stmts

[V6163]

Dear Pando

Thank u.
how can see atleast the 2 latest sql statements. Can u throw some light on this.