12.6 Flashback queries

---

In Oracle 9i, with the introduction of the undo tablespace management feature, that replaces the rollback segment approach, Oracle has also added the feature of retention. With the retention feature, the previous images of data could be retained for a user-specified period of time. The feature that supports the visibility to the previous queries and data from the undo tablespace is called ''flashback.''

Using the flashback query feature provides visibility to data at a point- in-time in the past, and this data could be used to compare the data in the present. In the case of an UPDATE operation, the flashback query option provides visibility to the data exactly as it appeared before the UPDATE statement was executed.

How much of the past data is visible depends on the UNDO_RETENTION parameter defined in the init<SID>.ora file. To retrieve these rows, Oracle has provided certain built-in packages such as DBMS_FLASH BACK and a set of standard procedures.

Benefits of using flashback query

• Saving SCNs and flashing back to those SCNs, instead of saving data sets and retrieving them later, could gain application performance.

• Flashback queries are online operations, in the sense that while normal activity against the database is in progress, users could perform flashback query transactions against the UNDO tablespace without affecting the normal activity. This is possible because flashback query normally goes back in time and different sessions can flash back to different flashback times or SCNs on the same object concurrently.

• Apart from defining an appropriate value for the UNDO_RETENTION parameter, no additional management activity is required to perform flashback queries.

While flashback queries are good to retrieve and audit certain operations in the past, this feature cannot be used to undo any activity. It only helps identify changes during a period of time or changes performed by a specific SCN.

Oracle 10g 

New Feature: There are significant enhancements to the flashback functionality in Oracle 10g,

1. FLASHBACK DATABASE: will help bring the database to a prior point in time by undoing all the changes that have taken place since that time.

2. FLASHBACK TRANSACTION HISTORY: using the DBA_TRANSACTION_QUERY view provided, changes to the database can be examined at the transaction level.

3. FLASHBACK TABLE: this statement helps quickly recover a table to a point in time in the past, eliminating the need to restore from a backup.

4. FLASHBACK DROP: Provides a way to restore accidentally dropped tables. When tables are dropped Oracle places them in a recycle bin from which they can later be recovered.

Flashback queries in RAC

Each instance in a two or more instance configuration such as RAC maintains its undo activity in their respective UNDO tablespaces. Similarly, each instance configures in its respective init<SID>.ora file the UNDO_RETENTION parameter that provides the threshold value on how many days' worth of undo data is retained, and makes the flashback query features specific to every instance.

---