TABLESPACE_NAME FILE_NAME SIZE_MB AUT MAX_MB
—————— ——————————————– ——– — ———-
UNDOTBS1 /oracle/app/oracle/oradata/FGEDUDB/undotbs01.dbf 20480 YES 32767

# 2. 查看Undo表空间使用情况
SQL> select
tablespace_name,
round(sum(bytes)/1024/1024, 2) as used_mb,
round(sum(maxbytes)/1024/1024, 2) as max_mb,
round(sum(bytes)/sum(maxbytes)*100, 2) as used_pct
from dba_data_files
where tablespace_name like ‘%UNDO%’
group by tablespace_name;

TABLESPACE_NAME USED_MB MAX_MB USED_PCT
—————— ——– ———- ———-
UNDOTBS1 20480.00 32767.00 62.50

# 3. 查看Undo保留时间
SQL> show parameter undo_retention;

NAME TYPE VALUE
———————————— ———– ——————————
undo_retention integer 900

# 4. 查看Undo统计信息
SQL> select
begin_time,
end_time,
undotsn,
undoblks,
txncount,
maxquerylen,
maxconcurrency,
ssolderrcnt
from v$undostat
where rownum <= 10 order by begin_time desc;

BEGIN_TIME END_TIME UNDOTSN UNDOBLKS TXNCOUNT MAXQUERYLEN MAXCONCURRENCY SSOLDERRCNT
——————– ——————– ———- ———- ———- ———– ————– ———–
2026-03-31 10:00:00 2026-03-31 10:10:00 1 1000 500 600 5 0
2026-03-31 09:50:00 2026-03-31 10:00:00 1 1200 600 500 6 1
2026-03-31 09:40:00 2026-03-31 09:50:00 1 800 400 400 4 0

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

SID SERIAL# USERNAME SQL_ID LAST_CALL_ET SQL_TEXT
———- ———- ———- ————- ———— ————————————————–
150 1234 SCOTT 7h3g8k2m9p1q 1800 select * from large_table where create_date > sysdate-365

# 6. 查看Undo表空间使用趋势
SQL> select
to_char(begin_time, ‘YYYY-MM-DD HH24:MI’) as time,
undoblks,
txncount,
maxquerylen,
ssolderrcnt
from v$undostat
where begin_time > sysdate – 1
order by begin_time;

TIME UNDOBLKS TXNCOUNT MAXQUERYLEN SSOLDERRCNT
—————– ———- ———- ———– ———–
2026-03-31 08:00 1000 500 600 0
2026-03-31 09:00 1200 600 500 1
2026-03-31 10:00 1500 700 400 0

# 7. 查看Undo表空间建议
SQL> select
tablespace_name,
round(required_undo_size/1024/1024, 2) as required_mb,
round(current_undo_size/1024/1024, 2) as current_mb,
round(required_undo_size/current_undo_size*100, 2) as usage_pct
from (
select
tablespace_name,
sum(bytes) as current_undo_size,
(select max(maxquerylen) * max(undoblks/((end_time-begin_time)*24*3600)) * 8192
from v$undostat) as required_undo_size
from dba_data_files
where tablespace_name like ‘%UNDO%’
group by tablespace_name
);

TABLESPACE_NAME REQUIRED_MB CURRENT_MB USAGE_PCT
—————— ———– ———— ———-
UNDOTBS1 5120.00 20480.00 25.00

# 8. 查看Undo保留时间建议
SQL> select
max(maxquerylen) as max_query_len,
max(maxquerylen) + 300 as recommended_retention
from v$undostat;

MAX_QUERY_LEN RECOMMENDED_RETENTION
————- ———————
1800 2100

System altered.

# 2. 验证Undo保留时间
SQL> show parameter undo_retention;

NAME TYPE VALUE
———————————— ———– ——————————
undo_retention integer 1800

# 3. 增加Undo表空间大小
SQL> alter database datafile ‘/oracle/app/oracle/oradata/FGEDUDB/undotbs01.dbf’
resize 30720m;

Database altered.

# 4. 验证Undo表空间大小
SQL> select tablespace_name, file_name, bytes/1024/1024 as size_mb
from dba_data_files
where tablespace_name like ‘%UNDO%’;

TABLESPACE_NAME FILE_NAME SIZE_MB
—————— ——————————————– ———-
UNDOTBS1 /oracle/app/oracle/oradata/FGEDUDB/undotbs01.dbf 30720

# 5. 启用Undo表空间自动扩展
SQL> alter database datafile ‘/oracle/app/oracle/oradata/FGEDUDB/undotbs01.dbf’
autoextend on next 100m maxsize unlimited;

Database altered.

# 6. 验证自动扩展设置
SQL> select tablespace_name, file_name, autoextensible,
increment_by*8192/1024/1024 as increment_mb,
maxbytes/1024/1024 as max_mb
from dba_data_files
where tablespace_name like ‘%UNDO%’;

TABLESPACE_NAME FILE_NAME AUT INCREMENT_MB MAX_MB
—————— ——————————————– — ———— ———-
UNDOTBS1 /oracle/app/oracle/oradata/FGEDUDB/undotbs01.dbf YES 100 32767.98

# 7. 设置Undo表空间保证保留
SQL> alter tablespace undotbs1 retention guarantee;

Tablespace altered.

# 8. 验证Undo表空间保留设置
SQL> select tablespace_name, retention
from dba_tablespaces
where tablespace_name like ‘%UNDO%’;

TABLESPACE_NAME RETENTION
—————— ———–
UNDOTBS1 GUARANTEE

#!/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-01555错误监控脚本

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

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

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

# 发送告警邮件
echo “ORA-01555 error detected: snapshot too old” | mail -s “ORA-01555 Alert” “$EMAIL”

# 分析Undo表空间
analyze_undo_tablespace
fi

# 检查Undo表空间使用情况
check_undo_usage

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

# 分析Undo表空间
analyze_undo_tablespace() {
local undo_info=$(sqlplus -s / as sysdba < exit
EOF
)

echo “Undo Tablespace Info:” >> “$ERROR_LOG”
echo “$undo_info” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”

local undo_stat=$(sqlplus -s / as sysdba < exit
EOF
)

echo “Undo Statistics:” >> “$ERROR_LOG”
echo “$undo_stat” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
}

# 检查Undo表空间使用情况
check_undo_usage() {
local undo_usage=$(sqlplus -s / as sysdba < exit
EOF
)

echo “Undo Tablespace Usage:” >> “$ERROR_LOG”
echo “$undo_usage” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
}

# 主函数
main() {
check_ora01555_errors

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

# 执行主函数
main

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

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

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

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

View created.

# 2. 查询Undo监控信息
SQL> select * from undo_monitor where ssolderrcnt > 0;

TIME UNDOBLKS TXNCOUNT MAXQUERYLEN MAXCONCURRENCY SSOLDERRCNT NOSPACEERRCNT
—————– ———- ———- ———– ————– ———– ————-
2026-03-31 09:00 1200 600 500 6 1 0

# 3. 创建Undo表空间使用监控视图
SQL> create or replace view undo_usage_monitor as
select
tablespace_name,
round(sum(bytes)/1024/1024, 2) as total_mb,
round(sum(maxbytes)/1024/1024, 2) as max_mb,
round(sum(bytes)/sum(maxbytes)*100, 2) as used_pct
from dba_data_files
where tablespace_name like ‘%UNDO%’
group by tablespace_name;

View created.

# 4. 查询Undo表空间使用信息
SQL> select * from undo_usage_monitor;

TABLESPACE_NAME TOTAL_MB MAX_MB USED_PCT
—————— ——— ———- ———-
UNDOTBS1 20480.00 32767.00 62.50

# 5. 创建ORA-01555错误告警
SQL> create or replace procedure ora01555_error_alert as
v_ssolderrcnt number;
v_nospaceerrcnt number;
v_maxquerylen number;
v_undo_retention number;
v_undo_usage_pct number;
begin
— 获取Undo统计信息
select sum(ssolderrcnt), sum(nospaceerrcnt), max(maxquerylen)
into v_ssolderrcnt, v_nospaceerrcnt, v_maxquerylen
from v$undostat
where begin_time > sysdate – 1;

— 获取Undo保留时间
select value into v_undo_retention
from v$parameter
where name = ‘undo_retention’;

— 获取Undo表空间使用率
select round(sum(bytes)/sum(maxbytes)*100, 2)
into v_undo_usage_pct
from dba_data_files
where tablespace_name like ‘%UNDO%’;

dbms_output.put_line(‘Snapshot Too Old Errors: ‘ || v_ssolderrcnt);
dbms_output.put_line(‘No Space Errors: ‘ || v_nospaceerrcnt);
dbms_output.put_line(‘Max Query Length: ‘ || v_maxquerylen || ‘ seconds’);
dbms_output.put_line(‘Undo Retention: ‘ || v_undo_retention || ‘ seconds’);
dbms_output.put_line(‘Undo Tablespace Usage: ‘ || v_undo_usage_pct || ‘%’);

if v_ssolderrcnt > 0 then
dbms_output.put_line(‘— ORA-01555 Errors Detected —‘);
dbms_output.put_line(‘Recommendation: Increase undo_retention or undo tablespace size’);

if v_maxquerylen > v_undo_retention then
dbms_output.put_line(‘Max query length exceeds undo retention’);
dbms_output.put_line(‘Recommended retention: ‘ || (v_maxquerylen + 300) || ‘ seconds’);
end if;

if v_undo_usage_pct > 80 then
dbms_output.put_line(‘Undo tablespace usage is high’);
dbms_output.put_line(‘Recommendation: Increase undo tablespace size’);
end if;
else
dbms_output.put_line(‘No ORA-01555 errors detected’);
end if;
end ora01555_error_alert;
/

Procedure created.

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

Snapshot Too Old Errors: 1
No Space Errors: 0
Max Query Length: 1800 seconds
Undo Retention: 900 seconds
Undo Tablespace Usage: 62.50%
— ORA-01555 Errors Detected —
Recommendation: Increase undo_retention or undo tablespace size
Max query length exceeds undo retention
Recommended retention: 2100 seconds

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

# 分析步骤

# 1. 查看Undo表空间信息
SQL> select tablespace_name, file_name, bytes/1024/1024 as size_mb
from dba_data_files
where tablespace_name like ‘%UNDO%’;

TABLESPACE_NAME FILE_NAME SIZE_MB
—————— ——————————————– ———-
UNDOTBS1 /oracle/app/oracle/oradata/FGEDUDB/undotbs01.dbf 20480

# 2. 查看Undo保留时间
SQL> show parameter undo_retention;

NAME TYPE VALUE
———————————— ———– ——————————
undo_retention integer 900

# 3. 查看Undo统计信息
SQL> select
begin_time,
end_time,
undoblks,
txncount,
maxquerylen,
ssolderrcnt
from v$undostat
where ssolderrcnt > 0
order by begin_time desc;

BEGIN_TIME END_TIME UNDOBLKS TXNCOUNT MAXQUERYLEN SSOLDERRCNT
——————– ——————– ———- ———- ———– ———–
2026-03-31 09:50:00 2026-03-31 10:00:00 1200 600 1800 1

# 4. 查看长时间运行的查询
SQL> select
s.sid,
s.serial#,
s.username,
s.sql_id,
s.last_call_et
from v$session s
where s.last_call_et > 900
and s.username is not null;

SID SERIAL# USERNAME SQL_ID LAST_CALL_ET
———- ———- ———- ————- ————
150 1234 SCOTT 7h3g8k2m9p1q 1800

# 5. 分析错误原因
# 错误代码：ORA-01555
# 错误信息：snapshot too old
# Undo表空间大小：20480MB
# Undo保留时间：900秒
# 最大查询长度：1800秒
# 错误原因：查询执行时间超过Undo保留时间

# 6. 解决方案1：增加Undo保留时间
SQL> alter system set undo_retention = 2100 scope=both;

System altered.

# 7. 解决方案2：增加Undo表空间大小
SQL> alter database datafile ‘/oracle/app/oracle/oradata/FGEDUDB/undotbs01.dbf’
resize 30720m;

Database altered.

# 8. 解决方案3：启用Undo表空间保证保留
SQL> alter tablespace undotbs1 retention guarantee;

Tablespace altered.

# 9. 验证问题解决
SQL> select * from large_table where create_date > sysdate – 365;

1000000 rows selected.

# 10. 预防措施
# – 增加Undo保留时间
# – 增加Undo表空间大小
# – 启用Undo表空间保证保留
# – 优化长时间运行的查询
# – 监控Undo表空间使用情况

TABLESPACE_NAME USED_MB MAX_MB USED_PCT
—————— ——— ———- ———-
UNDOTBS1 20480.00 20480.00 100.00

# 2. 查看Undo表空间自动扩展设置
SQL> select tablespace_name, file_name, autoextensible, maxbytes/1024/1024 as max_mb
from dba_data_files
where tablespace_name like ‘%UNDO%’;

TABLESPACE_NAME FILE_NAME AUT MAX_MB
—————— ——————————————– — ———-
UNDOTBS1 /oracle/app/oracle/oradata/FGEDUDB/undotbs01.dbf NO 0

# 3. 启用Undo表空间自动扩展
SQL> alter database datafile ‘/oracle/app/oracle/oradata/FGEDUDB/undotbs01.dbf’
autoextend on next 100m maxsize 32767m;

Database altered.

# 4. 添加新的Undo数据文件
SQL> alter tablespace undotbs1
add datafile ‘/oracle/app/oracle/oradata/FGEDUDB/undotbs02.dbf’
size 10240m autoextend on next 100m maxsize 32767m;

Tablespace altered.

# 5. 验证Undo表空间配置
SQL> select tablespace_name, file_name, bytes/1024/1024 as size_mb,
autoextensible, maxbytes/1024/1024 as max_mb
from dba_data_files
where tablespace_name like ‘%UNDO%’;

TABLESPACE_NAME FILE_NAME SIZE_MB AUT MAX_MB
—————— ——————————————– ——– — ———-
UNDOTBS1 /oracle/app/oracle/oradata/FGEDUDB/undotbs01.dbf 20480 YES 32767
UNDOTBS1 /oracle/app/oracle/oradata/FGEDUDB/undotbs02.dbf 10240 YES 32767

# 6. 查看Undo表空间使用率
SQL> select
tablespace_name,
round(used_percent, 2) as used_pct
from dba_tablespace_usage_metrics
where tablespace_name like ‘%UNDO%’;

TABLESPACE_NAME USED_PCT
—————— ———-
UNDOTBS1 62.50

# 7. 查看Undo表空间空间警告
SQL> select tablespace_name, status, contents, retention
from dba_tablespaces
where tablespace_name like ‘%UNDO%’;

TABLESPACE_NAME STATUS CONTENTS RETENTION
—————— ——— ——— ———–
UNDOTBS1 ONLINE UNDO GUARANTEE

# 8. 验证Undo表空间正常
SQL> select count(*) from v$rollstat;

COUNT(*)
———-
11