Oracle ORA-01555 (snapshot old)

Oracle ORA-01555 (snapshot old)

I. Introduction:

[Oracle@www.bkjia.com] $ oerr ora 01555
01555,000 00, "snapshot too old: rollback segment number % s with name \" % s \ "too small"
// * Cause: rollback records needed by a reader for consistent read are
// Overwritten by other writers
// * Action: If in Automatic Undo Management mode, increase undo_retention
// Setting. Otherwise, use larger rollback segments

ORA-01555 snapshot is too old, is a common error in the database, such as when our transactions need to use undo to build CR block, and at this time the corresponding undo does no longer exist, at this time the ORA-01555 error will be reported.

The most ORA-01555 errors are in Oracle 8i and earlier versions. Undo auto tuning from 9i to the current 10g, 11g, making ORA-01555 fewer and fewer errors. However, this error is still inevitable.

2, ORA-01555 errors, usually 2 in the case:

(1) When the SQL statement execution time is too long, the undo tablespace is too small, the transaction volume is too large, or the commit is too frequent, causing consistent read during SQL Execution, the pre-Modified Image (UNDO data) after SQL Execution has been overwritten in the UNDO tablespace, and consistent read block (CR blocks) cannot be constructed ). This is the most common case.

(2) When a block is accessed during SQL statement execution, the transaction commit time of the block cannot be determined in sequence with the SQL Execution start time when the block is cleared. This is rare.

3. Simulation of ORA-01555 scene:

SQL> create table jack (id int, name varchar2 (10 ));

Table created.

SQL> insert into jack values (1, 'A ');

1 row created.

SQL> insert into jack values (2, 'B ');

1 row created.

SQL> commit;

Commit complete.

SQL> show parameter undo

NAME TYPE VALUE
---------------------------------------------
Undo_management string AUTO
Undo_retention integer 900
Undo_tablespace string UNDO2
SQL> create undo tablespace undo1 datafile '/u01/app/oracle/oradata/yft/undo01.dbf' size 1 m;

Tablespace created.

SQL> alter system set undo_retention = 1;

System altered.

SQL> var x refcursor
SQL> begin
2 open: x for select * from jack;
3 end;
4/

PL/SQL procedure successfully completed.

SQL> begin
2 for I in 1 .. 20000 loop
3 update jack set id = id + 1;
4 commit;
5 end loop;
6 end;
7/

PL/SQL procedure successfully completed.

SQL> print: x
ERROR:
ORA-01555: snapshot too old: rollback segment number 17 with name
"_ SYSSMU17_2039231318 $" too small

 

No rows selected

Iv. Solutions to 1st cases:

(1) increase the size of the UNDO tablespace;

(2) increase the undo_retention time. The default value is 15 minutes;

(3) Optimize the SQL statements with errors to reduce the query time. The preferred method is as follows;

(4) Avoid frequent submission.

Oracle ORA-01555 snapshot old description

Troubleshooting for ORA-01078 and LRM-00109

ORA-01555 ultra-long Query Duration time

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store