Oracle Row Level Flashback Features

The value of the UNDO_RETENTION parameter determines the length of time your users can flashback their queries.

When you issue a SELECT statement using the VERSIONS clause, Oracle will return the different committed versions of the same row between two SCNs or two timestamps.

Here is a brief explanation of pseudocolumns that will be part of the flashback versions query output:

VERSIONS_STARTSCN and VERSIONS_STARTTIME  - This pseudocolumn tells you the SCN and timestamp when this particular row was first created.

VERSIONS_ENDSCN and VERSIONS_ENDTIME - These pseudocolumns tell you when this particular row expired.

VERSIONS_OPERATION - This pseudocolumn provides you with information as to the type of DML activity that was performed on the particular row. The DML activities are indicated by letters: I stands for insert, D for delete, and U for update.

VERSIONS_XID - This pseudocolumn stands for the unique transaction identifier of the transaction that resulted in this row version.

Note: If the VERSIONS_STARTSCN and the VERSIONS_STARTTIME are NULL, then the row was created before the lower bound specified by your BETWEEN clause.

Note: If the VERSIONS_ENDSCN and the VERSIONS_ENDTIME are NULL, this means that this row version is current when you tried to use the Flashback Versions Query operation, or the row was part of a delete operation.

Note: An index-organized table (IOT) will show an update operation as a separate insert and a delete operation.

Example:

FLASHBACK_TRANSACTION_QUERY - lets you identify which transaction or transactions were responsible for certain changes during a certain interval.

Its columns are:

Note: You must have the SELECT ANY TRANSACTION system privilege to query the
FLASHBACK_TRANSACTION_QUERY view.

Value passed to HEXTORAW function obtained from Flashback versions query of an old row version to undo or audit.

• Flashback Transaction Query on a transaction underlying a DDL displays the changes made to the data dictionary.

• When you use Flashback Transaction Query on a dropped table, object number ( not the table name) will be displayed.

• When you use Flashback Transaction Query on a dropped table, userid (not the username) will be displayed.

• If you query a transaction involving an IOT, an update operation is always shown as a two-step delete/insert operation.

• Sometimes you’ll notice a value of UNKNOWN under the OPERATION column, if the transaction didn’t have enough undo information to correctly identify its operation type.

• You may want to turn on minimal supplemental logging in order to support operations involving chained rows and special storage structures such as clustered tables.