Sessions SQL

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


© Copyright SS64.com 1999-2013
Some rights reserved