Hi, My name is Jimmy Sam. If you need DBA help, please email to jimmy_sam001@yahoo.com for further discussion. Thanks.

Kill an Oracle RAC Session

with my_session as (
select '1' sid, '39576' serial# from dual
)
select 
'-- session_id(sid)=' || gv.sid || ' serial#=' || gv.serial# || ' status=' || gv.status ||
' instance_id=' ||gv.inst_id || ' ' 
kill_session 
from gv$session gv, my_session c
where gv.sid=c.sid and gv.serial#=c.serial#
union all 
select 'alter system kill session '''|| gv.sid || ','|| gv.serial#|| ',@'||inst_id||''';'  kill_session
from gv$session gv, my_session c 
where
gv.sid=c.sid and gv.serial#=c.serial#
union all 
select 'alter system kill session '''|| gv.sid || ','|| gv.serial#|| ',@'||inst_id||''' immediate;'  kill_session
from gv$session gv, my_session c 
where
gv.sid=c.sid and gv.serial#=c.serial#;


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