ba6.us - Dave's Database Related Stuff

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