undo records

[dba1]

Need answer to this question:
Which of the following statement create undo records: 1) insert 2) update 3) update & delete only 4) insert,update & delete.

[pando]

4 of course

[dba1]

Thanks.
The reason I posted this question is to confirm my answer. I'm studying for Fundamental I test. The self-study software I'm using gives the answer 3) as solution. I'm confused by this.

[julian]

Originally posted by dba1
Thanks.
The reason I posted this question is to confirm my answer. I'm studying for Fundamental I test. The self-study software I'm using gives the answer 3) as solution. I'm confused by this.

Now a question for you: for what are undo records generated when insert is issued? As DBA you must know this :-)

[dba1]

Originally posted by julian
Now a question for you: for what are undo records generated when insert is issued? As DBA you must know this :-)

It's for undoing (rolling back) the transactions when needed in case. Please tell if you have different answer. Thanks.

[pando]

only rowid is recorded in undo for inserts

[jmodic]

Originally posted by dba1
It's for undoing (rolling back) the transactions when needed in case. Please tell if you have different answer.

I think your answer is quite correct. It's only for the purpose of eventual undoing the insert, it can't be for read consystency (as the record did not yet exis before, so noone will need its read consistent view untill the commit).

But perhaps julian wanted to ask "what is stored in undo records for inserts?" and that's what pando is refering to:

Originally posted by pando
only rowid is recorded in undo for inserts

Well, as far as actual data belonging to the inserted row is concerned, the above answer is correct (as inserted row has no before-immage). But technicaly speaking, Oracle has to record much more stuf into undo record for each inserted row than just its ROWID. That's why each inserted record will generate at least 64 bytes of undo information, in many cases even more. And 64 bytes is much more than it is needed for a ROWID.

[julian]

Originally posted by jmodic
I think your answer is quite correct. It's only for the purpose of eventual undoing the insert, it can't be for read consystency (as the record did not yet exis before, so noone will need its read consistent view untill the commit).

But perhaps julian wanted to ask "what is stored in undo records for inserts?" and that's what pando is refering to:

Well, as far as actual data belonging to the inserted row is concerned, the above answer is correct (as inserted row has no before-immage). But technicaly speaking, Oracle has to record much more stuf into undo record for each inserted row than just its ROWID. That's why each inserted record will generate at least 64 bytes of undo information, in many cases even more. And 64 bytes is much more than it is needed for a ROWID.

What else besides the rowid? Could you please provide some reference on that?

[jmodic]

Originally posted by julian
What else besides the rowid? Could you please provide some reference on that?

http://www.ixora.com.au/q+a/0103/14230855.htm