Hi,
I am trying to understand the internals of UNDO in the following scenario. The
database is a three-node RAC version 11.2.0.4:
1. A DELETE statement is running on node#3. The statement is deleting around
99 million records from table T1. The program running this statement has no
commit baked into the process.
DELETE FROM T1 WHERE TRUNC(TIMESTAMP) < :1 - 1
1. An INSERT statement runs on node #1 inserting records into table T1:
INSERT INTO T1 ( LOG_SEQUENCE, .....)
1. At his stage, if the INSERT statement (node#1) is trying to insert a
record into the same data block of T1 where another record is being deleted by
the DELETE (node #3) statement, how will Oracle process it? What kind of
waits/contention the session running INSERT might run into?
1. The process running the DELETE statement (node #3) is killed because it
was taking too long to finish, the UNDO segment has millions of UNDO blocks
that the killed process is now rolling back.
1. At his stage, if an INSERT statement (node#1) tries to insert a record
into a data block of T1 that had a deleted row that is being rolled back now,
how will Oracle process it? What kind of waits/contention the session running
INSERT might run into?
A feedback would be greatly appreciated.
Thank you,
Amir
