Oracleエラー対応（ORA-01555）：ORA-01555発生時の対処方法について

現役DBAが解説～Oracleエラー対応～

2023.04.18

UNDO表領域関係の大きさが不足している際に、ORA-01555が発生することがあります。

本記事では原因および対処法をご紹介します。

Contents

エラーメッセージ
原因
エラー調査と対応方法
- 3-1 UNDO_RETENTIONの値を超えるSQLの処理時間によるエラー発生
- 3-2 UNDO表領域の容量を超過したことによるエラー発生
最後に

1. エラーメッセージ

ORA-01555: スナップショットが古すぎます:
ロールバック・セグメント番号16、名前"_SYSSMU16_295376492$"が小さすぎます

2. 原因

ORA-01555は、UNDO表領域が必要としていたデータがその後の処理により上書きされてなくなった際に発生します。

＜原因例＞
・UNDO 表領域を超える量の更新が行われた
・SQLの実行時間が長くなった

3. エラー調査と対応

以下コマンドにて、UNDO表領域の大きさと、AUTOEXTENDのステータス、AUTOEXTENDが有効である場合の拡張最大時の大きさを確認します。

SQL>select file_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_data_files where file_name like '%undo%';

FILE_NAME                                　　　　　　　　BYTES/1024/1024 　　 AUT　 　MAXBYTES/1024/1024
----------------------------------------------------------  ------------------------ 　　　------ 　-----------------------------
/app/oracle/oradata/ORCL/undotbs1.dbf                  　　　　200 　　　　YES      　　　　　　　　200

3-1 UNDO_RETENTIONの値を超えるSQLの処理時間によるエラー発生

AUTOEXTENDが有効な際は、TUNED_UNDORETENTIONが有効になります。
そのことにより、TUNED_UNDORETENTIONを過ぎてしまったUNDOデータが上書きされてしまう場合があります。

以下コマンドにて、TUNED_UNDORETENTIONの下限値である初期化パラメータのUNDO_RETENTION、およびALERTLOGに記載されているORA-01555のQuery Durationの値を確認します。

SQL> show parameter undo_retention

NAME                                 　TYPE        　VALUE
------------------------------------ ----------- 　------------------------------
undo_retention                       integer    　 900

CMD>view /app/oracle/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log

※該当エラー箇所を抜粋
>ORA-01555 caused by SQL statement below (SQL ID: 9mpyc83bm12y0, Query Duration=1 sec, SCN: 0x00000000001c624c):

UNDO_RETENTIONの値が900（秒）であり、Query DURATIONの値が1secであったため、今回はUNDO_RETENTIONの設定に問題が無いことが分かりました。

3-2 UNDO表領域の容量を超過したことによるエラー発生

上記に当てはまらない場合は、この条件である可能性があります。

UNDO表領域に追加の空き容量がない場合は古いUNDOデータから上書きされていきます。
以下コマンドにてUNDO表領域で利用した最大サイズを確認することができます。

SQL>select tablespace_name, sum(bytes)/1024/1024 from dba_segments where tablespace_name like '%UNDO%' group by tablespace_name;

TABLESPACE_NAME                SUM(BYTES)/1024/1024
------------------------------ 　　　------------------------------
UNDOTBS1                                    　　　　　199.1875

初めに確認したSQLからUNDO表領域の容量が最大まで拡張されており、今回確認した表領域を利用した最大サイズがほぼ同じ値になっていることから、今回のORA-01555は古いUNDOデータが上書きされたことによるエラーだと分かりました。

以下コマンドにてUNDO表領域の現在の容量と拡張した際の最大容量を修正します。

SQL>alter database datafile '/app/oracle/oradata/ORCL/undotbs1.dbf' autoextend on maxsize 400M;

データベースが変更されました。

SQL> alter database datafile '/app/oracle/oradata/ORCL/undotbs1.dbf' resize 400M;

データベースが変更されました。