(10g) A query to find the most active users connected from another machine

This query is useful to find middle-tier (applications servers or so) users having a heavy activity in the instance, thanks to the column CLIENT_IDENTIFIER, that is maintained throught all the layers. You can, also, filter by active users.

set linesize 1000
col program format a30
col machine format a10
col PERSONA format a10
col program format a7
col username format a7
alter session set nls_date_format='dd/mm/yy hh24:mi';

select s.CLIENT_IDENTIFIER PERSONA, substr(s.program, 1,7) program, s.username, l.TIME_REMAINING REMAINING, l.ELAPSED_SECONDS ELAPSED,l.START_TIME, s.sid, s.serial#, s.process,
l.OPNAME,l.TARGET,l.TARGET_DESC,l.SOFAR,l.TOTALWORK,l.UNITS,
l.LAST_UPDATE_TIME,l.TIMESTAMP,l.CONTEXT,l.MESSAGE
from v$session s, v$session_longops l
where s.TYPE='USER'
and program like 'dis51ws@%'
and s.username='SCOTT'
and s.SID=l.sid
and s.SERIAL#=l.SERIAL#
--and status='ACTIVE'
order by elapsed_seconds desc;