Active session who wait events

set linesize 1000;
set pagesize 1000;
select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,25) box,
       substr(b.username,1,25) username,
--       b.server,
       substr(b.osuser,1,10) os_user,
       substr(b.program,1,40) program,
        to_date(b.LOGON_TIME,'DD-MM-YYYY HH24:MI:SS') 
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;

strong text

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
       SID,  
       serial#,
       MACHINE, 
       SES.sql_id, SES.sql_exec_start, SES.prev_exec_start ,
       --REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT, 
      SQL.SQL_TEXT 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$SQL SQL 
       --V$SQLtext_with_newlines SQL 
       --V$SQLtext 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;

version with spid/linux

SELECT nvl(ses.username,'ORACLE PROC')||' ('||ses.sid||')' USERNAME,
       SES.SID,
       P.spid,
       P.Program,
       SES.serial#,
       MACHINE, 
       SES.sql_id, SES.sql_exec_start, SES.prev_exec_start ,
       --REPLACE(SQL.SQL_TEXT,CHR(10),'') STMT, 
      SQL.SQL_TEXT 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$process p,
       V$SQL SQL 
       --V$SQLtext_with_newlines SQL 
       --V$SQLtext 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.paddr = p.addr
   --and Ses.AUDSID <> userenv('SESSIONID') 
 order by runt desc, 1--,sql.piece;

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

wait event

    select a.sid,substr(b.username,1,10) username,substr(b.osuser,1,10) osuser,
     substr(b.program||b.module,1,15) program,substr(b.machine,1,22) machine,
     a.event,a.p1,b.sql_hash_value
     from v$session_wait a,V$session b
     where b.sid=a.sid
     and a.event not in('SQL*Net message from client','SQL*Net message to client',
     'smon timer','pmon timer')
     and username is not null
     order by 6
 /