Make your queries Self Aware...

In past Oracle versions you may have used USERENV() to access some of this information. The information is now available in the 'USERENV' namespace of the sys_context function. http://www.techonthenet.com/oracle/functions/sys_context.php Here is a useful example. When monitoring the current in-flight SQL it is nice to exclude the current query from the results:
-- In Flight SQL
SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' UNAM, 
       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,
       'ALTER SYSTEM KILL SESSION ''' || SID || ',' || SERIAL# || ''';' as killstmt
  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  sys_context( 'USERENV', 'SESSIONID')
 order by runt desc, 1,sql.piece;
For a complete list of other parameters and more extensive detail on how the values returned might be used, please review the Oracle 9i SQL Reference. -- Dave
Tags: 

Add new comment