本文档风哥主要介绍Oracle ORA-00031会话中断相关知识，包括ORA-00031的概念、ORA-00031的常见原因、ORA-00031分析方法、ORA-00031错误分析配置、ORA-00031错误监控、ORA-00031错误故障处理等内容，由风哥教程参考Oracle官方文档Troubleshooting内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 ORA-00031的概念
Oracle ORA-00031是Oracle数据库的会话中断错误，表示试图标记要终止的会话但会话正在执行事务或SQL操作。ORA-00031错误通常发生在使用ALTER SYSTEM KILL SESSION命令终止会话时，会话正在执行事务。ORA-00031错误需要等待会话完成当前操作或使用IMMEDIATE选项强制终止会话。更多视频教程www.fgedu.net.cn
- 会话中断：会话正在执行事务或SQL操作
- 标记终止：会话被标记为要终止
- 事务执行：会话正在执行事务
- 等待完成：需要等待会话完成当前操作
- 强制终止：可以使用IMMEDIATE选项强制终止
1.2 ORA-00031的常见原因
Oracle ORA-00031的常见原因：
- 事务执行中：会话正在执行事务操作
- SQL执行中：会话正在执行SQL语句
- 长时间操作：会话正在执行长时间操作
- 回滚操作：会话正在执行回滚操作
- 分布式事务：会话正在执行分布式事务
- 存储过程执行：会话正在执行存储过程
- 批量操作：会话正在执行批量操作
1.3 ORA-00031分析方法
Oracle ORA-00031分析方法：
- 错误信息分析：分析错误信息含义
- 会话状态分析：分析会话当前状态
- 事务分析：分析会话执行的事务
- SQL分析：分析会话执行的SQL
- 等待事件分析：分析会话等待事件
- 进程分析：分析会话进程状态
Part02-生产环境规划与建议
2.1 ORA-00031错误处理规划
Oracle ORA-00031错误处理规划要点：
– 错误监控：监控ORA-00031错误发生
– 错误记录：记录ORA-00031错误信息
– 错误分析：分析ORA-00031错误原因
– 错误处理：处理ORA-00031错误问题
# 错误监控规划
– 监控工具：选择监控工具
– 监控频率：设置监控频率
– 监控范围：定义监控范围
– 告警配置：配置告警规则
# 错误记录规划
– 记录方式：选择记录方式
– 记录格式：定义记录格式
– 记录保留：设置记录保留时间
– 记录备份：备份错误记录
# 错误分析规划
– 分析方法：选择分析方法
– 分析工具：选择分析工具
– 分析流程：定义分析流程
– 分析报告：生成分析报告
# 错误处理规划
– 处理流程：定义处理流程
– 处理优先级：设置处理优先级
– 处理责任人：指定处理责任人
– 处理验证：验证处理结果
2.2 ORA-00031错误分析工具
Oracle ORA-00031错误分析工具：
- SQL*Plus：命令行工具
- SQL Developer：图形化工具
- Enterprise Manager：企业级管理工具
- V$SESSION：会话信息视图
- V$PROCESS：进程信息视图
- V$TRANSACTION：事务信息视图
– SQL*Plus：快速查询错误信息
– SQL Developer：图形化分析错误
– Enterprise Manager：集中管理错误
– V$SESSION：查看会话信息
– V$PROCESS：查看进程信息
– V$TRANSACTION：查看事务信息
2.3 ORA-00031错误处理最佳实践
Oracle ORA-00031错误处理最佳实践：
- 定期监控：定期监控ORA-00031错误
- 及时处理：及时处理ORA-00031错误
- 文档记录：记录错误处理过程
- 经验积累：积累错误处理经验
- 预防措施：制定预防措施
Part03-生产环境项目实施方案
3.1 ORA-00031错误分析配置
3.1.1 配置会话管理
SQL> select sid, serial#, username, status, last_call_et, sql_id
from v$session
where username is not null
order by last_call_et desc;
SID SERIAL# USERNAME STATUS LAST_CALL_ET SQL_ID
———- ———- ———- ——— ———— ————-
150 1234 SCOTT ACTIVE 300 7h3g8k2m9p1q
160 5678 HR INACTIVE 600
170 9012 FGFGFGAPP_USER ACTIVE 120 8k2m9p1q7h3g
# 2. 查看会话详细信息
SQL> select
s.sid,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.status,
s.last_call_et,
s.sql_id,
s.event
from v$session s
where s.sid = 150;
SID SERIAL# USERNAME OSUSER MACHINE PROGRAM STATUS LAST_CALL_ET SQL_ID EVENT
———- ———- ———- ——— ———- —————– ——– ———— ————- ————————-
150 1234 SCOTT oracle dbserver sqlplus@dbserver ACTIVE 300 7h3g8k2m9p1q db file sequential read
# 3. 查看会话执行的事务
SQL> select
s.sid,
s.serial#,
s.username,
t.start_time,
t.status,
t.used_urec,
t.used_ublk
from v$session s
join v$transaction t on s.saddr = t.ses_addr
where s.sid = 150;
SID SERIAL# USERNAME START_TIME STATUS USED_UREC USED_UBLK
———- ———- ———- ——————– ——– ———- ———-
150 1234 SCOTT 03/31/26 10:00:00 ACTIVE 10000 100
# 4. 查看会话执行的SQL
SQL> select sql_text
from v$sql
where sql_id = ‘7h3g8k2m9p1q’;
SQL_TEXT
————————————————————
update emp set sal = sal * 1.1 where deptno = 10
# 5. 查看会话进程信息
SQL> select
s.sid,
s.serial#,
s.username,
p.spid as os_pid,
p.pid as oracle_pid,
p.background
from v$session s
join v$process p on s.paddr = p.addr
where s.sid = 150;
SID SERIAL# USERNAME OS_PID ORACLE_PID B
———- ———- ———- ——— ———- –
150 1234 SCOTT 12345 20 N
# 6. 查看会话等待事件
SQL> select
s.sid,
s.serial#,
s.event,
s.seconds_in_wait,
s.wait_class
from v$session s
where s.sid = 150;
SID SERIAL# EVENT SECONDS_IN_WAIT WAIT_CLASS
———- ———- —————————— ————— ———-
150 1234 db file sequential read 5 User I/O
# 7. 查看会话锁信息
SQL> select
s.sid,
s.serial#,
l.type,
l.lmode,
l.request,
o.object_name
from v$session s
join v$lock l on s.sid = l.sid
left join dba_objects o on l.id1 = o.object_id
where s.sid = 150;
SID SERIAL# TY LMODE REQUEST OBJECT_NAME
———- ———- — ———- ———- —————-
150 1234 TX 6 0
150 1234 TM 3 0 EMP
# 8. 查看会话回滚进度
SQL> select
s.sid,
s.serial#,
s.username,
r.xid,
r.state,
r.cputime,
r.usn,
r.slt,
r.seq
from v$session s
join v$fast_start_transactions r on s.taddr = r.xid
where s.sid = 150;
SID SERIAL# USERNAME XID STATE CPUTIME USN SLT SEQ
———- ———- ———- —————- ——————– ———- ———- ———- ———-
150 1234 SCOTT 0002000A00012345 RECOVERING 300 2 10 12345678
3.1.2 配置会话终止
SQL> alter system kill session ‘150,1234’;
alter system kill session ‘150,1234’
*
ERROR at line 1:
ORA-00031: session marked for kill
# 2. 查看被标记终止的会话
SQL> select sid, serial#, username, status, server
from v$session
where sid = 150;
SID SERIAL# USERNAME STATUS SERVER
———- ———- ———- ——— ———
150 1234 SCOTT KILLED PSEUDO
# 3. 使用IMMEDIATE选项强制终止
SQL> alter system kill session ‘150,1234’ immediate;
System altered.
# 4. 验证会话已终止
SQL> select sid, serial#, username, status
from v$session
where sid = 150;
no rows selected
# 5. 使用操作系统命令终止进程
$ kill -9 12345
# 6. 查看被终止会话的事务回滚
SQL> select
xid,
state,
undoblocksdone,
undoblockstotal,
(undoblocksdone / undoblockstotal) * 100 as progress_pct
from v$fast_start_transactions;
XID STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PROGRESS_PCT
——————– —————– ————– ————— ————
0002000A00012345 RECOVERING 50 100 50
# 7. 监控回滚进度
SQL> select
xid,
state,
undoblocksdone,
undoblockstotal,
cputime
from v$fast_start_transactions
where state = ‘RECOVERING’;
XID STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME
——————– —————– ————– ————— ———-
0002000A00012345 RECOVERING 75 100 150
# 8. 等待回滚完成
SQL> select count(*)
from v$fast_start_transactions
where state = ‘RECOVERING’;
COUNT(*)
———-
0
3.2 ORA-00031错误监控
3.2.1 配置ORA-00031错误监控脚本
$ vi /home/oracle/scripts/monitor_ora00031.sh
#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# ORA-00031错误监控脚本
export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH
# 定义变量
ERROR_LOG=”/home/oracle/scripts/ora00031_errors.log”
EMAIL=”admin@fgedu.net.cn”
# 检查ORA-00031错误
check_ora00031_errors() {
local last_check_file=”/home/oracle/scripts/last_ora00031_check.txt”
local current_time=$(date +%s)
if [ -f “$last_check_file” ]; then
local last_check=$(cat “$last_check_file”)
local time_diff=$((current_time – last_check))
if [ $time_diff -lt 3600 ]; then
echo “Last check was $time_diff seconds ago, skipping…”
return 0
fi
fi
# 检查告警日志中的ORA-00031错误
local ora_errors=$(grep “ORA-00031” /oracle/app/oracle/diag/rdbms/fgedudb/FGEDUDB/trace/alert_FGEDUDB.log | tail -10)
if [ -n “$ora_errors” ]; then
echo “Found ORA-00031 errors:” >> “$ERROR_LOG”
echo “$ora_errors” >> “$ERROR_LOG”
echo “Timestamp: $(date)” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
# 发送告警邮件
echo “ORA-00031 error detected: session marked for kill” | mail -s “ORA-00031 Alert” “$EMAIL”
# 分析会话错误
analyze_session_errors
fi
# 检查被标记终止的会话
check_killed_sessions
# 更新最后检查时间
echo “$current_time” > “$last_check_file”
}
# 分析会话错误
analyze_session_errors() {
# 查询被标记终止的会话
local killed_sessions=$(sqlplus -s / as sysdba <
EOF
)
echo “Killed Sessions:” >> “$ERROR_LOG”
echo “$killed_sessions” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
}
# 检查被标记终止的会话
check_killed_sessions() {
local killed_count=$(sqlplus -s / as sysdba <
EOF
)
if [ “$killed_count” -gt 0 ]; then
echo “Found $killed_count killed sessions still active” >> “$ERROR_LOG”
# 查询回滚进度
local rollback_progress=$(sqlplus -s / as sysdba <
EOF
)
echo “Rollback Progress:” >> “$ERROR_LOG”
echo “$rollback_progress” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
fi
}
# 主函数
main() {
check_ora00031_errors
echo “ORA-00031 error monitoring completed at $(date)” >> “$ERROR_LOG”
}
# 执行主函数
main
# 2. 设置脚本权限
$ chmod +x /home/oracle/scripts/monitor_ora00031.sh
# 3. 测试脚本
$ /home/oracle/scripts/monitor_ora00031.sh
# 4. 设置定期监控
$ crontab -e
# 每小时检查ORA-00031错误
0 * * * * /home/oracle/scripts/monitor_ora00031.sh >> /home/oracle/scripts/monitor_ora00031.log 2>&1
3.2.2 配置数据库监控
SQL> create or replace view killed_session_monitor as
select
s.sid,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.status,
s.last_call_et,
p.spid as os_pid,
t.start_time as transaction_start,
t.used_ublk as undo_blocks
from v$session s
left join v$process p on s.paddr = p.addr
left join v$transaction t on s.taddr = t.xid
where s.status = ‘KILLED’;
View created.
# 2. 查询被终止会话信息
SQL> select * from killed_session_monitor;
SID SERIAL# USERNAME OSUSER MACHINE PROGRAM STATUS LAST_CALL_ET OS_PID TRANSACTION_START UNDO_BLOCKS
———- ———- ———- ——— ———- —————– ——– ———— ——— ——————– ———–
150 1234 SCOTT oracle dbserver sqlplus@dbserver KILLED 300 12345 03/31/26 10:00:00 100
# 3. 创建回滚进度监控视图
SQL> create or replace view rollback_progress_monitor as
select
xid,
state,
undoblocksdone,
undoblockstotal,
round((undoblocksdone / undoblockstotal) * 100, 2) as progress_pct,
cputime,
(select count(*) from v$session where status = ‘KILLED’) as killed_sessions
from v$fast_start_transactions
where state = ‘RECOVERING’;
View created.
# 4. 查询回滚进度
SQL> select * from rollback_progress_monitor;
XID STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PROGRESS_PCT CPUTIME KILLED_SESSIONS
——————– —————– ————– ————— ———— ———- —————
0002000A00012345 RECOVERING 75 100 75 150 1
# 5. 创建ORA-00031错误告警
SQL> create or replace procedure ora00031_error_alert as
v_killed_count number;
v_rollback_count number;
v_email varchar2(100) := ‘admin@fgedu.net.cn’;
begin
— 获取被标记终止的会话数量
select count(*) into v_killed_count
from v$session
where status = ‘KILLED’;
— 获取正在回滚的事务数量
select count(*) into v_rollback_count
from v$fast_start_transactions
where state = ‘RECOVERING’;
dbms_output.put_line(‘Killed sessions: ‘ || v_killed_count);
dbms_output.put_line(‘Rollback transactions: ‘ || v_rollback_count);
if v_killed_count > 0 then
dbms_output.put_line(‘— Killed Sessions —‘);
for rec in (select * from killed_session_monitor) loop
dbms_output.put_line(‘SID: ‘ || rec.sid || ‘, Serial: ‘ || rec.serial#);
dbms_output.put_line(‘User: ‘ || rec.username);
dbms_output.put_line(‘OS PID: ‘ || rec.os_pid);
dbms_output.put_line(‘Undo Blocks: ‘ || rec.undo_blocks);
end loop;
end if;
if v_rollback_count > 0 then
dbms_output.put_line(‘— Rollback Progress —‘);
for rec in (select * from rollback_progress_monitor) loop
dbms_output.put_line(‘XID: ‘ || rec.xid);
dbms_output.put_line(‘Progress: ‘ || rec.progress_pct || ‘%’);
dbms_output.put_line(‘CPU Time: ‘ || rec.cputime);
end loop;
dbms_output.put_line(‘Recommendation: Wait for rollback to complete’);
else
dbms_output.put_line(‘No active rollbacks’);
end if;
end ora00031_error_alert;
/
Procedure created.
# 6. 执行告警存储过程
SQL> set serveroutput on
SQL> exec ora00031_error_alert;
Killed sessions: 1
Rollback transactions: 1
— Killed Sessions —
SID: 150, Serial: 1234
User: SCOTT
OS PID: 12345
Undo Blocks: 100
— Rollback Progress —
XID: 0002000A00012345
Progress: 75%
CPU Time: 150
Recommendation: Wait for rollback to complete
PL/SQL procedure successfully completed.
# 7. 创建定时任务
SQL> begin
dbms_scheduler.create_job(
job_name => ‘ORA00031_ERROR_ALERT_JOB’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘ORA00031_ERROR_ALERT’,
start_date => systimestamp,
repeat_interval => ‘FREQ=HOURLY;INTERVAL=1’,
enabled => true
);
end;
/
PL/SQL procedure successfully completed.
3.3 ORA-00031错误故障处理
3.3.1 ORA-00031错误处理
SQL> alter system kill session ‘150,1234’;
alter system kill session ‘150,1234’
*
ERROR at line 1:
ORA-00031: session marked for kill
# 分析步骤
# 1. 查看会话状态
SQL> select sid, serial#, username, status, server
from v$session
where sid = 150;
SID SERIAL# USERNAME STATUS SERVER
———- ———- ———- ——— ———
150 1234 SCOTT KILLED PSEUDO
# 2. 查看会话事务
SQL> select
s.sid,
s.serial#,
s.username,
t.start_time,
t.status,
t.used_ublk
from v$session s
join v$transaction t on s.taddr = t.xid
where s.sid = 150;
SID SERIAL# USERNAME START_TIME STATUS USED_UBLK
———- ———- ———- ——————– ——– ———-
150 1234 SCOTT 03/31/26 10:00:00 ACTIVE 100
# 3. 查看回滚进度
SQL> select
xid,
state,
undoblocksdone,
undoblockstotal,
round((undoblocksdone / undoblockstotal) * 100, 2) as progress_pct
from v$fast_start_transactions;
XID STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PROGRESS_PCT
——————– —————– ————– ————— ————
0002000A00012345 RECOVERING 50 100 50
# 4. 分析错误原因
# 错误代码：ORA-00031
# 错误信息：session marked for kill
# 会话状态：KILLED
# 会话服务器：PSEUDO
# 事务状态：ACTIVE
# 回滚进度：50%
# 错误原因：会话正在执行事务，需要等待回滚完成
# 5. 解决方案1：等待回滚完成
SQL> select count(*)
from v$fast_start_transactions
where state = ‘RECOVERING’;
COUNT(*)
———-
0
# 6. 解决方案2：使用IMMEDIATE选项
SQL> alter system kill session ‘150,1234’ immediate;
System altered.
# 7. 解决方案3：使用操作系统命令
$ kill -9 12345
# 8. 预防措施
# – 在终止会话前检查是否有活动事务
# – 使用IMMEDIATE选项强制终止
# – 监控回滚进度
# – 避免在高峰期终止会话
3.3.2 长时间回滚处理
SQL> select
xid,
state,
undoblocksdone,
undoblockstotal,
cputime,
round((undoblocksdone / undoblockstotal) * 100, 2) as progress_pct
from v$fast_start_transactions
where state = ‘RECOVERING’
and cputime > 300;
XID STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME PROGRESS_PCT
——————– —————– ————– ————— ———- ————
0002000A00012345 RECOVERING 5000 10000 600 50
# 2. 估算回滚完成时间
SQL> select
xid,
state,
undoblocksdone,
undoblockstotal,
cputime,
round((undoblockstotal – undoblocksdone) *
(cputime / undoblocksdone) / 60, 2) as estimated_minutes
from v$fast_start_transactions
where state = ‘RECOVERING’;
XID STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL CPUTIME ESTIMATED_MINUTES
——————– —————– ————– ————— ———- —————–
0002000A00012345 RECOVERING 5000 10000 600 10
# 3. 加快回滚速度
SQL> alter system set fast_start_parallel_rollback = high;
System altered.
# 4. 查看并行回滚进程
SQL> select
sid,
serial#,
username,
program,
event
from v$session
where program like ‘%SMON%’;
SID SERIAL# USERNAME PROGRAM EVENT
———- ———- ———- —————– ————————-
1 1 SYS oracle@dbserver parallel recovery slave
# 5. 监控回滚进度
SQL> select
xid,
state,
undoblocksdone,
undoblockstotal,
round((undoblocksdone / undoblockstotal) * 100, 2) as progress_pct
from v$fast_start_transactions
where state = ‘RECOVERING’;
XID STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PROGRESS_PCT
——————– —————– ————– ————— ————
0002000A00012345 RECOVERING 7500 10000 75
# 6. 查看回滚完成
SQL> select count(*)
from v$fast_start_transactions
where state = ‘RECOVERING’;
COUNT(*)
———-
0
# 7. 验证会话已清理
SQL> select sid, serial#, username, status
from v$session
where sid = 150;
no rows selected
# 8. 检查告警日志
$ grep “ORA-00031” /oracle/app/oracle/diag/rdbms/fgedudb/FGEDUDB/trace/alert_FGEDUDB.log
ORA-00031: session marked for kill
SMON: about to recover transaction 0002000A00012345
SMON: recovered transaction 0002000A00012345
Part04-生产案例与实战讲解
4.1 ORA-00031错误分析案例
在生产环境中分析ORA-00031错误的完整案例：
4.1.1 场景描述
某企业生产数据库出现ORA-00031会话中断错误，需要分析错误原因并解决问题。
4.1.2 分析步骤
SQL> alter system kill session ‘160,5678’;
alter system kill session ‘160,5678’
*
ERROR at line 1:
ORA-00031: session marked for kill
# 2. 查看会话状态
SQL> select sid, serial#, username, status, server, last_call_et
from v$session
where sid = 160;
SID SERIAL# USERNAME STATUS SERVER LAST_CALL_ET
———- ———- ———- ——— ——— ————
160 5678 FGFGFGAPP_USER KILLED PSEUDO 600
# 3. 查看会话事务
SQL> select
s.sid,
s.serial#,
s.username,
t.start_time,
t.status,
t.used_ublk,
round(t.used_ublk * 8 / 1024, 2) as undo_mb
from v$session s
join v$transaction t on s.taddr = t.xid
where s.sid = 160;
SID SERIAL# USERNAME START_TIME STATUS USED_UBLK UNDO_MB
———- ———- ———- ——————– ——– ———- ———-
160 5678 FGFGFGAPP_USER 03/31/26 09:00:00 ACTIVE 5000 39.06
# 4. 查看回滚进度
SQL> select
xid,
state,
undoblocksdone,
undoblockstotal,
round((undoblocksdone / undoblockstotal) * 100, 2) as progress_pct,
cputime
from v$fast_start_transactions;
XID STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PROGRESS_PCT CPUTIME
——————– —————– ————– ————— ———— ———-
0003000B00023456 RECOVERING 1000 5000 20 120
# 5. 分析错误原因
# 错误代码：ORA-00031
# 错误信息：session marked for kill
# 会话状态：KILLED
# 事务大小：39.06MB
# 回滚进度：20%
# 错误原因：会话正在执行大事务，需要等待回滚完成
# 6. 解决方案：等待回滚完成
SQL> select
xid,
state,
undoblocksdone,
undoblockstotal,
round((undoblocksdone / undoblockstotal) * 100, 2) as progress_pct
from v$fast_start_transactions;
XID STATE UNDOBLOCKSDONE UNDOBLOCKSTOTAL PROGRESS_PCT
——————– —————– ————– ————— ————
0003000B00023456 RECOVERING 5000 5000 100
# 7. 验证问题解决
SQL> select count(*)
from v$fast_start_transactions
where state = ‘RECOVERING’;
COUNT(*)
———-
0
SQL> select sid, serial#, username, status
from v$session
where sid = 160;
no rows selected
4.2 ORA-00031错误故障处理
在ORA-00031错误故障处理过程中的方法和技巧：
4.2.1 故障处理流程
# 1. 错误识别
# – 监控会话中断错误
# – 检查错误信息
# – 确认错误参数
# 2. 错误分析
# – 查看会话状态
# – 检查事务信息
# – 分析回滚进度
# 3. 错误处理
# – 等待回滚完成
# – 使用IMMEDIATE选项
# – 使用操作系统命令
# 4. 错误预防
# – 检查活动事务
# – 使用IMMEDIATE选项
# – 监控回滚进度
# 示例：ORA-00031错误处理
# 1. 错误识别
SQL> alter system kill session ‘170,9012’;
alter system kill session ‘170,9012’
*
ERROR at line 1:
ORA-00031: session marked for kill
# 2. 错误分析
SQL> select * from killed_session_monitor where sid = 170;
SID SERIAL# USERNAME OSUSER MACHINE PROGRAM STATUS LAST_CALL_ET OS_PID TRANSACTION_START UNDO_BLOCKS
———- ———- ———- ——— ———- —————– ——– ———— ——— ——————– ———–
170 9012 FGFGFGAPP_USER appuser appserver java@appserver KILLED 300 23456 03/31/26 10:00:00 50
# 3. 错误处理
SQL> alter system kill session ‘170,9012’ immediate;
System altered.
# 4. 验证处理结果
SQL> select sid, serial#, username, status
from v$session
where sid = 170;
no rows selected
# 5. 错误预防
# – 在终止会话前检查是否有活动事务
# – 使用IMMEDIATE选项强制终止
# – 监控回滚进度
# – 避免在高峰期终止会话
4.3 ORA-00031错误优化
优化ORA-00031错误处理配置的最佳实践：
4.3.1 优化会话管理
SQL> create or replace procedure optimize_session_management as
v_killed_count number;
v_rollback_count number;
v_long_transaction_count number;
begin
— 获取被标记终止的会话数量
select count(*) into v_killed_count
from v$session
where status = ‘KILLED’;
— 获取正在回滚的事务数量
select count(*) into v_rollback_count
from v$fast_start_transactions
where state = ‘RECOVERING’;
— 获取长时间事务数量
select count(*) into v_long_transaction_count
from v$transaction
where (sysdate – to_date(start_time, ‘MM/DD/YY HH24:MI:SS’)) * 24 * 60 > 30;
dbms_output.put_line(‘Killed sessions: ‘ || v_killed_count);
dbms_output.put_line(‘Rollback transactions: ‘ || v_rollback_count);
dbms_output.put_line(‘Long transactions (>30min): ‘ || v_long_transaction_count);
— 显示被标记终止的会话
if v_killed_count > 0 then
dbms_output.put_line(‘— Killed Sessions —‘);
for rec in (select * from killed_session_monitor) loop
dbms_output.put_line(‘SID: ‘ || rec.sid || ‘, User: ‘ || rec.username);
dbms_output.put_line(‘OS PID: ‘ || rec.os_pid);
dbms_output.put_line(‘Undo Blocks: ‘ || rec.undo_blocks);
end loop;
end if;
— 显示回滚进度
if v_rollback_count > 0 then
dbms_output.put_line(‘— Rollback Progress —‘);
for rec in (select * from rollback_progress_monitor) loop
dbms_output.put_line(‘XID: ‘ || rec.xid);
dbms_output.put_line(‘Progress: ‘ || rec.progress_pct || ‘%’);
end loop;
dbms_output.put_line(‘Recommendation: Wait for rollback to complete’);
end if;
end optimize_session_management;
/
Procedure created.
# 2. 执行优化存储过程
SQL> set serveroutput on
SQL> exec optimize_session_management;
Killed sessions: 1
Rollback transactions: 1
Long transactions (>30min): 0
— Killed Sessions —
SID: 150, User: SCOTT
OS PID: 12345
Undo Blocks: 100
— Rollback Progress —
XID: 0002000A00012345
Progress: 75%
Recommendation: Wait for rollback to complete
PL/SQL procedure successfully completed.
# 3. 创建定时任务
SQL> begin
dbms_scheduler.create_job(
job_name => ‘OPTIMIZE_SESSION_MANAGEMENT_JOB’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘OPTIMIZE_SESSION_MANAGEMENT’,
start_date => systimestamp,
repeat_interval => ‘FREQ=HOURLY;INTERVAL=1’,
enabled => true
);
end;
/
PL/SQL procedure successfully completed.
# 4. 创建安全终止会话存储过程
SQL> create or replace procedure safe_kill_session(
p_sid in number,
p_serial in number,
p_force in varchar2 default ‘N’
) as
v_status varchar2(20);
v_transaction_count number;
begin
— 检查会话状态
select status into v_status
from v$session
where sid = p_sid and serial# = p_serial;
— 检查是否有活动事务
select count(*) into v_transaction_count
from v$transaction t
join v$session s on t.ses_addr = s.saddr
where s.sid = p_sid;
if v_transaction_count > 0 then
dbms_output.put_line(‘Warning: Session has active transaction’);
if p_force = ‘Y’ then
execute immediate ‘alter system kill session ”’ || p_sid || ‘,’ || p_serial || ”’ immediate’;
dbms_output.put_line(‘Session killed with IMMEDIATE option’);
else
dbms_output.put_line(‘Use FORCE=Y to kill session with active transaction’);
end if;
else
execute immediate ‘alter system kill session ”’ || p_sid || ‘,’ || p_serial || ””;
dbms_output.put_line(‘Session killed successfully’);
end if;
exception
when no_data_found then
dbms_output.put_line(‘Error: Session not found’);
end safe_kill_session;
/
Procedure created.
# 5. 测试安全终止会话
SQL> set serveroutput on
SQL> exec safe_kill_session(150, 1234);
Warning: Session has active transaction
Use FORCE=Y to kill session with active transaction
PL/SQL procedure successfully completed.
SQL> exec safe_kill_session(150, 1234, ‘Y’);
Warning: Session has active transaction
Session killed with IMMEDIATE option
PL/SQL procedure successfully completed.
Part05-风哥经验总结与分享
5.1 ORA-00031错误总结
Oracle ORA-00031错误是会话中断错误，具有以下特点：
- 会话中断：会话正在执行事务或SQL操作
- 标记终止：会话被标记为要终止
- 事务执行：会话正在执行事务
- 等待完成：需要等待会话完成当前操作
- 强制终止：可以使用IMMEDIATE选项强制终止
5.2 ORA-00031错误检查清单
Oracle ORA-00031错误检查清单：
- 错误识别：识别错误信息和参数
- 错误分析：分析会话状态和事务
- 错误处理：处理错误问题
- 错误验证：验证处理结果
- 错误预防：制定预防措施
- 错误记录：记录处理过程
5.3 ORA-00031错误工具推荐
Oracle ORA-00031错误工具推荐：
- SQL*Plus：命令行工具
- SQL Developer：图形化工具
- Enterprise Manager：企业级管理工具
- V$SESSION：会话信息视图
- V$PROCESS：进程信息视图
- V$TRANSACTION：事务信息视图
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
