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