I am in the process of putting my notes into web pages, please keep checking in later for more contents

Oracle Blocking Session

select 'SID ' || blocker.sid || ' running ' || blocker.program || ' from machine ' || blocker.machine || 
       ' logged in as ' || blocker.username || ' has been blocking SID ' || blocked.sid ||
       ' for ' || blocked.last_call_et || ' seconds.' "Message", blocked.last_call_et "Seconds Blocked"
from v$session blocked
inner join v$session blocker on blocker.sid = blocked.blocking_session
order by blocked.blocking_session;


Sessions Stats

set pagesize 3000
set linesize 350 trimspool on

-- select username,machine,status,count(*) from v$session where type='USER' group by username,machine,status;

select username,machine,status,count(*) cnt from v$session where type='USER' group by username,machine,status having count(*) > 50 order by cnt desc, status,machine,username;

select status,type,count(*) from v$session where type='USER' group by status,type;

select username,machine,status,count(*) cnt from v$session where type='USER' group by username,machine,status having count(*) > 50 order by cnt desc, status,machine,username;

select username,machine,status,count(*) cnt from v$session where type='USER' and status='ACTIVE' group by username,machine,status having count(*) > 5 order by cnt desc, status,machine,username;
select username,machine,status,count(*) cnt from v$session where type='USER' and status='INACTIVE' group by username,machine,status having count(*) > 5 order by cnt desc, status,machine,username;

Home   Oracle