Ask the Expert

Getting back to a particular day's state after committing something incorrectly

Suppose I have commited something that is wrong, and later on I want to go back to that particular time or day's state. How do I do that?

    Requires Free Membership to View

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.

This was first published in December 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: