Blog Archive for October 2, 2018

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 …