# 1. 查看被取消的会话
SQL> select
s.sid,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.status,
s.last_call_et
from v$session s
where s.username is not null
order by s.last_call_et desc;

SID SERIAL# USERNAME OSUSER MACHINE PROGRAM STATUS LAST_CALL_ET
———- ———- ———- ——— ———- —————– ——– ————
150 1234 SCOTT oracle dbserver sqlplus@dbserver ACTIVE 300
160 5678 HR oracle dbserver sqlplus@dbserver INACTIVE 600

# 2. 查看长时间运行的SQL
SQL> select
s.sid,
s.serial#,
s.username,
s.sql_id,
sq.sql_text,
s.last_call_et
from v$session s
join v$sql sq on s.sql_id = sq.sql_id
where s.last_call_et > 300
and s.username is not null;

SID SERIAL# USERNAME SQL_ID SQL_TEXT LAST_CALL_ET
———- ———- ———- ————- ————————————————— ————
150 1234 SCOTT 7h3g8k2m9p1q update emp set sal = sal * 1.1 where deptno = 10 300

# 3. 查看长时间操作
SQL> select
sid,
serial#,
opname,
target,
sofar,
totalwork,
time_remaining
from v$session_longops
where time_remaining > 0;

SID SERIAL# OPNAME TARGET SOFAR TOTALWORK TIME_REMAINING
———- ———- —————————— —————————— ———- ———- ————–
150 1234 Table Scan SCOTT.EMP 5000 10000 60

# 4. 查看会话等待事件
SQL> select
s.sid,
s.serial#,
s.username,
s.event,
s.seconds_in_wait,
s.wait_class
from v$session s
where s.username is not null
and s.status = ‘ACTIVE’;

SID SERIAL# USERNAME EVENT SECONDS_IN_WAIT WAIT_CLASS
———- ———- ———- —————————— ————— ———-
150 1234 SCOTT db file sequential read 5 User I/O

# 5. 查看事务信息
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.saddr = t.ses_addr
where s.username is not null;

SID SERIAL# USERNAME START_TIME STATUS USED_UBLK
———- ———- ———- ——————– ——– ———-
150 1234 SCOTT 03/31/26 10:00:00 ACTIVE 100

# 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
——————– —————– ————– ————— ————
0002000A00012345 RECOVERING 50 100 50

# 7. 查看SQL执行计划
SQL> select * from table(dbms_xplan.display_cursor(‘7h3g8k2m9p1q’));

PLAN_TABLE_OUTPUT
—————————————————————-
SQL_ID 7h3g8k2m9p1q, child number 0
————————————-
update emp set sal = sal * 1.1 where deptno = 10

Plan hash value: 2325252947

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | UPDATE STATEMENT | | | | 3 (100)| |
| 1 | UPDATE | EMP | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 5 | 35 | 3 (0)| 00:00:01 |
—————————————————————————

# 8. 查看会话统计信息
SQL> select
s.sid,
s.serial#,
s.username,
sn.name,
ss.value
from v$session s
join v$sesstat ss on s.sid = ss.sid
join v$statname sn on ss.statistic# = sn.statistic#
where s.sid = 150
and sn.name like ‘%CPU%’;

SID SERIAL# USERNAME NAME VALUE
———- ———- ———- ————————– ———-
150 1234 SCOTT CPU used by this session 1000
150 1234 SCOTT CPU used when call started 500

# 1. 设置SQL超时
SQL> alter session set sql_trace = true;

Session altered.

# 2. 设置会话超时
SQL> alter profile default limit
connect_time 60
idle_time 30;

Profile altered.

# 3. 设置资源限制
SQL> alter profile default limit
cpu_per_session 10000
logical_reads_per_session 100000;

Profile altered.

# 4. 查看资源限制
SQL> select resource_name, limit
from dba_profiles
where profile = ‘DEFAULT’;

RESOURCE_NAME LIMIT
——————————– ———-
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION 10000
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION 100000
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME 30
CONNECT_TIME 60

# 5. 设置客户端超时
SQL> alter system set open_cursors = 300 scope=both;

System altered.

# 6. 查看客户端超时设置
SQL> show parameter open_cursors;

NAME TYPE VALUE
———————————— ———– ——————————
open_cursors integer 300

# 7. 设置分布式事务超时
SQL> alter system set distributed_lock_timeout = 300 scope=both;

System altered.

# 8. 查看分布式事务超时设置
SQL> show parameter distributed_lock_timeout;

NAME TYPE VALUE
———————————— ———– ——————————
distributed_lock_timeout integer 300

# 1. 创建ORA-01013错误监控脚本
$ vi /home/oracle/scripts/monitor_ora01013.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-01013错误监控脚本

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/ora01013_errors.log”
EMAIL=”admin@fgedu.net.cn”

# 检查ORA-01013错误
check_ora01013_errors() {
local last_check_file=”/home/oracle/scripts/last_ora01013_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-01013错误
local ora_errors=$(grep “ORA-01013” /oracle/app/oracle/diag/rdbms/fgedudb/FGEDUDB/trace/alert_FGEDUDB.log | tail -10)

if [ -n “$ora_errors” ]; then
echo “Found ORA-01013 errors:” >> “$ERROR_LOG”
echo “$ora_errors” >> “$ERROR_LOG”
echo “Timestamp: $(date)” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”

# 发送告警邮件
echo “ORA-01013 error detected: user requested cancel” | mail -s “ORA-01013 Alert” “$EMAIL”

# 分析取消操作
analyze_cancel_operations
fi

# 更新最后检查时间
echo “$current_time” > “$last_check_file”
}

# 分析取消操作
analyze_cancel_operations() {
# 查询长时间运行的SQL
local long_sql=$(sqlplus -s / as sysdba < 300
and username is not null;
exit
EOF
)

echo “Long Running SQL:” >> “$ERROR_LOG”
echo “$long_sql” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
}

# 主函数
main() {
check_ora01013_errors

echo “ORA-01013 error monitoring completed at $(date)” >> “$ERROR_LOG”
}

# 执行主函数
main

# 2. 设置脚本权限
$ chmod +x /home/oracle/scripts/monitor_ora01013.sh

# 3. 测试脚本
$ /home/oracle/scripts/monitor_ora01013.sh

# 4. 设置定期监控
$ crontab -e

# 每小时检查ORA-01013错误
0 * * * * /home/oracle/scripts/monitor_ora01013.sh >> /home/oracle/scripts/monitor_ora01013.log 2>&1

# 1. 创建取消操作监控视图
SQL> create or replace view cancel_operation_monitor as
select
s.sid,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.status,
s.last_call_et,
s.sql_id,
sq.sql_text,
s.event,
s.seconds_in_wait
from v$session s
left join v$sql sq on s.sql_id = sq.sql_id
where s.username is not null
and s.last_call_et > 60;

View created.

# 2. 查询取消操作监控信息
SQL> select * from cancel_operation_monitor where rownum <= 10;

SID SERIAL# USERNAME OSUSER MACHINE PROGRAM STATUS LAST_CALL_ET SQL_ID SQL_TEXT EVENT SECONDS_IN_WAIT
———- ———- ———- ——— ———- —————– ——– ———— ————- ————————————————— —————————— —————
150 1234 SCOTT oracle dbserver sqlplus@dbserver ACTIVE 300 7h3g8k2m9p1q update emp set sal = sal * 1.1 where deptno = 10 db file sequential read 5

# 3. 创建长时间操作监控视图
SQL> create or replace view long_operation_monitor as
select
sid,
serial#,
opname,
target,
sofar,
totalwork,
time_remaining,
round((sofar / totalwork) * 100, 2) as progress_pct,
start_time,
last_update_time
from v$session_longops
where time_remaining > 0;

View created.

# 4. 查询长时间操作信息
SQL> select * from long_operation_monitor;

SID SERIAL# OPNAME TARGET SOFAR TOTALWORK TIME_REMAINING PROGRESS_PCT START_TIME LAST_UPDATE_TIME
———- ———- —————————— —————————— ———- ———- ————– ———— ——————– —————–
150 1234 Table Scan SCOTT.EMP 5000 10000 60 50 2026-03-31 10:00:00 2026-03-31 10:05:00

# 5. 创建ORA-01013错误告警
SQL> create or replace procedure ora01013_error_alert as
v_cancel_count number;
v_long_sql_count number;
v_long_operation_count number;
begin
— 获取长时间运行的SQL数量
select count(*) into v_long_sql_count
from v$session
where last_call_et > 300
and username is not null;

— 获取长时间操作数量
select count(*) into v_long_operation_count
from v$session_longops
where time_remaining > 0;

dbms_output.put_line(‘Long running SQL (>5min): ‘ || v_long_sql_count);
dbms_output.put_line(‘Long operations: ‘ || v_long_operation_count);

if v_long_sql_count > 0 then
dbms_output.put_line(‘— Long Running SQL —‘);
for rec in (select * from cancel_operation_monitor) loop
dbms_output.put_line(‘SID: ‘ || rec.sid || ‘, User: ‘ || rec.username);
dbms_output.put_line(‘SQL: ‘ || substr(rec.sql_text, 1, 50));
dbms_output.put_line(‘Duration: ‘ || rec.last_call_et || ‘ seconds’);
end loop;

dbms_output.put_line(‘Recommendation: Check if user wants to cancel’);
else
dbms_output.put_line(‘No long running SQL found’);
end if;

if v_long_operation_count > 0 then
dbms_output.put_line(‘— Long Operations —‘);
for rec in (select * from long_operation_monitor) loop
dbms_output.put_line(‘SID: ‘ || rec.sid || ‘, Operation: ‘ || rec.opname);
dbms_output.put_line(‘Progress: ‘ || rec.progress_pct || ‘%’);
dbms_output.put_line(‘Time Remaining: ‘ || rec.time_remaining || ‘ seconds’);
end loop;
end if;
end ora01013_error_alert;
/

Procedure created.

# 6. 执行告警存储过程
SQL> set serveroutput on
SQL> exec ora01013_error_alert;

Long running SQL (>5min): 1
Long operations: 1
— Long Running SQL —
SID: 150, User: SCOTT
SQL: update emp set sal = sal * 1.1 where deptno = 10
Duration: 300 seconds
Recommendation: Check if user wants to cancel
— Long Operations —
SID: 150, Operation: Table Scan
Progress: 50%
Time Remaining: 60 seconds

PL/SQL procedure successfully completed.

# 7. 创建定时任务
SQL> begin
dbms_scheduler.create_job(
job_name => ‘ORA01013_ERROR_ALERT_JOB’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘ORA01013_ERROR_ALERT’,
start_date => systimestamp,
repeat_interval => ‘FREQ=HOURLY;INTERVAL=1’,
enabled => true
);
end;
/

PL/SQL procedure successfully completed.

# 问题现象
SQL> update emp set sal = sal * 1.1 where deptno = 10;
^C
update emp set sal = sal * 1.1 where deptno = 10
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

# 分析步骤

# 1. 查看会话状态
SQL> select sid, serial#, username, status, last_call_et
from v$session
where username = ‘SCOTT’;

SID SERIAL# USERNAME STATUS LAST_CALL_ET
———- ———- ———- ——— ————
150 1234 SCOTT ACTIVE 300

# 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.saddr = t.ses_addr
where s.username = ‘SCOTT’;

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-01013
# 错误信息：user requested cancel of current operation
# 会话状态：ACTIVE
# 事务状态：ACTIVE
# 回滚进度：50%
# 错误原因：用户按下Ctrl+C取消操作

# 5. 解决方案：等待回滚完成
SQL> select count(*)
from v$fast_start_transactions
where state = ‘RECOVERING’;

COUNT(*)
———-
0

# 6. 验证问题解决
SQL> select * from emp where deptno = 10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
———- ———- ——— ———- ——— ———- ———- ———-
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10

# 7. 预防措施
# – 优化长时间运行的SQL
# – 设置合理的超时时间
# – 使用批处理操作
# – 监控长时间操作

# 1. 查看应用程序取消操作
SQL> select
s.sid,
s.serial#,
s.username,
s.program,
s.module,
s.action,
s.client_info
from v$session s
where s.program like ‘%java%’
and s.last_call_et > 300;

SID SERIAL# USERNAME PROGRAM MODULE ACTION CLIENT_INFO
———- ———- ———- —————– ————— ————— —————
160 5678 FGFGFGAPP_USER java@appserver OrderProcess UpdateOrder OrderID:12345

# 2. 查看应用程序执行的SQL
SQL> select
s.sid,
s.serial#,
s.username,
sq.sql_text,
s.last_call_et
from v$session s
join v$sql sq on s.sql_id = sq.sql_id
where s.program like ‘%java%’
and s.last_call_et > 300;

SID SERIAL# USERNAME SQL_TEXT LAST_CALL_ET
———- ———- ———- ————————————————— ————
160 5678 FGFGFGAPP_USER update orders set status = ‘PROCESSED’ where… 300

# 3. 查看应用程序模块信息
SQL> select
s.sid,
s.serial#,
s.username,
s.module,
s.action,
s.client_info
from v$session s
where s.module is not null;

SID SERIAL# USERNAME MODULE ACTION CLIENT_INFO
———- ———- ———- ————— ————— —————
160 5678 FGFGFGAPP_USER OrderProcess UpdateOrder OrderID:12345

# 4. 查看应用程序等待事件
SQL> select
s.sid,
s.serial#,
s.username,
s.event,
s.seconds_in_wait,
s.wait_class
from v$session s
where s.program like ‘%java%’
and s.status = ‘ACTIVE’;

SID SERIAL# USERNAME EVENT SECONDS_IN_WAIT WAIT_CLASS
———- ———- ———- —————————— ————— ———-
160 5678 FGFGFGAPP_USER enq: TX – row lock contention 60 Application

# 5. 查看应用程序锁信息
SQL> select
s.sid,
s.serial#,
s.username,
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.program like ‘%java%’;

SID SERIAL# USERNAME TY LMODE REQUEST OBJECT_NAME
———- ———- ———- — ———- ———- —————-
160 5678 FGFGFGAPP_USER TX 6 0
160 5678 FGFGFGAPP_USER TM 3 0 ORDERS

# 6. 终止应用程序会话
SQL> alter system kill session ‘160,5678’ immediate;

System altered.

# 7. 查看回滚进度
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 75 100 75

# 8. 验证回滚完成
SQL> select count(*)
from v$fast_start_transactions
where state = ‘RECOVERING’;

COUNT(*)
———-
0