7

Can committed data be rolled back in oracle? If yes then how can we do this. Please help.

udo
  • 4,832
  • 4
  • 54
  • 82
facebook
  • 1,894
  • 3
  • 21
  • 28

3 Answers3

15

Oracle has several features under the umbrella term FLASHBACK, which in some circumstances allow restoring individual tables or the entire database to an earlier state, without having to do an RMan restore & recover option. The availability of these features depends on (1) which edition of the database you are using, and (2) whether a flash recovery area has been configured. The amount of time that you can flashback depends on the size of the FRA and the amount of activity in the database.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
6

In all RDBMS's, a committed transaction is committed.

As pointed out by @Dave Costa, ORACLE supports Flashback allows you to revert committed changes in some circumstances, without a restore from backup.

Normally, in order to undo committed data, you would have to restore an appropriate backup and then apply changes up to the point you desire.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • 8
    Not quite true. FLASHBACK in Oracle allows reversion of committed changes in some circumstances, without a restore from backup. – Dave Costa May 06 '11 at 13:07
2

In Oracle there are several ways to rollback transactions, even when committed. FLASHBACK is one, an other is logminer generating undo transactions. Most of the times the intention is to revert specific tables. Question that it rises is: how are the other tables? How should the data be kept consistent.

Normally, yes, committed is permanent but we can do more, if we run in archive log mode.

If the data is in a self contained tablespace, you could to a TSPITR to recover that specific tablespace to just before the update.

More input is required to correctly give the right options to undo the tx.