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 linesize 380 trimspool on pagesize 300

column program format a23
column machine format a23
column username format a14
column cnt format 9,999,999

select c.cnt total_session,b.cnt total_users_sess, a.* from 
(select count(*) single_user_sess,username,machine,status,program,osuser from v$session
where type='USER' and status='INACTIVE'
group by username,machine,status,program,osuser having count(*) > 5
order by single_user_sess desc, status,machine,username) a,
(select count(*) cnt from v$session where type='USER') b,
(select count(*) cnt from v$session) c;



Object Locks And Session Blockers

define MY_TABLE_NAME=XXX
select * from v$locked_object;

select * from dba_tables where table_name like '%&&MY_TABLE_NAME%'

select * from dba_objects where object_name like '%&&MY_TABLE_NAME%' and last_ddl < sysdate -3;

select * from dba_objects where object_name like '%&&MY_TABLE_NAME%' and last_ddl_time < sysdate -3 order by last_ddl_time desc;

select * from v$parameter where name like '%job_queue_processes%';

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.blocking_session,
   b.status,
   b.osuser,
   b.machine,
   b.program,
   b.sql_id,
   t.sql_text,
   t.sql_fulltext,
   a.*
from
   v$locked_object a ,
   v$session b,
   dba_objects c,
   v$sqlarea t
where
   b.sid = a.session_id
and
   a.object_id = c.object_id
and t.sql_id = b.sql_id 
and object_name = '&&MY_TABLE_NAME'
order by object_name
;


Sessions Stats

set pagesize 3000
set linesize 350 trimspool on

select count(*) from v$process;

select count(*) from v$session;

select count(*) from v$session where type='USER';

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

column machine format a32
column program format a32
set linesize 180 trimspool on

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

select machine, program, status, count(*) from v$session where type='USER' 
group by machine,program, status having count(*) > 6;

-- username,machine,status
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