Flashback
technology provides the capability to query past versions of schema objects,
query historical data, and perform change analysis. Every transaction logically
generates a new version of the database. With Flashback technology, you can
navigate through these versions to find an error and its cause:
•
Flashback
Query: Query all
data as it existed at a specific point in time.
•
Flashback
Version Query: See
all versions of rows between two times and the transactions that changed the
row.
•
Flashback
Transaction Query:
See all changes made by a transaction.
Flashback
Query
•
With
the Flashback Query feature, you can perform queries as of a certain time. By
using the AS OF clause of the SELECT statement, you can specify the time stamp
for which to view the data. This is useful for analyzing a data discrepancy.
•
Note: TIMESTAMP and SCN are valid options
for the AS OF clause.
Flashback
Version Query
•
With
Flashback Query, you can perform queries on the database as of a certain time
span or range of user-specified system change numbers (SCNs). The Flashback
Version Query feature enables you to use the VERSIONS clause to retrieve all
the versions of the rows that exist between two points in time or two SCNs.
•
The
rows returned by Flashback Version Query represent a history of changes for the
rows across transactions. Flashback Version Query retrieves only committed
occurrences of the rows. Uncommitted row versions within a transaction are not
shown. The rows returned also include deleted and subsequently reinserted
versions of the rows.
•
You
can use Flashback Version Query to retrieve row history. It provides you with a
way to audit the rows of a table and retrieve information about the
transactions that affected the rows. You can then use the returned transaction
identifier to perform transaction mining by using LogMiner or to perform a
Flashback Transaction Query.
•
Note: VERSIONS_XID is a pseudocolumn that
returns the transaction identifier of the corresponding version of a row.
Flashback
Version Query: Considerations
The
VERSIONS clause cannot be used to query the following types of tables:
•
External
tables
•
Temporary
tables
•
Fixed
tables
You cannot
use the VERSIONS clause to query a view. However, a view definition can use the
VERSIONS clause.
The
VERSIONS clause in a SELECT statement cannot produce versions of rows across
the DDL statements that change the structure of the corresponding tables. This
means that the query stops producing rows after it reaches a time in the past
when the table structure was changed.
Certain
maintenance operations, such as a segment shrink, may move table rows across
blocks. In this case, the version query filters out such phantom versions because
the row data remains the same.
Flashback
Transaction Query
Flashback
Transaction Query is a diagnostic tool that you can use to view changes made to
the database at the transaction level. This enables you to diagnose problems in
your database and perform analysis and audits of transactions.
You can use
the FLASHBACK_TRANSACTION_QUERY view to determine all the necessary SQL
statements that can be used to undo the changes made either by a specific
transaction or during a specific period of time.
Flashback
Transaction Query: Considerations
Within the
database, DDL operations are nothing but a series of space management
operations and changes to the data dictionary. Flashback Transaction Query on a
transaction underlying a DDL command displays the changes made to the data
dictionary.
When
Flashback Transaction Query involves tables that have been dropped from the
database, the table names are not reflected. Instead, object numbers are used.
If the user
who executed a transaction is dropped, Flashback Transaction Query of that
transaction displays the corresponding user ID only, and not the username.
Note: When there is not enough undo data
for a specific transaction, a row with a value of UNKNOWN in the OPERATION
column of FLASHBACK_TRANSACTION_QUERY is returned.
