View the current session's last sql

Try this SQL instead:

Select sql_text from v$sqltext where address =
   (Select prev_sql_addr from v$session where sid =
      (Select unique sid from v$mystat)
   )

I am using unique in place of rownum.  Here is what I got when I tested this statement:

SQL> select sysdate from dual;

SYSDATE
---------
30-APR-04

SQL> Select sql_text from v$sqltext where address =
  2     (Select prev_sql_addr from v$session where sid =
  3        (Select unique sid from v$mystat)
  4     );

SQL_TEXT
----------------------------------------------------------------
select sysdate from dual

Hope this helps

Elena-S,

Not to doubt your input, but I just tried the exact same thing and I'm still getting the current query. In other words,

SQL> Select sql_text from v$sqltext where address =
  2     (Select prev_sql_addr from v$session where sid =
  3        (Select unique sid from v$mystat)
  4     order by piece;
  5     );

gives me:

SQL_TEXT
--------------------------------------------------------------------------
Select sql_text from v$sqltext where address =  (Select prev_sql
_addr from v$session where sid =  (Select unique sid from v$mys
tat) )

Could this be an Oracle configuration issue?  I'm using 9iR2 on Solaris.  

Thanks,
Matthew

I am not sure. I ran mine in Oracle 10g.  When I do it on Oracle 9i (Enterprise Edition Release 9.2.0.4.0), I get the same result as you.  When I select sql_address and prev_sql_address from v$session, I get the same value for both columns. It is also so for sql_hash_value and prev_hash_value columns.  I will let you know if I find more info.  

Try v$SQLTEXT_WITH_NEWLINES instead of V$SQLTEXT.


a quick test:


  1  Select sql_text from  v$SQLTEXT_WITH_NEWLINES where address =
  2         (Select prev_sql_addr from v$session where sid =(Select unique sid from v$mystat)
  3         )
  4* order by piece
SQL> /

SQL_TEXT
================================================================
Select sql_text from  v$SQLTEXT_WITH_NEWLINES where address =


      (Select prev_sql_addr from v$session where sid =(Select un
ique sid from v$mystat)
       )
order by piece

But the problem here is that when you will execute the command

Select sql_text from  v$SQLTEXT_WITH_NEWLINES where address =
  2         (Select prev_sql_addr from v$session where sid =(Select unique sid from v$mystat)
  3         )
  4* order by piece


it will become last SQL Command and hence the same will be displayed. So i think you are aware that you will have to explicity give you the sid for the user for which you want to see the last command executed.

And a simple one, you can know which user is doing what by using:

set linesize 150
  set pagesize 66
  col c1 for a9
  col c1 heading "OS User"
  col c2 for a9
  col c2 heading "Oracle User"
  col b1 for a9
  col b1 heading "NT PID"
  col b2 for 9999 justify left
  col b2 heading "ORA SID"
  col b3 for 999999 justify left
  col b3 heading "SERIAL#"
  col sql_text for a65
  set space 1
  set pagesize 2000
  break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3


  select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
  a.sql_text,b.status
--,to_char(b.logon_time, 'DD-MON-YYYY HH24:MI:SS')
    from v$SQLTEXT_WITH_NEWLINES a
--v$sqltext a
, v$session b, v$process c
     where a.address    = b.sql_address
--     and b.status     = 'ACTIVE'
/* YOU CAN CHOOSE THIS OPTION ONLY TO SEE
  --ACTVE TRANSACTION ON THAT MOMENT*/
     and b.paddr      = c.addr
     and a.hash_value = b.sql_hash_value
--and b.username like '&user%'
and b.username not in('SYSTEM','SYS')
order by c.spid,a.hash_value,a.piece
/

set title off

riazpk,

I won't get a chance to try your suggestion until Monday, but I think I've already tried something very close to this.  It reminds me of an AskTom solution:

http://asktom.oracle.com/pls/ask/f?p=4950:8:519498075307800596::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:497421739750,

When I tried the AskTom solution it still had the problem of displaying the sql that I ran to view the sql (The same problem as mentioned above).  Have you tested your solution in 9i?  

Anyway, I appreciate the input and will give it a try on Monday.

Thanks,
Matthew

riazpk,

Gave your solution a try but since I'm trying to get the last sql for the current session, it still has the same problem of the request becoming the last sql.  

I have found a solution that looks that it will work, but it requires me to use the DBMS_TRACE package.  I'm not sure if that will be allowed.  

If anybody knows of a way to get the last (or if performing a query, the 2nd to last) sql of a session without using the trace package, please help! ;-)

-Matthew

Hey,

I was facing the same issue.
I found a workaround by creating a DB link to the same DB and executing the query using the DB link and therefor that select occurs in a different session.

This works fine for me on 'Oracle Database 10g Express Edition Release 10.2.0.1.0"

-- A function that returns the current SQL by SID
create or replace function get_current_sql(p_sid number) return varchar2 as 
  cur_sql_text varchar2(32767) := '';
  sql_text_len number;
  cursor last_sql_cur(p_cur_sid number) is
    select a.sql_text
    from v$session s,
    v$sqltext_with_newlines a,
    v$process p
    where s.sql_hash_value=a.hash_value
    and s.sql_address=a.address
    and s.paddr=p.addr
    and s.status='ACTIVE'
    and sid = p_cur_sid
    order by piece;
BEGIN
  
  for sql_text_rec in last_sql_cur(p_sid) loop
    cur_sql_text := cur_sql_text || sql_text_rec.sql_text;
  end loop;  
  
  sql_text_len := length(cur_sql_text);
  -- Removing the NULL charaecter if exists
  if (ascii(SUBSTR(cur_sql_text, sql_text_len)) = 0) then
    cur_sql_text := SUBSTR(cur_sql_text, 0, sql_text_len - 1);
  end if;
 
  return (cur_sql_text);
END;
/
 
-- Creating a DB link to the same DB we are running on
create database link this connect TO demo identified by demo using 'xe';
/
 
CREATE OR REPLACE SYNONYM this_get_current_sql for get_current_sql@this;
/
 
-- Test it
DECLARE 
  current_sid number;
BEGIN
    select distinct sid 
    into current_sid
    from v$mystat;
         
    dbms_output.put_line('Current SQL ' || this_get_current_sql(current_sid));
END;
/

Select allOpen in new window