Re: Last SQL Query for any User

From: Steve Howard <stephen.howard_at_us.pwcglobal.com>
Date: 22 May 2003 11:30:33 -0700
Message-ID: <6d8b7216.0305221030.13f7369@posting.google.com>

"Alex Smith" <SorryNoSpam_at_Please.Com> wrote in message news:<cl2za.2762$Pz3.1280396_at_news4.srv.hcvlny.cv.net>...
> How can we get the Query that was last executed by a User.
>
> For TOAD users - "Kill / Trace Session" gives this ability to monitor all
> activities and see SQL query executed last for any user.
>
> How can I accomplish this by linking
>
> V$SESSION, V$SQLAREA or V$SQLTEXT

The following probably gives you more than you want, but it may get you started.

select distinct spid,

       s.sid,
       s.serial#,to_char(sysdate - last_call_et/(24*3600),'mm/dd/yy

hh24:mi:ss') "LAST_ACTIVITY"
,'alter system kill session '''||s.sid||','||s.serial#||'''' "KILL",

       logon_time,
       osuser,
       s.program,
       schemaname,
       sql_text,

    reads.value reads,
    writes.value writes,
    cpu.value cpu
  from v$session s,

      v$process p,
    v$sql t,
   (select name,sid,value

         from v$sesstat st,v$statname n
         where st.statistic#=n.statistic#
           and name in('physical reads')) reads,
      (select name,sid,value
          from v$sesstat st,v$statname n
          where st.statistic#=n.statistic#
            and name in('physical writes')) writes,
      (select name,sid,value
          from v$sesstat st,v$statname n
          where st.statistic#=n.statistic#
            and name in('CPU used by this session')) cpu
  where s.paddr=p.addr

    and t.hash_value(+)=s.sql_hash_value     and s.type !='BACKGROUND'
    and reads.sid=s.sid
  and writes.sid=s.sid
  and cpu.sid=s.sid; Received on Thu May 22 2003 - 13:30:33 CDT