Solved

View the current session's last sql

Posted on 2004-04-30
11
7,108 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
11 Comments
 
LVL 2

Expert Comment

by:Elena-S
ID: 10962646
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
ID: 10963584
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
ID: 10963844
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 13

Expert Comment

by:riazpk
ID: 10963997
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
 
LVL 13

Expert Comment

by:riazpk
ID: 10964019
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
ID: 10967845
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
ID: 10981667
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
ID: 10996111
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
ID: 24074989
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Row_number in SQL 6 57
sql server store procedure contains temp tables need to convert oracle? 3 58
error doing substr 3 52
only show the last 365 days 6 35
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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

739 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