数据库杀锁shell脚本分享

#!/bin/bash # 设置 Oracle 环境变量 export ORACLE_SID="ORACLE_SID" export ORACLE_HOME="ORACLE_HOME" export PATH=$ORACLE_HOME/bin:$PATH # 获取脚本所在目录的绝对路径 script_dir=$(dirname "$(realpath "$0")") # 定义临时 SQL 文件路径 temp_sql_file="${script_dir}/kill_sessions.sql" # 定义日志文件路径 log_file="${script_dir}/kill_sessions.log" # 记录开始时间 start_time=$(date +"%Y-%m-%d %H:%M:%S") echo "START TIME: $start_time" >> $log_file # 查询阻塞会话并将结果保存到临时 SQL 文件 sqlplus -s / as sysdba <<EOF > "$temp_sql_file" SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ',@' || inst_id || ''' IMMEDIATE;' AS kill_command FROM gv\$session WHERE (sid, inst_id) IN   (SELECT blocking_session, blocking_instance    FROM gv\$session    WHERE blocking_session IS NOT NULL) AND type = 'USER' AND status = 'INACTIVE'; EXIT; EOF # 查询锁源会话信息并保存到日志文件 sqlplus -s / as sysdba <<EOF >> $log_file 2>&1 SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF SET LINE 300 COL program FOR A16 COL machine FOR A30 COL username FOR A15 COL event FOR A30 SELECT inst_id,        sid,        serial#,        program,        machine,        username,        event,        blocking_instance,        blocking_session,        sql_id,        prev_sql_id,        wait_class FROM   gv\$session WHERE  blocking_session IS NOT NULL; EXIT; EOF # 检查临时文件是否有内容 if [ -s "$temp_sql_file" ]; then   # 使用sqlplus执行临时 SQL 文件   echo "Executing commands from $temp_sql_file..." sqlplus -s / as sysdba <<EOF @"$temp_sql_file" exit; EOF   # 检查命令是否执行成功   if [ $? -eq 0 ]; then     echo "Sessions killed successfully."   else     echo "Failed to kill sessions."   fi else   echo "No blocking sessions found." fi # 删除临时文件 rm "$temp_sql_file" # 记录结束时间 end_time=$(date +"%Y-%m-%d %H:%M:%S") echo "END TIME: $end_time" >> $log_file echo "Script execution completed at $end_time."