Undo Management [message #468599] |
Sat, 31 July 2010 00:48 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
zahidbashir
Messages: 34 Registered: September 2009
|
Member |
|
|
Hello,
I have been reading various articles about the undo management. This basic concept of undo management is simple but how oracle implements it is bit harder for me to grasp.
What i have read and understood is that whenever a DML(Update, Delete, Insert) statement is issued by a user, the data is fetched from datafile to database buffer cache and at the same time a copy of the original data is saved in undo segment. Now if other users requests the same data, they are presented with the unchanged copy in the undo segment.
Now I have the following questions:
1) In case of Insert statement, what data is saved in undo segment. Is it the complete data in the table to which we want to insert the new row?
2)When the user issues DML statement, there are three copies of the same data, one in Memory (which is changed and not the same as original data), second in Undo segment (Which is unchanged copy of original data) and third in datafile file ( which is original data). What is the difference in the data in undo segment and data in datafile at this stage. Why are the other users presented with the data from undo segment rather than original data from the datafile to maintain read consistency.
3)When the user issues rollback, the changes made to the copy of data in memory are undone.The copies of data in memory and undo segment are now same?. What happens to the before change copy in undo segment. Is it still there or deleted.
Please help
Regards,
|
|
|
Re: Undo Management [message #468604 is a reply to message #468599] |
Sat, 31 July 2010 01:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8944 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
This is all dealt with in the Database Admin Workshop 1 course. You are almost there. Firstly, the data written to the undo segment is only the minimal change vector needed to reverse the change: if you update one column, only enough data to identify the row and the previous version of the column is written. For an insert, only the row identifier. This is why deletes generate much more undo than inserts. Secondly, other sessions are not presented with data from the undo segment directly, rather a read consistent version of the block is constructed.
Does this help?
|
|
|
Re: Undo Management [message #468606 is a reply to message #468604] |
Sat, 31 July 2010 02:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
zahidbashir
Messages: 34 Registered: September 2009
|
Member |
|
|
thanks for replying.
Nicely answered but couldn't get the insert part. Rowids of what?? Every row in the table in which the row is to be inserted? or the newly inserted row?
|
|
|
|
Re: Undo Management [message #468611 is a reply to message #468606] |
Sat, 31 July 2010 02:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
zahidbashir
Messages: 34 Registered: September 2009
|
Member |
|
|
Plus is it wrong to say that a copy of original data is saved in the undo segment (As i read in various articles that a copy is saved). Could you please share a link which describes the whole architecture. I google it but most of the article only discuss things like types,retention etc and not the architecture.
Regards,
|
|
|
Re: Undo Management [message #468617 is a reply to message #468611] |
Sat, 31 July 2010 03:16 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:Plus is it wrong to say that a copy of original data is saved in the undo segment
It is not wrong but the correct words are: undo contains the information necessary to rebuild the data as there were before the modification.
Quote:you please share a link which describes the whole architecture.
Database Concepts
Regards
Michel
|
|
|