Databases Reference
In-Depth Information
data in your Oracle Database at a previous point in time. With Flashback Query, you
can direct Oracle to return the results for a SQL query at a specific point in time. For
instance, you could ask for a set of results from the database as of two hours ago. Flash‐
back provided extra functionality by leveraging the rollback feature that was already a
core part of the Oracle architecture.
Since Flashback uses rollback segments, you can only flash back as far as the information
in the current rollback segment. This requirement typically limits the span of Flashback
to a relatively short period of time—you normally would not be able to roll back days,
since your Oracle Database doesn't keep that much rollback information around. De‐
spite this limitation, there are scenarios in which you might be able to use a Flashback
Query effectively, such as going back to a point in time before a user made an error that
resulted in a loss of data.
The use of Flashback has increased as Oracle has added more flashback capabilities to
the database. Oracle Database 10
g
greatly expanded the flashback capabilities available
to include:
• Flashback Database, to roll back the entire database to a consistent state
• Flashback Table, to roll back a specific table
• Flashback Drop, to roll back a DROP operation
• Flashback Versions Query, to retrieve changes to one or more rows
Oracle Database 11
g
continued this expansion with the Flashback Transaction feature,
which can be used to reverse the effect of a transaction and any other transactions that
are dependent on it.Oracle Database 11
g
R2 added Flashback Data Archive, part of the
Advanced Compression option.With this feature, older versions of rows are stored in
shadow tables, allowing Flashback Queries against very old data without having to have
to keep all of the versions in the undo tablespace. In Oracle Database 12
c
, Flashback
Queries are extended to support queries on temporal validity dimensions (for valid time
periods). Additionally, with Oracle Database 12
c
you can recover an individual table
from a database backup.
A Transaction, Step by Step
This simple example illustrates the complete process of a transaction. The example uses
the EMP table of employee data, which is part of the traditional test schema shipped
with Oracle Databases. In this example, an HR clerk wants to update the name of an
employee. The clerk retrieves the employee's data from the database, updates the name,
and commits the transaction.
The example assumes that only one user is trying to update the information for a row
in the database. Because of this assumption, it won't include the steps normally taken
