Oracle Database - UNDO (Rollback Segment)

1 - What is UNDO ?

Oracle Database creates and manages information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

  • Provide read consistency

To manage the multiversion read consistency model, the database must create a read-consistent set of data when a table is simultaneously queried and updated. Oracle Database achieves this goal through undo data.

  • Roll back transactions when a ROLLBACK statement is issued

When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction.

  • Recover the database

During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles.

  • Analyze data as of an earlier point in time by using Oracle Flashback Query
  • Recover from logical corruptions using Oracle Flashback features

You can reduce the undo generation by introducing multiple commits in a loop while loading data, but the overall elapsed time will increase significantly.

3 - Support

3.1 - ORA-01555 - snapshot too old

ORA-01555: snapshot too old: rollback segment number 12 with name "_SYSSMU12_253434260$" too small '
  • The UNDO space was too small. The SELECT statement was killed letting the DML operations continue.
  • Of the Minimum Undo Retention Period was achieved Minimum Undo Retention

See AskTom

4 - Reference/Documentation

db/oracle/undo.txt ยท Last modified: 2017/09/06 19:29 by gerardnico