usefull query for database

set lines 100 pages 999
col ID format a15

select username

,      sid || ',' || serial# "ID"
,      status
,      last_call_et "Last Activity"
from   v$session
where  username is not null
order by status desc
,        last_call_et desc
/

Time since last user activity

set lines 100 pages 999

select username
,      floor(last_call_et / 60) "Minutes"
,      status
from   v$session
where  username is not null
order by last_call_et
/

Sessions sorted by logon time
 

set lines 100 pages 999

col ID               format a15
col osuser        format a15
col login_time format a14
select   username
,           osuser
,           sid || ',' || serial# "ID"
,           status
,           to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
,           last_call_et
from    v$session
where  username is not null
order   by login_time
/
 

Show user info including os pid

col "SID/SERIAL" format a10

col username format a15
col osuser format a15
col program format a40
select   s.sid || ',' || s.serial# "SID/SERIAL"
,           s.username
,           s.osuser
,           p.spid "OS PID"
,           s.program
from    v$session s,     v$process p
Where s.paddr = p.addr
order   by to_number(p.spid)
/

Show a users current sql

Select sql_text

from   v$sqlarea
where  (address, hash_value) in
(select sql_address, sql_hash_value
        from v$session
        where username like '&username')
/

 

Session status associated with the specified os process id

select   s.username

,           s.sid
,           s.serial#
,           p.spid
,           last_call_et
,           status
from    V$SESSION s,   V$PROCESS p
where  s.PADDR = p.ADDR
and      p.spid='&pid'
/

 

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

 

Display any long operations

set lines 100 pages 999

col username format a15
col message format a40
col remaining format 9999

select   username
,           to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
,           time_remaining remaining
,           message
from    v$session_longops
where  time_remaining = 0
order by time_remaining desc
/

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  sess.username is not null
and      sess.sid = stat.sid
and      stat.statistic# = sn.statistic#
and      sn.name = 'opened cursors current'
order by value

/