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 a15
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    vsn.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;
 /

Identify the number of SQL statements in the library cache that are not using bind variables

These SQL statements cause expensive hard parse events in the shared pool

select count('x')                       num_sql
,      sum(decode(executions, 1, 1, 0)) one_use_sql
,      sum(sharable_mem)/1024/1024      meg_used
,      sum(decode ( executions
                  , 1, sharable_mem
                     , 0
                  )
          )/1024/1024                   mb_per
from   v$sqlarea
where  sharable_mem > 0;

List number of open cursors by 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;