REM SQL to display current user connections in the database
REM NOTE: only shows activity on whichever node of the OPS
REM       is referred to by the user-s TWO_TASK environment variable
REM       (usually cas1)

set linesize 100;
set pagesize 200;

column os_user format A10 trunc;
column db_user format A12 trunc;
column machine format A10 trunc;
column program format A30 trunc;
column login_time format A20;

select 
osuser os_user,
username||decode(status,'ACTIVE','*','INACTIVE','-') db_user,
machine,
decode
(substr
(program,1,instr(program,'@')-1),
'oracle','(parallel query)',
substr(program,1,instr(program,'@')-1)) program,
to_char(logon_time, 'dd-MON-yyyy hh24:mi:ss') login_time
from 
v$session
where
sid in
(select min(sid) from v$session where username is not null 
group by process) 
order by osuser,username,program;

quit;

/
