ORACLE – Scripts para controle das Sessoes

 
--“Ver sessões travadas”
select 'ALTER SYSTEM KILL SESSION '|| ''''||sid||','||serial#||',@'||inst_id||''' IMMEDIATE; --'||osuser||' '||client_info||' '||program from gv$session where lockwait is not null;
 
--“Ver quem está travando as sessões”
select vl.block, 'ALTER SYSTEM KILL SESSION '|| ''''||vs.sid||','||vs.serial#||',@'||vs.inst_id||''' IMMEDIATE; -- Usuario:'||osuser||' Mais inf: '||client_info||'Programa:'||program
from gv$session vs
inner join gv$lock vl on vs.inst_id = vl.inst_id and vs.sid = vl.sid
where vl.id1 is not null and vl.id2 is not null and vl.block = 1;
 
--"Usuários com objetos bloqueados"
select distinct 'ALTER SYSTEM KILL SESSION '|| ''''||vs.sid||','||vs.serial#||',@'||vs.inst_id||''' IMMEDIATE; --'||vs.osuser||' '||vs.client_info||' '||vs.program
from gv$session vs
inner join GV$LOCKED_OBJECT vl on vl.session_id = vs.sid and vs.inst_id = vl.inst_id;
 
select * from GV$LOCKED_OBJECT;
 
select 'ALTER SYSTEM KILL SESSION '|| ''''||sid||','||serial#||',@'||inst_id||''' IMMEDIATE; --'||osuser||' '||client_info||' '||program from gv$session where username='RM';
 
select 'ALTER SYSTEM KILL SESSION '|| ''''||sid||','||serial#||',@'||inst_id||''' IMMEDIATE;' from gv$session where username='RM';
 
select count(*) from gv$session where username='RM';
 
select o.sid, osuser, machine, count(*) num_curs
from v$open_cursor o, v$session s
where o.sid=s.sid and OSUSER = 'totvsintegra'
group by o.sid, osuser, machine
order by num_curs desc;
 
select count(*) num_curs
from v$open_cursor o, v$session s
where o.sid=s.sid
--group by o.sid, osuser, machine
order by num_curs desc;
 
select vl.block, 'ALTER SYSTEM KILL SESSION '|| ''''||vs.sid||','||vs.serial#||',@'||vs.inst_id||''' IMMEDIATE;'
from gv$session vs
inner join gv$lock vl on vs.inst_id = vl.inst_id and vs.sid = vl.sid
where vl.id1 is not null and vl.id2 is not null and vl.block = 1;
 
select 'ALTER SYSTEM KILL SESSION '|| ''''||sid||','||serial#||',@'||inst_id||''' IMMEDIATE; --'||osuser||' '||client_info||' '||program from gv$session where lockwait is not null;