敗家貓

os:redhat 4.3

oracle:9iR2 

#!/bin/sh
#定義環境變數
START_TIME=`date`
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/9.2.0
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
ORACLE_DOC=$ORACLE_BASE/doc
LD_LIBRARY_PATH=$ORACLE_HOME/lib
SHLIB_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/cts/lib:/lib
PATH=$ORACLE_HOME/bin:$PATH
NLS_LANG=AMERICAN_AMERICA.UTF8
TNS_ADMIN=$ORACLE_HOME/network/admino
#定義oracle相關的變數
ORACLE_SID=binhu
CHECK_TIME= 259200

SPID_SPOOL_SQL=time_spid.sql
SPID_TEMP_FILE=temp.spool
SID_SPOOL_SQL=time_sid.sql
SID_TEMP_FILE=temp_sid.spool
LOG_FILE=/home/oracle/script/$ORACLE_SID/time_log


export ORACLE_BASE ORACLE_HOME ORA_NLS33 ORACLE_DOC PATH SHLIB_PATH LD_LIBRARY_PATH TNS_ADMIN ORACLE_SID NLS_LANG CHECK_TIME ORACLE_SID SPID_SPOOL_SQL SPID_TEMP_FILE SID_SPOOL_SQL SID_TEMP_FILE LOG_FILE MailToUser

echo "start time ==> $START_TIME" > $LOG_FILE


#取得沒有動作超過3天的spid
echo "
set echo off;
set heading off;
set feedback off;
set pagesize 0;
spool $SPID_TEMP_FILE;
select spid from v\$session a , v\$process b where a.username is not null and a.last_call_et > $CHECK_TIME and a.paddr = b.addr;
spool off;
exit;
" > /home/oracle/script/$ORACLE_SID/$SPID_SPOOL_SQL

echo  "===== start spid sql ====  " >> $LOG_FILE
cat /home/oracle/script/$ORACLE_SID/$SPID_SPOOL_SQL >> $LOG_FILE
echo  "===== end spid sql ====  " >> $LOG_FILE

/home/oracle/script/$ORACLE_SID/$SPID_SPOOL_SQL

$ORACLE_HOME/bin/sqlplus 'system/oracle' @/home/oracle/script/$ORACLE_SID/$SPID_SPOOL_SQL
rm -rf /home/oracle/script/$ORACLE_SID/$SPID_SPOOL_SQL

#取得sid,serial的資料
echo "
set echo off;
set heading off;
set feedback off;
set pagesize 0;
spool $SID_TEMP_FILE;
select sid||','||serial# from v\$session where username is not null and last_call_et > $CHECK_TIME;
spool off;
exit;
"> /home/oracle/script/$ORACLE_SID/$SID_SPOOL_SQL

echo  "===== start sid,serial sql ====  " >> $LOG_FILE
cat /home/oracle/script/$ORACLE_SID/$SID_SPOOL_SQL >> $LOG_FILE
echo  "===== end sid,serial sql ====  " >> $LOG_FILE

$ORACLE_HOME/bin/sqlplus 'system/oracle' @/home/oracle/script/$ORACLE_SID/$SID_SPOOL_SQL
rm -rf /home/oracle/script/$ORACLE_SID/$SID_SPOOL_SQL

#刪除session
SIDLIST=`cat /home/oracle/script/$ORACLE_SID/$SID_TEMP_FILE`
rm -rf /home/oracle/script/$ORACLE_SID/$SID_TEMP_FILE
if [ "$SIDLIST" != "" ]; then
for sids in $SIDLIST
do
 echo "alter system kill session '$sids' immediate;
       exit;" > run_temp.sql
 $ORACLE_HOME/bin/sqlplus 'system/oracle' @/home/oracle/script/$ORACLE_SID/run_temp.sql
 echo "alter system kill session '$sids' immediate;" >> $LOG_FILE
done
fi
rm -rf /home/oracle/script/$ORACLE_SID/run_temp.sql

#刪除session
SPIDLIST=`cat /home/oracle/script/$ORACLE_SID/$SPID_TEMP_FILE`
rm -rf /home/oracle/script/$ORACLE_SID/$SPID_TEMP_FILE
if [ "$SPIDLIST" != "" ]; then
for spids in $SPIDLIST
do
  kill -9  $spids
  echo "kill -9 $spids" >> $LOG_FILE
done
fi

cat $LOG_FILE

rm -f $LOG_FILE