Understanding Flashback Technology - OCP: Oracle Database 12c Administrator Certified Professional - page 377

Flashback Transaction Query goes a step further, allowing you to perform transactional
recovery of tables. In other words, it provides you with the SQL that could be used to undo
the transaction.
Before you can begin using Flashback Transaction Query functionality, there are two
configuration steps that must be completed.
1. Ensure that the database is running with version compatibility = 10.0 or higher.
2. Enable supplemental logging (in other words, ALTER DATABASE ADD SUPPLEMENTAL
LOG DATA; ).
In addition to these system-wide settings, users who want to take advantage of this
feature must be granted the SELECT ANY TRANSACTION privilege. They must also be
granted the FLASHBACK privilege on the specific tables they want to flash back, or they
must have the broader FLASHBACK ANY TABLE privilege.
Flashback Transaction Query is implemented through the use of the FLASHBACK_
TRANSACTION_QUERY view. The data in this view allows analysis of a specific transaction to
identify what changes were made to the data. This view can be large, so it is helpful to use a
filter when querying the view. This will generally be the transaction identifier ( XID column).
Be sure to note that the transaction identifier is stored as a raw value in Oracle 12 c . This
is a change from Oracle 10 g , which stored it as a character value. Because of this, you can't
simply pass in a string representation of a transaction identifier; you must provide a raw
value. You can use Flashback Version Query to provide it for you. For example, let's use the
FLASHBACK_TRANSACTION_QUERY view to analyze the transactions that created the changes
we viewed in the previous section. To do this, we will join a Flashback Version Query with
the FLASHBACK_TRANSACTION_QUERY view, as shown here:
SQL> select table_name, operation, undo_sql
from flashback_transaction_query t,
(select versions_xid as xid
from employees versions between scn minvalue and maxvalue
where employee_id = 123) e
where t.xid = e.xid
and operation = 'UPDATE';
TABLE_NAME OPERATION UNDO_SQL
----- ----- --------------------
EMPLOYEES UPDATE update "HR"."EMPLOYEES" set "SALARY" =
'2108.93' where ROWID =
'AAARAgAAFAAAABYABd';
EMPLOYEES UPDATE update "HR"."EMPLOYEES" set "SALARY" =
'4217.85' where ROWID =
'AAARAgAAFAAAABYABd';
EMPLOYEES UPDATE update "HR"."EMPLOYEES" set "SALARY" =
'4017' where ROWID =