Database sessions

Sessions sorted by login time

 set lines 200
 set pages 999
 col killer      format a15
 col osuser      format a15
 col login_time  format a15
 select s.username
 ,      s.osuser
 ,      s.sid || ',' || s.serial# "Killer"
 ,      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    s.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'
       );

Show what SQL statements are being run by all user sessions

select a.username
,      a.sid
,      a.serial#
,      b.sql_text
from   v$session a
,      v$sqlarea b
where  1=1
and    a.sql_address = b.address

Show blocking sessions

select s1.username || '@' || s1.machine||
       ' ( SID=' || s1.sid || ' )  is blocking '||
       s2.username || '@' || s2.machine ||
       ' ( SID=' || s2.sid || ' ) '  blocking_status
from   v$lock l1
,      v$session s1
,      v$lock l2
,      v$session s2
where  1=1
and    s1.sid     = l1.sid
and    s2.sid     = l2.sid
and    l1.block   = 1
and    l2.request > 0
and    l1.id1     = l2.id1
and    l2.id2     = l2.id2;

Show all ddl locks in the system

 select	ses.username
 ,	ddl.session_id
 ,	ses.serial#
 ,	owner || '.' || ddl.name object
 ,	ddl.type
 ,	ddl.mode_held
 from	dba_ddl_locks ddl
 ,	v$session ses
 where	owner like '%&userid%'
 and	ddl.session_id = ses.sid;

Generate kill statement for ddl locking sessions

 select 'alter system kill session ''' || ddl.session_id || ',' || ses.serial# || ''' immediate;'
 from   dba_ddl_locks ddl
 ,      v$session ses
 where  1=1
 and    ddl.session_id = ses.sid;
 and    owner          like upper('%&userid%')

Display any long operations

set lines 100
set pages 100
col username  format a15
col message   format a40
col remaining format 9999
select username
,      to_char(start_time, 'dd-mon-yy hh24:mi') started
,      time_remaining remaining
,      message
from   v$session_longops
where  1=1
and    time_remaining != 0
order  by time_remaining desc

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  1=1
        and    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;
 /

List open cursors per user

set pages 999
select sess.username
,      sess.sid
,      sess.serial#
,      stat.value cursors
from   v$sesstat stat
,      v$statname sn
,      v$session sess
where  1=1
and    sess.username   is not null
and    sess.sid        = stat.sid
and    stat.statistic# = sn.statistic#
and    sn.name         = 'opened cursors current'
order  by value;