Inactive session Maintain Oracle R12

Inactive Session ulazation session:
===================================
select p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,
ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,
round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day,S.SID,S.SERIAL#,S.LAST_CALL_ET
from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg
where s.paddr=p.addr and ss.sid=s.sid
and ss.statistic#=12 and si.sid=s.sid
and bg.paddr(+)=p.addr
and s.status='INACTIVE'
and round((ss.value/100),0) > 10
order by 8;



Active session utlazation session:
=================================

select p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,
ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,
round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day,S.SID,S.SERIAL#
from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg
where s.paddr=p.addr and ss.sid=s.sid
and ss.statistic#=12 and si.sid=s.sid
and bg.paddr(+)=p.addr
and s.status='ACTIVE'
and round((ss.value/100),0) > 10
order by 8;



Long running Jobs:
==================

We can find out long running jobs with the help of the below query:

col username for a20
col message for a50
col remaining for 9999
select username,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started,
time_remaining remaining, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc;


Bulk kill at single query report:
=================================

select 'alter system kill session '||''''||sid||','||serial#||''''||';' from V$session where username='MT' AND status='INACTIVE';





I am going to kill more than 4 hours which all session are inative sessions.


select 'alter system kill session '||''''||sid||','||serial#||''''||';' from V$session where username not in ('SYS',
                    'SYSTEM',
                    'DBSNMP',
                    'TSMSYS',
                    'OUTLN',
                    'EXFSYS',
                    'ORDSYS',
                    'WMSYS',
                    'XDB',
                    'MDSYS'
                   ) AND status='INACTIVE' AND LAST_CALL_ET>14400;


-------------------------------

spool /tmp/inactive.lst


 SELECT
 s.sid,
 s.serial#,
 s.machine,
 to_char(s.logon_time,'mm-dd-yy hh24:mi:ss') Logon_Time,
 s.last_call_et/7200 Last_Call_ET,
 s.action,
 s.module
 FROM
        V$SESSION s,v$PROCESS p
        WHERE s.paddr = p.addr AND
        s.username IS NOT NULL AND
        s.last_call_et/7200 > 1 and
        s.status='INACTIVE' and
        s.module not like 'JDBC%'
        order by logon_time
/
spool off


Find exact what application form getting inactive stage.





select 'alter system kill session '||''''||sid||','||serial#||''''||';' from V$session where username='MT' AND status='INACTIVE';