Difference between revisions of "Database sessions"

Revision as of 20:21, 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'
       );

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;

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

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;