Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can you show the SQL that is currently executing on an oracle db?

Extra information that would be useful would include user, session id etc.

share|improve this question

2 Answers 2

up vote 7 down vote accepted

Most of the info is available in v$session ... and the SQL Text can be grabbed from v$sql or v$sqltext_with_newlines...

Here is a query that I often use that formats In-flight SQL, longest running at top.

-- In Flight SQL 
SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
       SID,   
       MACHINE, 
       REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT, 
      ltrim(to_char(floor(SES.LAST_CALL_ET/3600), '09')) || ':'
       || ltrim(to_char(floor(mod(SES.LAST_CALL_ET, 3600)/60), '09')) || ':'
       || ltrim(to_char(mod(SES.LAST_CALL_ET, 60), '09'))    RUNT 
  FROM V$SESSION SES,   
       V$SQLtext_with_newlines SQL 
 where SES.STATUS = 'ACTIVE'
   and SES.USERNAME is not null
   and SES.SQL_ADDRESS    = SQL.ADDRESS 
   and SES.SQL_HASH_VALUE = SQL.HASH_VALUE 
   and Ses.AUDSID <> userenv('SESSIONID') 
 order by runt desc, 1,sql.piece;
share|improve this answer
1  
+1 great! but runt? :) –  Jack Douglas Dec 6 '11 at 15:57
    
RUNT = Runtime. –  David Mann Dec 6 '11 at 17:55
    
I got that - but wondered if any comedy effect was intentional ;) –  Jack Douglas Dec 6 '11 at 18:47
1  
I think I was just in a rush when I first threw it together... after messing with all that ltrim/tochar/floor/mod stuff I just wanted to name the column and get out :) –  David Mann Dec 6 '11 at 21:36
    
Great answer, thanks very much. I half figured out an answer that would suffice, but didn't have enough rep to post it, ha! Your's is much better though :) Have a tick and a +1. –  Clarkey Dec 7 '11 at 9:16

Good answer found here.

Log in as sysdba:

sqlplus '/as sysdba'

And execute the following sql:

select session.sid
      ,session.serial#
      ,session.username
      ,session.sql_id
      ,session.sql_child_number
      ,optimizer_mode
      ,hash_value
      ,address
      ,sql_text
from   v$sqlarea sqlarea
      ,v$session session
where  session.sql_hash_value = sqlarea.hash_value
and    session.sql_address    = sqlarea.address
and    session.username       is not null;

If the output is unreadable, change the LINESIZE (take from here):

SET LINESIZE 20000
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.