In Tom's reply above, doesn't the second session block on the transaction of the first session rather than on the first session itself ?

Thanks, Tom ; this is short and sweet !

But, still, I would like to know how the Oracle differentiates the rows with respect to their locks & Save points (i.e., which rows were locked before the savepoint and which were locked after the savepoint).

Thanks again, Tom !
I understand that savepoints & locks & DML operations, all are there in the UNDO.

This raises two more queries (A) & (B) below:

(A) Whenever we see UNLIMITED (like, locks, savepoints, transactions, sessions, etc), is it better to think of Block Header, UNDO & REDO ?
And, whenever we see LIMITED (like cursors, processes, etc), is it better to think of some other structure ?

(B) This query is less related to the original question.

When does Oracle make an entry of a lock in the UNDO ?
(1) Just Before / Soon After the row is locked
OR
(2) Just Before / Soon After the row is actually changed / deleted ?

Consider this :


08:44:11 stores1@ >create table pt (pk int primary key);

Table created.

real: 78
08:44:23 stores1@ >create table ct (fk int references pt);

Table created.

real: 32
08:44:36 stores1@ >insert into pt values (1);

1 row created.

real: 16
08:44:44 stores1@ >commit;

Commit complete.

real: 16
08:44:50 stores1@ >insert into ct
08:44:56 2 select 1 from all_objects;

4847 rows created.

real: 1172
08:45:06 stores1@ >r
1 insert into ct
2* select 1 from all_objects

4847 rows created.

real: 1609
08:45:11 stores1@ >r
1 insert into ct
2* select 1 from all_objects

4847 rows created.

real: 766
08:45:13 stores1@ >r
1 insert into ct
2* select 1 from all_objects

4847 rows created.

real: 531
08:45:18 stores1@ >r
1 insert into ct
2* select 1 from all_objects

4847 rows created.

real: 485
08:45:20 stores1@ >commit;

Commit complete.

real: 32
08:45:24 stores1@ >select count(*) from ct;

COUNT(*)
----------
24235
----------
24235

real: 31
08:45:30 stores1@ >-- no index on the child table ct
08:45:41 stores1@ >
08:45:42 stores1@ >update pt set pk = 1;

1 row updated.

real: 16
08:45:50 stores1@ >commit;

Commit complete.

real: 16
08:45:52 stores1@ >
08:45:55 stores1@ >
08:45:55 stores1@ >
08:45:55 stores1@ >-- one more case
08:46:02 stores1@ >
08:46:04 stores1@ >update pt set pk = 1;

1 row updated.

real: 16
08:46:11 stores1@ >rollback;

Rollback complete.

real: 15
08:46:14 stores1@ >spo off

What is the effect of this on the size of UNDO / REDO ?

Thank you, Tom !

I believe that it is true for Transaction Entries as well. If a transaction entry for the current transaction is made in the block after the savepoint SP1, then "Rollback to savepoint SP1;" will release the transaction entry as well, as it is was put in the UNDO.