Solved

View the current session's last sql

Posted on 2004-04-30
11
7,052 Views
Last Modified: 2007-12-19
I am currently trying to find a way in Oracle (either via PL/SQL or plain SQL) to view my current session's last SQL.  I have figured out how to use the v$session and v$sql tables to get the last sql for other sessions, but if I use this query to retrieve my own last sql, it gives me the SQL that I just entered.  That sql looks something like this:

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

Is there another way that I can view the last sql for my session?  

Would it be possible to run the above sql outside my current session and return the results back to my session?  

Any other suggestions for viewing the last sql for a session are welcome.

Thanks,
Matthew Van Zante
0
Comment
Question by:mvanzan
  • 3
  • 3
  • 2
  • +1
11 Comments
 
LVL 2

Expert Comment

by:Elena-S
Comment Utility
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
0
 

Author Comment

by:mvanzan
Comment Utility
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




0
 
LVL 2

Expert Comment

by:Elena-S
Comment Utility
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.  
0
 
LVL 13

Expert Comment

by:riazpk
Comment Utility
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Expert Comment

by:riazpk
Comment Utility
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

0
 

Author Comment

by:mvanzan
Comment Utility
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
0
 

Author Comment

by:mvanzan
Comment Utility
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
0
 
LVL 13

Accepted Solution

by:
riazpk earned 125 total points
Comment Utility
I think there is no way to do this BUT to run the query:
Select sql_text from  v$SQLTEXT_WITH_NEWLINES where address =
          (Select prev_sql_addr from v$session where sid =&sid )
   order by piece


from another user (and give sid of user)

Another way may be:

select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
  a.sql_text,b.status
    from v$SQLTEXT_WITH_NEWLINES a
, v$session b, v$process c
     where a.address    = b.sql_address
and a.username='&username'
     and b.paddr      = c.addr
     and a.hash_value = b.sql_hash_value
order by c.spid,a.hash_value,a.piece
/

and give the username. It will display the SQL Command for that user (all session). You can then further find what is your desried session.
0
 

Expert Comment

by:ElishaMoshe
Comment Utility
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;

/

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now