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;

Free space in TEMP tablespaces

select tablespace_name temp_name
,      tablespace_size/1024/1024 temp_size
,      allocated_space/1024/1024 temp_alloc
,      free_space/1024/1024 temp_free
from   dba_temp_free_space
/

Live TEMP free space monitoring

select a.tablespace_name                                             tablespace
,      d.mb_total                                                    mb_total
,      sum (a.used_blocks * d.block_size) / 1024 / 1024              mb_used
,      d.mb_total - sum (a.used_blocks * d.block_size) / 1024 / 1024 mb_free
from   v$sort_segment a
,      (
       select b.name
       ,      c.block_size
       ,      sum (c.bytes) / 1024 / 1024 mb_total
       from   v$tablespace b
       ,      v$tempfile   c
       where  b.ts# = c.ts#
       group  by b.name
       ,      c.block_size
       ) d
where  a.tablespace_name = d.name
group  by a.tablespace_name
,      d.mb_total
/

Sessions currently holding segments in TEMP tablespace

select b.tablespace
,      b.segfile#
,      b.segblk#
,      b.blocks
,      a.sid
,      a.serial#
,      a.username
,      a.osuser, a.status
from  v$session a
,     v$sort_usage b
where  a.saddr = b.session_addr
order  by b.tablespace
,      b.segfile#
,      b.segblk#
,      b.blocks
/

More detailed sessions currently holding segments in TEMP tablespace

col sid_serial for a10
col module     for a20
col program    for a25
col tablespace for a20
select s.sid || ',' || s.serial# sid_serial
,      s.username
,      s.osuser
,      p.spid
,      s.module
,      s.program
,      sum (t.blocks) * tbs.block_size / 1024 / 1024 mb_used
,      t.tablespace
,      count(*) sort_ops
from   v$sort_usage t
,      v$session s
,      dba_tablespaces tbs
,      v$process p
where  t.session_addr = s.saddr
and    s.paddr = p.addr and t.tablespace = tbs.tablespace_name
group  by s.sid
,      s.serial#
,      s.username
,      s.osuser
,      p.spid
,      s.module
,      s.program
,      tbs.block_size
,      t.tablespace
order  by sid_serial
/

SQL statements used by TEMP sessions

select s.sid || ',' || s.serial# sid_serial
,      s.username
,      t.blocks * tbs.block_size / 1024 / 1024 mb_used
,      t.tablespace
,      t.sqladdr address
,      q.hash_value
,      q.sql_text
from   v$sort_usage t
,      v$session s
,      v$sqlarea q
,      dba_tablespaces tbs
where  t.session_addr = s.saddr
and    t.sqladdr      = q.address (+)
and    t.tablespace   = tbs.tablespace_name
order  by s.sid
/

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

col username  for a12
col sid       for 99999
col sql_text  for a200
set lines 1000 pages 1000 long 100000
select a.username
,      to_char(a.logon_time,'DD-MON-YY HH24:MI:SS') dt
,      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

Show waits for (blocking) datapump sessions

set pages 100 lines 1000
col p1 for 999999999999
col p2 for 999999999999
col p3 for 999999999999
select sid
,      event
,      p1
,      p2
,      p3
,      seconds_in_wait
from   v$session_wait
where  sid in
       (
       select sid
       from   v$session vs
       ,      v$process vp
       ,      dba_datapump_sessions dp
       where  vp.addr  = vs.paddr(+)
       and    vs.saddr = dp.saddr
       )

Show current sessions that perform a lot of hard parses

set pages 1000 lines 1000
col username for a12
select vss.sid
,      vs.username
,      vsn.name
,      vss.value
,      round((sysdate-vs.logon_time)*24) hours_connected
from   v$sesstat  vss
,      v$statname vsn
,      v$session  vs
where  1=1
and    vss.sid        = vs.sid
and    vss.statistic# = vsn.statistic#
and    vss.value      > 0
and    b.name         like '%parse count%'

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;