With
Flashback Transaction, you can reverse a transaction and dependant
transactions. Oracle Database determines the dependencies between transactions
and, in effect, creates a compensating transaction that reverses the unwanted
changes. The database rewinds to a state as if the transaction, and any
transactions that could be dependent on it, never occurred.
You can use
the Flashback Transaction functionality from within Enterprise Manager or with
PL/SQL packages.
Prerequisites
In order to
use this functionality, supplemental logging must be enabled and the correct
privileges established. For example, the HR user in the HR schema decides to
use Flashback Transaction for the REGIONS table. The SYSDBA performs the following
setup steps in SQL*Plus:
alter database add
supplemental log data;
alter database add
supplemental log data (primary key) columns;
grant execute on
dbms_flashback to hr;
grant select any
transaction to hr;
Flashing
Back a Transaction
Security
privileges
To flash
back or back-out a transaction—that is, to create a compensating
transaction—you must have the SELECT, FLASHBACK, and DML privileges on all
affected tables.
Conditions
of Use
•
Transaction
back-out is not supported across conflicting DDL.
•
Transaction
back-out inherits data type support from LogMiner. See the Oracle Database 11g
documentation for supported data types.
Recommendation
•
When
you discover the need for transaction back-out, performance is better if you
start the back-out operation sooner. Large redo logs and high transaction rates
result in slower transaction back-out operations.
•
Provide
a transaction name for the back-out operation to facilitate later auditing. If
you do not provide a transaction name, it will be automatically generated for
you.
WorkFlow
1.
Viewing
data in a table
2.
Discovering
a logical problem
3.
Using
Flashback Transaction
1.
Performing
a query
2.
Selecting
a transaction
3.
Flashing
back a transaction (with no conflicts)
4.
Choosing
other back-out options (if conflicts exists)
4.
Reviewing
Flashback Transaction results
