#!/bin/bash

# by ray

# 2016-06-16

ksfile=/tmp/.kill_session.tmp #kill session 的檔案路徑

killSqlFile=/tmp/.kill_session_temptablespace_highparallel.sql #kill指令碼的路徑

killSqlHisFile=/home/oracle/shell/killSqlHisInfo.txt #被kill的會話資訊

oraUser=**** #oracle的使用者名稱

oraPwd=****** #oracle的密碼

#函式，獲取執行較長時間的sql的資訊

getSqlInfo(){

[ -e ${killSqlFile} ]&& rm -f ${killSqlFile}

[ -e $3 ]&& rm -f $3

sqlplus -s /nolog <<-RAY

conn $1/$2

set termout off;

set echo off;

set feedback off;

set verify off;

set heading off;

set wrap on;

set trimspool on;

set serveroutput on;

set escape on;

set pagesize 50000;

set long 2000000000;

set linesize 300;

spool $3

select distinct b.SID,b.SERIAL\#,b.LAST_CALL_ET,a.sql_id,b.OSUSER,b.MACHINE from v\$sql a,v\$session b,v\$process p where a.SQL_ID=b.SQL_ID and b.PADDR=p.ADDR and b.STATUS=\'ACTIVE\' and b.LAST_CALL_ET>100 and b.MODULE=\'JDBC Thin Client\' order by a.sql_id,B.LAST_CALL_ET desc;

spool off

RAY

#形成kill會話指令碼

awk '$0!=""{print "alter system kill session'\''"$1","$2"'\'';"}' $3 > ${killSqlFile}

#把被kill會話的資訊寫入檔案

echo "#########################################################################################" >> ${killSqlHisFile}

echo "#######################################"`date`"#######################################" >> ${killSqlHisFile}

cat $3 >> ${killSqlHisFile}

}

#執行指令碼的函式

execSQL(){

sqlplus /nolog <<-RAY

conn $1/$2

@$3

RAY

}

#指令碼入口

getSqlInfo ${oraUser} ${oraPwd} ${ksfile}

execSQL ${oraUser} ${oraPwd} ${killSqlFile}

[ -e ${ksfile} ]&& rm -f ${ksfile}

[ -e ${killSqlFile} ]&& rm -f ${killSqlFile}

kill_session_temptablespace_highparallel.sh 查殺執行時間較長的sql

相關文章