Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

View the current session's last sql

Posted on 2004-04-30
11
Medium Priority
?
7,202 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
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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 500 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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

916 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