ORACLE KILL INACTIVE SESSIONS SCRIPT

MAIL_ADDR=abc@abc.com
OUTPUT=/db/home/oracle/dbadmin/killed.sql
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.1/db1
export ORACLE_HOME
ORACLE_SID=DSSRPT1
export ORACLE_SID
$ORACLE_HOME/bin/sqlplus -s  " / as sysdba" <<EOF
set head off
set feedback off
spool kill.sql
select 'alter system kill session ''' || sid || ',' || serial# || ',' ||'@' || inst_id || ''' immediate;' from gv\$session where username='OSS_READ' and status<>'active';
spool off
spool killed.sql
set echo on
@@kill.sql
exit
EOF
mail -s "OSS_READ DBLINK INACTIVE SESSIONS ON $ORACLE_SID ARE KILLED" $MAIL_ADDR < $OUTPUT


Every day @5pm UTC time
======================================================================================
00 22 * * * /db/home/oracle/dbadmin/inactive_kill.sh



MAIL_ADDR=manikandan_govindaraj@cable.comcast.com
OUTPUT=/home/oracle/dbadmin/killed.sql
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/tds_db
export ORACLE_HOME
ORACLE_SID=PTDSPRB1
export ORACLE_SID
$ORACLE_HOME/bin/sqlplus -s  " / as sysdba" <<EOF
set head off
set feedback off
spool kill.sql
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ',' ||'@' || s.inst_id || ''' immediate;'   from gv\$session s, v\$process p where p.addr=s.paddr and s.last_call_et > 10800 and s.status='INACTIVE';
spool off
spool killed.sql
set echo on
@@kill.sql
exit
EOF
mail -s "More than 3Hrs Inactive sessions ON $ORACLE_SID are killed" $MAIL_ADDR < $OUTPUT

====================================================================================================
#!/bin/ksh
MAIL_ADDR=manikandan_govindaraj@abc.com
INPUT=/db/home/oracle/dbadmin/kill.sql
OUTPUT=/db/home/oracle/dbadmin/killed.sql
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.1/db1
export ORACLE_HOME
ORACLE_SID=DSSRPT1
export ORACLE_SID
. /db/home/oracle/db.env
$ORACLE_HOME/bin/sqlplus -s  " / as sysdba" <<EOF
set head off
set feedback off
set pages 0
spool ${INPUT}
select 'alter system kill session ''' || sid || ',' || serial# || ',' ||'@' || inst_id || ''' immediate;' from gv\$session where username='OSS_READ' and status<>'active';
spool off
spool ${OUTPUT}
set echo on
@@${INPUT}
spool off
exit
EOF
[ -s ${INPUT} ] && /bin/mail -s "OSS_READ DBLINK INACTIVE SESSIONS ON $ORACLE_SID ARE KILLED" $MAIL_ADDR < ${OUTPUT}


====================================================================================================
MAIL_ADDR=manikandan_govindaraj@abc.com
INPUT=/db/home/oracle/dbadmin/kill.sql
OUTPUT=/home/oracle/dbadmin/killed.sql
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0.3/tds_db
export ORACLE_HOME
ORACLE_SID=PTDSPRB1
export ORACLE_SID
$ORACLE_HOME/bin/sqlplus -s  " / as sysdba" <<EOF
set head off
set feedback off
spool ${INPUT}
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ',' ||'@' || s.inst_id || ''' immediate;'   from gv\$session s, v\$process p where p.addr=s.paddr and s.last_call_et > 10800 and s.status='INACTIVE' and s.USERNAME='RECONAPP';
spool off
spool ${OUTPUT}
set echo on
@@${INPUT}
select to_char(sysdate,'dd/mm/yyyy hh:mi') from dual;
spool off
exit
EOF
[ -s ${INPUT} ] && /bin/mail -s "RECONN inactive sesson >3Hrs on $ORACLE_SID are killed" $MAIL_ADDR < ${OUTPUT}