DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Tuesday, April 7, 2009

Show the top 10 longest-active user sessions

/* top10active.sql
shows the top 10 longest-active user sessions
col osuser format a10 trunc
col LastCallET format 99,999
col sid format 9999
col spid formar 999999
col username format a10 trunc
col uprogram format a25 trunc
col machine format a10 trunc
set linesize 132
set verify off
select * from (
select to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.sid, s.status,
floor(last_call_et/60) "LastCallET",
s.username, s.osuser,
p.spid, s.module || ' - ' || s.program uprogram,
s.machine, s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
and s.type = 'USER'
and module is not null
and s.status = 'ACTIVE'
order by 4 desc)
where rownum < 11;

Currently Active Sessions

/* active.sql
show info about active user sessions */

set linesize 200

col sid format 999
col username format a8 trunc
col osuser format a10 trunc
col logged_on format a13 head LOGON_TIME
col machine format a17 trunc
col command format A15
--col program format a20 trunc
col proginfo format a25 trunc
set verify off
select s.sid, s.status,s.username,s.osuser,s.machine,s.sql_hash_value,
1,'Create table' , 2,'Insert',
3,'Select' , 6,'Update',
7,'Delete' , 9,'Create index',
10,'Drop index' ,11,'Alter index',
12,'Drop table' ,13,'Create seq',
14,'Alter sequence' ,15,'Alter table',
16,'Drop sequ.' ,17,'Grant',
19,'Create syn.' ,20,'Drop synonym',
21,'Create view' ,22,'Drop view',
23,'Validate index' ,24,'Create procedure',
25,'Alter procedure' ,26,'Lock table',
42,'Alter session' ,44,'Commit',
45,'Rollback' ,46,'Savepoint',
47,'PL/SQL Exec' ,48,'Set Transaction',
60,'Alter trigger' ,62,'Analyse Table',
63,'Analyse index' ,71,'Create Snapshot Log',
72,'Alter Snapshot Log' ,73,'Drop Snapshot Log',
74,'Create Snapshot' ,75,'Alter Snapshot',
76,'drop Snapshot' ,85,'Truncate table',
0,'No command', '? : '||s.command) command
,s.process, p.spid,
to_char(logon_time,'mm/dd hh:miAM') logged_on
--s.module || ' - ' || s.program proginfo
from v$session s, v$process p
where s.username is not null
and s.paddr = p.addr
and s.status='ACTIVE'