Your possible solutions to your problem depend on your Oracle version and your configuration.
If you are using Oracle 8i and higher and you have your database configured to archive your online redo logs, then you can "mine" those archived redo logs for the transaction that was committed improperly. You will need to know the approximate time you performed this commit. You then use Oracle's Log Miner utility to search the archived redo log from that time. The V$LOGMINER_CONTENTS view contains the contents of your Log Miner session. Most important to you is the SQL_UNDO column which contains the SQL statements needed to undo or reverse your committed transaction.
If you are using Oracle 10g and you have configured a sufficient enough retention time in your UNDO tablespace, then you can perform a Flashback Table option. Flashback your table to a time before you committed the transaction. You will then be able to query the table's contents as it existed before the commit. Save the table's contents in another table. When you disable the Flashback Table operation, you can use the saved contents to put the data back in the table as required.
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.