The Oracle database saves the old value (undo data) when a process changes data in a database. It stores the data as it exists before modifications. Capturing undo data enables you to roll back your uncommitted data. Undo supports read-consistent and flashback queries. Undo can also be used to “rewind” (flash back) transactions and tables.
Read-consistent queries provide results that are consistent with the data as of the time a query started. For a read-consistent query to succeed, the original information must still exist as undo information. If the original data is no longer available, you receive a “Snapshot too old” error. As long as the undo information is retained, the Oracle database can reconstruct data to satisfy read-consistent queries.
Flashback queries purposely ask for a version of the data as it existed at some time in the past. As long as undo information for that past time still exists, flashback queries can complete successfully. Flashback Transaction uses undo to create compensating transactions, to back out a transaction and its dependent transactions. With Flashback Table, you can recover a table to a specific point in time.
Undo data is also used to recover from failed transactions. A failed transaction occurs when a user session ends abnormally (possibly because of network errors or a failure on the client computer) before the user decides to commit or roll back the transaction. Failed transactions may also occur when the instance crashes or you issue the SHUTDOWN ABORT command.
In case of a failed transaction, the safest behavior is chosen, and the Oracle database reverses all changes made by a user, thereby restoring the original data.
Undo information is retained for all transactions, at least until the transaction is ended by one of the following:
- User undoes a transaction (transaction rolls back).
- User ends a transaction (transaction commits).
- User executes a DDL statement, such as a CREATE, DROP, RENAME or ALTER statement. If the current transaction contains any DML statements, the database first commits the transaction and then executes and commits the DDL as a new transaction.
- User session terminates abnormally (transaction rolls back).
- User session terminates normally with an exit (transaction commits).
The amount of undo data that is retained and the time for which it is retained depend on the amount of database activity and the database configuration.
