Session Related Queries

1. Show all connected users
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

2. Time since last user activity
set lines 100 pages 999
select username, floor(last_call_et / 60) "Minutes", statusfrom v$sessionwhere username is not nullorder by last_call_et

3. Sessions sorted by logon time
set lines 100 pages 999
col ID format a15col 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_etfrom v$sessionwhere username is not nullorder by login_time

4. Show user info including os pid
col "SID/SERIAL" format a10
col username format a15col
osuser format a15col
program format a40
select s.sid ',' s.serial# "SID/SERIAL", s.username, s.osuser, p.spid "OS PID", s.programfrom v$session s, v$process pWhere s.paddr = p.addrorder by to_number(p.spid)

5. Show a users current sql
Select sql_textfrom v$sqlareawhere (address, hash_value) in(select sql_address, sql_hash_value from v$session where username like '&username')

6. Session status associated with the specified os process id
select s.username, s.sid, s.serial#, p.spid, last_call_et, statusfrom V$SESSION s, V$PROCESS pwhere s.PADDR = p.ADDRand p.spid='&pid'

7. All active sql
set feedback offset
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;
/
8. 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, messagefrom v$session_longops
where time_remaining = 0
order by time_remaining desc

9. 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

Copied from http://www.shutdownabort.com/