Undo in oracle is one of the most confusing concept, which most DBA's find difficult to understand. First of all what is undo...?... In simple terms to revert/undo changes that you have done.
But first as per Oracle definition " Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it"
In simple terms undo means to revert/undo changes that you have done, but when we are talking about undo w.r.t the database it has many more things.
Undo is the old data which database requires for read consistency. We will try to understand this with an example :
1) User A has created a table Employee.
2) User A has inserted 10 rows in the table and commit the database. Now after committing this data has been written by LGWT in redo log i.e. this becomes permanent.
3) User A again inserted 10 more rows but haven't committed yet.
4) As we know oracle is an OLTP system and many users can manipulate the same data at same time.
User B wants to read the data of Employee table.
Here comes the concept of read consistency. For User A Employee table has 20 Rows but up to the first 10 rows data is committed i.e permanent but what about the other 10 rows, these are not yet committed. What if User A deletes that row and then again enter a commit.
Oracle can only show User B data that is committed/permanent. This is read consistency.
Oracle definition " Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it"
But first as per Oracle definition " Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it"
In simple terms undo means to revert/undo changes that you have done, but when we are talking about undo w.r.t the database it has many more things.
Undo is the old data which database requires for read consistency. We will try to understand this with an example :
1) User A has created a table Employee.
2) User A has inserted 10 rows in the table and commit the database. Now after committing this data has been written by LGWT in redo log i.e. this becomes permanent.
3) User A again inserted 10 more rows but haven't committed yet.
4) As we know oracle is an OLTP system and many users can manipulate the same data at same time.
User B wants to read the data of Employee table.
Here comes the concept of read consistency. For User A Employee table has 20 Rows but up to the first 10 rows data is committed i.e permanent but what about the other 10 rows, these are not yet committed. What if User A deletes that row and then again enter a commit.
Oracle can only show User B data that is committed/permanent. This is read consistency.
Oracle definition " Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it"
Here 10 rows data is before the image of the employee table which oracle has to maintain at some place so that while user is changing that data still another user can read/access that particular data. For this Oracle manage Undo records.
This is the basic crux of Undo.
Thanks and Enjoy Learning!!
No comments:
Post a Comment