Difference between revisions of "Database sessions"

Revision as of 19:48, 10 November 2012

Sessions sorted by login time

set lines 100
set pages 999
col ID          format a15
col osuser      format a15
col login_time  format a14
select s.username
,      s.osuser
,      s.sid || ',' || s.serial# "ID"
,      p.spid                    "OS PID"
,      s.status
,      to_char(s.logon_time, 'dd-mon-yy hh24:mi') login_time
,      s.last_call_et
from   v$session s
,      v$process p
where  1=1
and    s.paddr  = p.addr
and    username is not null
order  by login_time;

Show a users current sql

select sql_text
from   v$sqlarea
where  1=1
and    (address, hash_value) in
       (
       select sql_address
       ,      sql_hash_value 
       from   v$session
       where  1=1
       and    username like '&username'
       );

All active sql

set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
   for x in
   (
   select username||'('||sid||','||serial#||') ospid = '|| process ||' program = ' || program username
   ,      to_char(LOGON_TIME,' Day HH24:MI') logon_time
   ,      to_char(sysdate,' Day HH24:MI')    current_time
   ,      sql_address
   ,      sql_hash_value
   from   v$session
   where  1=1
   and    status                 = 'ACTIVE'
   and    rawtohex(sql_address) != '00'
   and    username is not null
   ) loop
       for y in (select sql_text
       from v$sqlarea
       where address = x.sql_address )
       loop
           if ( y.sql_text not like '%listener.get_cmd%' and y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
               dbms_output.put_line( '--------------------' );
               dbms_output.put_line( x.username );
               dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
               dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
           end if;
       end loop;
   end loop;
end;
/