清除oracle过期死锁会话脚本

#!/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