Session

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

/

 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'

/

 

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

/