Oracle DML Process

The process of a Delete operation in Oracle
DELETE)
1. Oracle reads the Block to the Buffer Cache (if the Block does not exist in the Buffer)
2. record the details of the delete operation in the redo log buffer.
3. Create an undo entry in the transaction table of the corresponding rollback segment Header
4. Save the image before the record is deleted to the Undo Block.
5. delete records on the corresponding data blocks in the Buffer Cache and mark the corresponding data blocks as Dirty

Submit)
1. Oracle generates an SCN
2. Mark the transaction status as committed in the rollback segment transaction table
3. LGWR Flush Log Buffer to Log files
3. If the data Block is still in the Buffer Cache, the SCN will be recorded on the Block Header, which is called fast commit)
4. If the dirty block has been written back to the disk, the next process accessing the block will obtain the status of the transaction from the rollback segment and confirm that the transaction is committed. Then the process obtains the committed SCN and writes it back to the Block Header. This is called delayed block cleanout ).

An Update operation procedure in Oracle
1. the user submits an update statement.
2. serverprocess checks the memory cache
If there is no valid memory space, start DBWR and write the dirty data blocks not written to the disk in the cache.
Ii. If there is space available, read data from the disk
3. Update Data in the cache
I apply for a rollback segment entry to write old data into the rollback segment
Ii lock update data
Iii. Record the modification in the Redo log buffer at the same time.
4. the user submits a Commit
I SCN increase
Ii write the Redo log buffer into the Redo log file
Iii tell the user that Commit is complete

An Insert operation in Oracle
1. Apply for memory space from db buffer.
2. Generate REDO. UNDO records (UNDO generates REDO information), index segment REDO information, and record relevant data row numbers in UNDO.
3. COMMIT
Increase of SCN
Write Redo log buffer into Redo log file
Start DBWR and write the cached data to the dirty data block on the disk.
Tells the user that Commit is complete.

DELETE generates the most UNDO information (records the values of all related fields ).
The UNDO information generated by UPDATE is in the middle (record the value of the field to be updated ).
INSERT produces the least UNDO information (the row number of the record to be inserted ).