this is not a simple question, as there are many activities involved in a DML.
I’ll try to answer, there will be lack of details..probably errors…I studied such things long time ago with 8i version,
and now probably some behaviour has changed.
Let imagine an UPDATE which, as far I remember, is the most complicated, because a “before image” is needed.
For simplicity take in count a very simple update which modifies a single row, like “update table set column=value where column_pk=xx;’
(column_pk==> Primary Key)
Any modification against a data block, at first happens in Database Buffer Cache (DBF)
and only after it is moved to datafiles (if committed).
– The server process (which is the one that works at server side) scans the DBC
to see if the required data block is already cached. Suppose it is not.
– The server process scans the DBC to find a free block where to copy contents from datafile.
– Once a free block is found, the data is copied and the buffer state is changed to PINNED.
– A slot in rollback segment is acquired, and a block of rollback it is
copied to DBC and filled with the present copy of data (the before image => before the update).
– The update modification is applied to the block in DBC.
– The entries related with the operations are written in redo log buffer. I used “operations”, plural,
because both data and rollback changes are written in the redo log, .
At this point it is ALL in memory, nothing (but this is not always true, because of DBWR and LGWR algorithm)
has been written on disk yet.
– the user issues a commit.
– Oracle assigns a unique SCN (System Change Number) to the transaction both in rollback segments
and in the redo log buffer.
– LGWR actually writes the content in the redo log buffer to the redo log files on disk subsystem.
– Once the operating system confirms that the write has been done, Oracle notifies the server process
that the transaction has been committed.
This is a very simplified view around what really happens. You have to take in count that
DBC is not a simple array but there are other structures that make it usable by server process and
Here some details:
More than this, there are surely other things to take care about:
Locks, Latchs, Read Consistency and probably many other that
I’m forgetting now.
Hope this help
Its well explained. Check out this.