number of rows affected by rollback

Question

Hi, I need to find out the number of rows affected by a rollback. How can I get this ? Please help.

Answer 1

Actually, the number of rows affected by a rollback is zero. That's because, technically, those rows aren't changed until a commit occurs (the A in ACID). And, if you're rolling back, the commit doesn't happen.

Answer 2

I dont know of a way to do this with oracle, but you could potentially keep track of your created/altered/deleted rows using SQL%ROWCOUNT so you know what will be affected in a rollback

Answer 3

Consider a table fred with two columns (id, value) with two rows. The first row is (1,'Blue') and the second is (2,'Blue')

I issue the following statements

INSERT INTO fred VALUES (1,'Red'); [inserts 1 row]
UPDATE fred SET value = 'Blue';    [updates 3 rows but the value on 2 doesn't change]
UPDATE fred SET id = 3 WHERE id = 1; [updates 1 row]
ROLLBACK;

Both records originally in the table have been updated. 1 was updated twice. One row was inserted and then updated. Then all those changes were rolled back. The question is, what number do you want ? The number of records updated, or the total number of updates performed to records.

The easiest answer to get, from a technical point of view, is the statistic number of undo records applied. But you'd have to measure this before and after. Actually, it can get very confusing because with an UPDATE statement that hits concurrent activity, a statement may be stopped part way through, rolled back and restarted. Ref AskTom