Blog / 2018 / October / 2

Active SQL Statements on Oracle

October 2, 2018

This query can be used to show the active SQL statements, and their elapsed time, on an Oracle database.

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;

You can also add in a restriction on the machine column to limit to statements from a specific source machine.

Tags: oracle queries performance database db