How to identify and roll back by backing out specific transactions
In Oracle 11g, you can use OEM to examine and rollback transactions
using LogMinor to extract DML statements from the redo log files.
The following are the steps:
Enable log mining:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
In OEM, from the Database homepage, go to the "Availability"
Then under Manage click on the "View
and Manage Transactions"
link. This will bring the LogMinor interface.
you can enter specific ranges in time or SCN plus DBA user and specific
table. Enter your criteria filter and click Continue.
Now, you have kicks up the LogMinor to list all transactions from online
and archived redo log files. You see your number of DML transactions.
On Oracle 11g, now you can view the Dependent Transactions, if you click
on "Flashback transaction."
But Submit all the dependent transactions will be backed out.