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

set lines 1000 pages 1000
col sid_serial for a10
col username   for a15
col osuser     for a10
col spid       for a10
col module     for a35
col program    for a25
col tablespace for a10

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
/

or

SET PAGESIZE 50
SET LINESIZE 300
 
COLUMN tablespace FORMAT A20
COLUMN temp_size FORMAT A20
COLUMN sid_serial FORMAT A20
COLUMN username FORMAT A20
COLUMN program FORMAT A50

SELECT b.tablespace,
       ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
       a.inst_id as Instance,
       a.sid||','||a.serial# AS sid_serial,
       NVL(a.username, '(oracle)') AS username,
       a.program,
       a.status,
       a.sql_id
FROM   gv$session a,
       gv$sort_usage b,
       gv$parameter p
WHERE  p.name  = 'db_block_size'
AND    a.saddr = b.session_addr
AND    a.inst_id=b.inst_id
AND    a.inst_id=p.inst_id
ORDER BY b.tablespace, b.blocks
/

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'
       );

Top 10 SQL statements

Lists all SQL statements in the library cache with CPU time > &1 &1 % of the total of all statements

define CPUTimePct = '&1'
@sqlplus_settings
@nls_settings
@header_title 155 DC 'All statements in the library cache with a total CPU time >= &CPUTimePct % of all statements together'

COLUMN cpu_time_total       FORMAT      9G999G990 HEADING "CPU time|total"
COLUMN sql_text             FORMAT            A60 HEADING "Sql text"
COLUMN pct                  FORMAT          990D9 HEADING "%"
COLUMN buffer_gets_per_exec FORMAT 99G999G999G999 HEADING "Buffer gets|per exec"
COLUMN wait_per_exec        FORMAT      999G990D9 HEADING "Wait time|per exec"
COLUMN cpu_per_exec         FORMAT      999G990D9 HEADING "CPU time|per exec"
COLUMN disk_reads_per_exec  FORMAT    999G999G999 HEADING "Disk reads|per exec"
COLUMN executions           FORMAT     99G999G999 HEADING "Executions"
COLUMN sql_id               FORMAT            A13 HEADING "Sql id"

SET FEEDBACK  ON
SET ARRAYSIZE 1
SET LONG      2000
SET RECSEP    WRAPPED
SET PAGESIZE  100

WITH total_cpu_time
  AS ( SELECT sum(cpu_time) total_cpu FROM v$sqlarea )
SELECT cpu_time/1000000                                                    cpu_time_total,
       (cpu_time*100)/total_cpu                                            pct,
       sql_text,
       executions,
       cpu_time/(decode(executions,0,1,executions)*1000000)                cpu_per_exec,
       (elapsed_time-cpu_time)/(decode(executions,0,1,executions)*1000000) wait_per_exec,
       buffer_gets/decode(executions,0,1,executions)                       buffer_gets_per_exec,
       disk_reads/decode(executions,0,1,executions)                        disk_reads_per_exec,
       sql_id
  FROM v$sqlarea,
       total_cpu_time
 WHERE cpu_time >= &CPUTimePct*total_cpu/100
 ORDER BY cpu_time desc;

undefine CPUTimePct

SET ARRAYSIZE 20
SET LONG      200

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 using Oracle standard script

Run this if never run before (to create needed tables)

@?/rdbms/admin/catblock

then to show locking processes in a tree structure (non indented process is blocking indented sessions)

@?/rdbms/admin/utllockt

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 blocking sessions with lock type

select nvl(s.username,'Internal')         username
,      nvl(s.terminal,'None')             terminal
,      l.sid||','||s.serial#              killer
,      u1.name||'.'||substr(t1.name,1,20) tab
,      decode ( l.lmode
              ,1, 'No Lock'
              ,2, 'Row Share'
              ,3, 'Row Exclusive'
              ,4, 'Share'
              ,5, 'Share Row Exclusive'
              ,6, 'Exclusive'
                , null)                   lmode
,      decode ( l.request
              ,1, 'No Lock'
              ,2, 'Row Share'
              ,3, 'Row Exclusive'
              ,4, 'Share'
              ,5, 'Share Row Exclusive'
              ,6, 'Exclusive'
                , null)                   request
from  v$lock    l
,     v$session s
,     sys.user$ u1
,     sys.obj$  t1
where l.sid    = s.sid
and   t1.obj#  = decode(l.id2,0,l.id1,l.id2)
and   u1.user# = t1.owner#
and   s.type  != 'BACKGROUND'
and   S.sid   in ( select blocking_session from v$session where blocking_session is not null )
order by 1,2,5
/

Blocking sessions in tree layout

from www.guyharrison.net

set wrap off
column sid format a8
column object_name format a20
column sql_text format a150
set echo on 
WITH sessions AS
   (SELECT /*+materialize*/
           sid, blocking_session, row_wait_obj#, sql_id
      FROM v$session)
SELECT LPAD('  ', LEVEL ) || sid sid, object_name,
       substr(sql_text,1,240) sql_text
  FROM sessions s
  LEFT OUTER JOIN dba_objects
       ON (object_id = row_wait_obj#)
  LEFT OUTER JOIN v$sql
       USING (sql_id)
 WHERE sid IN (SELECT blocking_session FROM sessions)
    OR blocking_session IS NOT NULL
 CONNECT BY PRIOR sid = blocking_session
 START WITH blocking_session IS NULL
/

... and prepare the killer statement

set wrap off lines 2000 echo on
column sid for     a8
column username    for a12
column terminal    for a12
column object_name for a20
column sql_text    for a150
column killer      for a60
with sessions as
    (
    select /*+materialize*/ sid         sid
    ,      nvl(username,'Internal')     username
    ,      serial#                      serial#
    ,      nvl(terminal,'None')         terminal
    ,      blocking_session             blocking_session
    ,      row_wait_obj#                row_wait_obj#
    ,      sql_id                       sql_id
    ,      'alter system disconnect session '''|| sid || ',' || serial# || ''' immediate;' killer
    from   v$session
    )
select lpad('   ', level)||sid          sid
,      object_name                      object_name
,      substr(sql_text,1,240)           sql_text
,      decode(level,1,s.killer,null)    killer
from   sessions s
left   outer join dba_objects on    (object_id = row_wait_obj#)
left   outer join v$sql       using (sql_id)
where  sid in (select blocking_session from sessions)
or     blocking_session       is not null
connect by prior sid          = blocking_session
start  with blocking_session  is null
/

Show all ddl locks in the system

set lines 1000
col  object for a40
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;

Show all locks, internal also

select nvl(b.username,'SYS') username
,      session_id
,      lock_type
,      mode_held
,      mode_requested
,      lock_id1
,      lock_id2
from   sys.dba_lock_internal a
,      sys.v_$session        b
where  1=1
and    a.session_id = b.sid
and    b.username   like '%&username%'
and    b.sid        = &session_id
/

Show object locks

set linesize 150;
set head on;
col sid_serial form a13
col ora_user for a15;
col object_name for a35;
col object_type for a10;
col lock_mode for a15;
col last_ddl for a8;
col status for a10;
break on sid_serial;
SELECT l.session_id||','||v.serial# sid_serial,
       l.ORACLE_USERNAME ora_user,
       o.object_name, 
       o.object_type, 
       DECODE(l.locked_mode,
          0, 'None',
          1, 'Null',
          2, 'Row-S (SS)',
          3, 'Row-X (SX)',
          4, 'Share',
          5, 'S/Row-X (SSX)',
          6, 'Exclusive', 
          TO_CHAR(l.locked_mode)
       ) lock_mode,
       o.status, 
       to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
FROM dba_objects o, gv$locked_object l, v$session v
WHERE o.object_id = l.object_id
      and l.SESSION_ID=v.sid
order by 2,3;

Generate kill statement for ddl locking sessions

 select 'alter system disconnect 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 serveroutput on
set feedback off
column username format a20
column sql_text format a55 word_wrapped
begin
    dbms_output.enable(null);

    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;

Show SQL statements generating the high number of cursors

Use SID from query above.

select sid
,      sql_text
,      count('e') open_crsrs
,      user_name
from   v$open_cursor
where  1=1
and    sid = &SID
group  by sid
,      sql_text
,      user_name
order  by 3;

Show current setting for max open cursors and highest number used since startup

select max(a.value) highest_open_crsrs
,      p.value      max_open_crsrs
from   v$sesstat   a
,      v$statname  b
,      v$parameter p
where  1=1
and    a.statistic# = b.statistic#
and    b.name       = 'opened cursors current'
and    p.name       = 'open_cursors'
group  by p.value;