SQL to return detail of all current User Sessions:
column identity format a45 heading 'PID Client/User' ; column command format a14 ; column program format a23 ; SELECT p.spid ||' '|| s.machine||':'|| s.osuser ||'<'|| s.terminal ||'>'|| decode(s.username,s.osuser,null,s.username) identity, decode(s.command, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 6,'UPDATE', 7,'DELETE', 9,'CRE INDEX', 12,'DROP TABLE', 15,'ALT TABLE', 39,'CRE TBLSPC', 42,'ALT SESSION', 44,'COMMIT', 45,'ROLLBACK', 47,'PL/SQL EXEC', 48,'SET XACTN', 62,'ANALYZE TAB', 63,'ANALYZE IX', 71,'CREATE MLOG', 74,'CREATE SNAP', 79,'ALTER ROLE', 85,'TRUNC TAB', to_char(s.command)) command, substr(s.program,instr(s.program,']',-1)+1, decode(instr(s.program,'.',-1) - instr(s.program,']',-1)-1,-1,99, instr(s.program,'.',-1) - instr(s.program,']',-1)-1)) Program FROM v$session s, v$process p WHERE (s.type <> 'BACKGROUND') and (s.paddr = p.addr) and (s.program is not null) ORDER BY s.osuser;
Related:
List Active Sessions SQL