#!/bin/sh
#this shell script is used to kill the head session
#author colin
#date Sun Sep 26 CST 2010
function print_usage()
{
echo "Usage: kill_session.sh colin/colin "
echo "\t 查找死锁会话开始"
}
function get_pid() #the head locked process id
{
pid=`${SQLPLUS} -S ${ORACLE_USERNAME} <<\EOF
set head off
select v2.spid
from v$session v1, v$process v2
where v1.paddr = v2.addr
and v1.sid in (select v3.SESSION_ID
from v$locked_object v3
where v3.oracle_username = user);
EOF
`
}
function kill_session() #kill the head locked session
{
${SQLPLUS} ${ORACLE_USERNAME} << \EOF
DECLARE
v_sid int;
v_serial int;
BEGIN
select v1.sid, v1.serial#
into v_sid, v_serial
from v$session v1, v$process v2
where v1.paddr = v2.addr
and v1.sid in (select v3.SESSION_ID
from v$locked_object v3
where v3.oracle_username = user);
if v_sid is not null and v_serial is not null then
execute immediate 'alter system kill session ' || '''' || v_sid || ',' ||
v_serial || '''';
end if;
END;
/
EOF
}
work_job()
{
kill_session ${SQLPLUS} ${ORACLE_USERNAME}
get_pid ${SQLPLUS} ${ORACLE_USERNAME}
kill -9 $pid
}
##set the env
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10.2.0
export ORACLE_BASE ORACLE_HOME
SQLPLUS=${ORACLE_HOME}/bin/sqlplus
ORACLE_USERNAME=$1
if [ $# -lt 1 ]; then
print_usage
exit -1
else
echo "Begin Kill the Headsing Session..."
fi
work_job
|
