V$session Help

[ctcyl]

Dear Friends..

I am checking thorugh V$session, that which user is connecting with our application, but i am unable to check which SQL command that particular user is using.

Can anybody will advise, how can we check it.

Thanks in advance.

Sandeep

[pipo]

just make a join between v$session.sql_address and v$sqlarea.address, and get sql_text
from v$sqlarea

[jjongman]

rem Name: Xsqluser.sql - SQLText executing now per user
rem Date: 08-may-2001
rem Note: Select is on "users_executing >0" ; only active statements.

set pages 100 feed OFF wrap on linesize 100
col SID for 999
col buffer_gets for 99999999999 head 'Buffer|reads'
col users_executing for 999 head 'Exec|now'
col executions for 999999 head 'Execs'
col rows_processed for 999999 head 'rows|procesd'
col firstload head 'First|load'
col username for A10 head Username word_wrap
col sql_text for A40

select S.SID
,S.username
,A.users_executing
,A.buffer_gets
,A.executions
,A.rows_processed
,substr(A.first_load_time,12,8) Firstload
,A.sql_text
from v$SQLAREA A
,v$SESSION S
where A.address = S.sql_address
and S.username = &username
order by users_executing,first_load_time
/