sess

============== Current Active Session ======================
select distinct last_call_et,username,sid,blocking_session,s.sql_id,event,executions exec,round(((elapsed_time/1000000)/executions),3) ET,
(select object_name from dba_objects where object_id=row_wait_obj#) Object_name,
(select object_type from dba_objects where object_id=row_wait_obj#) obj_type,sql_text
from
v$session s,v$sql q
where
s.sql_id=q.sql_id(+) and
status='ACTIVE'
and username is not null
and username not in ('DESK','SYS')
order by 1 desc;

============== Current InActive Session ======================
select last_call_et,username,module,program,machine,sid,sql_id,event
from
v$session
where status='INACTIVE'
and username is not null
order by 1 desc;

============== Count of sessions ======================
select count(1),username,module,status from
v$session where username is not null
group by username,module,status;

============== Kill Inactive sessions ======================
select 'alter system kill session ''' || sid || ',' || serial# || '''immediate'
from
v$session
where status='INACTIVE'
and username is not null
order by 1 desc;

Leave a Reply