So now  we will understand How update statement works internally. - Once we hit sqlplus statement client process(user) access sqlnet listener. - Sql net listener confirms that DB is open & create server process. - Server process allocates PGA. - ‘Connected’ Message returned to user. - SQL>select * from emp; - Server process checks the SGA to see if data is already in buffer cache. - If not then data is retrived from disk and copied into SGA (DB Cache). - Data is returned to user via PGA & server process. - Now another statement is SQL>Update emp set salary=50000 where empid=40; - Server process (Via PGA) checks SGA to see if data is already there in buffer cache. - In our situation chances are the data is still in the SGA (DB Cache). - Data updated in DB cache and mark as ‘Dirty Buffer’. - Update employee placed into redo buffer. - Row updated message returned to user - SQL>commit; - New SCN obtained from control file. - Data in DB cache is marked as ‘Updated and ready for saving’. - commit placed into redo buffer. - LGWR writes redo buffer contents to redo log files & remove from redo buffer. - Control file is updated with new SCN. - Commit complete message return to user. - Update emp table in datafile & update header of datafile with latest SCN. - exit from SQL prompt. - Unsaved changes are rolled back. - Server process deallocates PGA. - Server process terminates. - After some time redo log are archived by ARCH process.

1
2
3
4

Sqlplus sys/oracle@prim
SQL>select * from emp;
SQL>update emp set salary=50000 where empid=40;
SQL>commit;