Difference between Transactions, Save Ponits and Restore Points in Oracle

Copy blogging is one hell of a great way to get you started and motivated towards your blogging experience. Sometimes its the only way. I regularly read perhaps the best blog about blogging to kept myself going. But to use Copy Blogging well is also a technique that requires time to master and there are some rules that you need to follow even in copying and guys at Copy Blogger are very good at teaching you those rules. Today was yet another day when I used one of those techniques to blog. Its called re-blogging your own blog. Re-writing your old posts gives you new insights to an old idea and also enables you to do it better. Scanning through my blog I realized that there are a lot of topics that are required to be changed because in many cases technology has changed a lot since that original writing. So here is first one... 

 

Transaction mechanism is one of the most important aspects of any database and likewise every database implements it. Oracle introduced transaction management way back in 1984 in their Release 3 of Oracle RDBMS. 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. Transactions introduced the concept of atomicity while executing multiple statements at the same time and also gave much more control of what can be done.

Almost same is the concept of 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. So save points introduced further granular control inside a transaction.

While transaction and save points are mainly concerned with developer, the concept of Restore Points normally helps a DBA. Restore points allow you to bring your database back to a previous state. That tells us one thing and that is the Restore Points apply to database (data stored on hard disk) and not to memory (or in other words instance). Consider the following situation: 

Suppose you are working on your database and did some updates/deletes in one of your tables and also committed it or you accidentally deleted a table or any other database object that was critical for system or business . Now you want to undo all that crap. Of course you got a very good list of tools to help you recover from this situation. If you go for recovery options than you required to have a backup and if you use Flashback Query that would require quite a lot of time if number of affected rows were in hundreds or thousands. On the other hand if you would have created Restore Point before that dangerous update/delete, life would have been much easier. You can simply restore database back to before update/delete operation.

Creating restore point is simple and you can issue the following command to create one before every critical update/delete:

SQL>Create restore point before_update;

Name can be anything of your choice but should be a descriptive one. Now if something goes wrong with database even if database stops responding than simply shutdown it, bring it to mount state and issue the following command:

SQL>flashback database to restore point before_update;

P.S. Restore point is a feature available in Oracle Database 10g R2 or later and also requires that you enable flashback.

Also posted on Tech Blessing.