I have a question about the dirty blocks. As you know, there are multiple rows in a block. If multiple users operate on different rows in the same block, will oracle create specific dirty blocks for each transaction?
Or, if transaction just modifies a single row in that block, will the dirty block contain only one row data?
So, in this case, will oracle perform row level lock, or block level lock?
As I know, even if we change only one row in a block, the database loads the complete to the buffer cache and names it as dirty. So assume that, three users changed three different rows of the same block. Then, one performed a rollback, other performed a commit, and the last still makes changes. So in this case how the database will manage that block and their operations?
Bests.
Oracle handles this automatically via the SCN (system change number), plus the UNDO tablespace, and (for the locking) the ITL (interested transaction list) part of the block header.
When you make a change to the data via a DML statement, oracle writes that change out to the relevant blocks (marking the relevant rows as locked in the ITL section of each block header) and also the way to undo the transaction to the UNDO tablespace. Each time you commit, each block has its SCN updated.
Now suppose you are running a query (which the database knows is at SCN #1), but someone has changed the block so it's now at SCN #2. When the block is retrieved by your query, Oracle knows it needs to rollback the data to how it was at SCN #1 - it does this by using the information in the UNDO tablespace to work the data back to the specific SCN.
This is all a part of read consistency, and you can read more about the SCN in this ASKTOM answer, and more about locking of rows here.