Oracle: Understanding UNDO Usage

Having effective UNDO functionality is one of the key requirement for your Database. Oracle is working beautifully by managing UNDO functionality. So what is the use of undo and how it is related to the application/batch process coding?

Every uncommitted DML generates undo data, helping the database user to rollback transactions up to certain time. This depends upon the size of UNDO Tablespace and retention time. Mostly housekeeping or batch (delete/update) process, background DML jobs generates more UNDO data as they consist high amount of records. There are some cases when inserts also impact UNDO in the activity index rebalancing in the case of large indexed tables. In the normal scenario, every delete generates good amount UNDO data. Oracle uses this information to maintain data integrity. When database user issues rollback command, oracle UNDO all activities at database level till the last commit. The database uses UNDO tablespaces to store all this information. Also, when batch processes are updating some information; but not committed, and at the same time some database user queries on the same set of information then oracle fetch old data from the UNDO tablespace and rest of the data from the actual tables. In such scenario, Oracle will not allow the user to view uncommitted data maintaining data integrity.

Oracle provides functionality to configure UNDO retention time, means till what period database can store UNDO data. After setting up this value we can enforce database to maintain UNDO data up to given time. If we are setting force undo retention then we must ensure to allocate the right amount of size for UNDO tablespace. Otherwise, we will get cannot extend UNDO tablespace errors.

Oracle also provides flashback query feature nowadays using which we can view dataset in the given time. How far we can go back is depends upon the size of the UNDO tablespace and how much frequently table information is getting updated. In the normal scenario, we can look back up to 24 hours or a week depends on the undo retention parameter if database operations are very quite. In order to run flashback query on the specific table, we need to have flashback grants for that table for non-schema owners. Means earlier after commit there was no option to find what was the value of the dataset earlier unless backup which was not an easy route. Using flashback queries is not difficult, we just need to add “as of timestamp” option and need to provide right value in the past with correct timestamp format.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s