Databases Reference
In-Depth Information
How It Works
When a row is updated, the database records the relevant change details in the database blocks, in addition to
some other related details, such as the SCN of when the change occurred, the timestamp, the type of operation that
resulted in the change, and so on—a sort of “metadata” about the changes, if you will. This metadata is stored in
pseudocolumns and can be queried afterward.
Table
13-4
describes the flashback query pseudocolumns. The pseudocolumns that start with VERSIONS, such
as VERSIONS_STARTTIME, are not actually part of the table. They are computed and shown to the user at runtime.
A good everyday example of such a pseudocolumn is ROWNUM, which denotes the serial number of a row in the
returned result set. This column is not stored in the table but is computed and returned to the user when the query is
executed. Since these columns are not part of the table's definition, they are called pseudocolumns.
Table 13-4.
Flashback Query Pseudocolumns
Pseudo Column Name
Description
VERSIONS_STARTTIME
This is the timestamp when this version of the row became effective. This is the
commit time after the row was changed.
VERSIONS_STARTSCN
This is the SCN when this version became effective.
VERSIONS_ENDTIME
This is the timestamp when the version became old, replaced by a new version.
This is the time of commit after the row was changed.
VERSIONS_ENDSCN
This is the SCN when the row's version was changed.
VERSIONS_XID
This is the transaction ID that changed the row's version. This can be joined with the
XID column of the dictionary view FLASHBACK_TRANSACTION_QUERY to show
the transaction that made this change. The view FLASHBACK_TRANSACTION_QUERY
also shows other relevant details of the transaction, such as who did it, when, and so on.
VERSIONS_OPERATION
This is the abbreviated activity code—I, U, or D—for Insert, Update, or Delete that
resulted in this version of the row.
Flashing Back a Specific Table
Problem
You want to flash back a specific table, not the entire database, to a point in time in the past.
Solution
The table can be flashed back with a specialized adaptation of flashback queries. Here are the steps on how to do it:
1.
Make sure the table has row movement enabled:
SQL> select row_movement
2 from user_tables
3 where table_name = 'ACCOUNTS';
ROW_MOVE
--------
ENABLED
