Restore Points

Transaction mechanism is one of the most important aspects of any database and likewise every database implements it. Transaction is an atomic block of SQL statements that can be executed against a database. Committing a transaction means that the changes made by the transaction should be made permanent. And likewise rollback means that the changes made by transaction must not be applied to the database. Transaction mechanism works logically in the memory and not on the physical hard disc. It means that once you have committed a transaction or have rolled back then you can’t do the other. Similar for save points; save point allow you to specify specific points within a transaction so that you can perform specific operations on specific points within a transaction. It all sounds good with the obvious limitation described above. Oracle takes this concept to the next level in 10g.
The very simple but still very powerful feature for the DBA is of restore points. Restore points allow you to bring your database back to a previous state. Consider the following situation. Suppose you are working on your database and made a change in one of your tables and also committed it. You shut downed the database and went back home. There you realized that the change you made was a blunder. You can undo the change with Oracle’s flashback query but what if the changes were in hundreds. You need an entire day. No. you did not if you would have implemented just one thing. If you have created the restore points after critical updates every time doing them then you can roll back your database just with a single command. The creation of restore points is through very familiar “create” statement. You can create restore points with the following command.
create restore point restorepointname; But remember this command only works for Oracle Database 10g Release 2. So create restore points after every critical update.