Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Abhimitha.

Asked: April 25, 2013 - 8:45 am UTC

Last updated: April 25, 2013 - 1:38 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

I read that for every insert, delete and update enough information is stored in undo that makes the respective operation go away.
1)Is this information in the form of sql script? For example,
Insert into a table will have a delete statement( like Delete from table ) in the undo. If not, how are all this stored in undo?
2) where does oracle keep track of savepoint we create?And is SCN being created for the savepoints created?


and Tom said...

1) no, it is not in the form of a sql script. It is binary information, stored in complex data structures.

Conceptually all we need to know is that for every row inserted - undo will contain a "DELETE+ROWID" to un-insert that row. For every row deleted - undo will contains a "INSERT () values (....) INTO THIS ROWID :R" - the before image of the row and where the row needs to go back to (its address). For every row updated - there will be a corresponding "un-update"

of course we do not store the SQL in there, just sufficient binary information for us to know what to do, what action to take.

2) a savepoint is just conceptually a pointer into your undo stream. We do not need an SCN for it, we just need to know how far back in your undo stream to rollback to.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Does that mean UNDO blocks are released when savepoint is rolled back to?

Vijay Devadhar, September 26, 2014 - 12:37 am UTC

Tom,

You said "a savepoint is just conceptually a pointer into your undo stream. We do not need an SCN for it, we just need to know how far back in your undo stream to rollback to.".

Follow-up question - if we rollback to a savepoint, are the UNDO blocks generated after the savepoint released right away? or do they hang around until the session commits or rolls back outer transaction?

Thanks
Vijay

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.