1A:- The server process first brings the old salary to the buffer cache(with reference to emp ID) and it will be loaded in to UNDO and maintain lock on the new sal:2000 block so that no other user can fetch new sal:2000 value until user issue commit. Once the commit is issued old value in the UNDO will be deleted and the lock will be released on new sal:2000 so that all the other user's can fetch updated sal:2000 value. Also on the other hand irrespective of the the commit is issued new SCN and sal:2000 data will be written in to the online redo log's by LGWR.
Before all thank you for your efforts. Question 5 : Concerning the locks in case of an update of the 2 rows among 5 in the same block, yes, the 3 other rows will not be locked, but it can be impossible to update the 3 other rows. Why, because there will not be space in the header block to record the medata of the new update transaction. Take a look to INITRANS and MAXTRANS properties of a segment to understand the behavior. Question 6 : All queries are soft parsed every time they are submitted. If there is no plan or some environment conditions change, a hard parse is done. Not all queries put the read blocks in the SGA. In 11g and above, when a FULL SCAN is done on a great table, the read blocks from files are not put in the SGA.
