I am hoping you can shed some light on the topic of rollback in Oracle. I will use SHUTDOWN IMMEDIATE as an example. A DML update command is issued updating, say, 10,000 records. No COMMIT is issued. The DBA issues a SHUTDOWN IMMEDIATE command, which prevents additional sessions from connecting and rolls back the uncommitted transactions.
My question is, what is Oracle rolling back? If the changes were uncommitted, never written to disk, never written to the datafiles and only affected blocks in memory, what is it actually rolling back?
Your question assumes the changes were uncommitted and thus:
1. Changes were never written to disk
2. Changes were never written to the datafiles
Both of these are incorrect. It is possible for an uncommitted transaction to write changes to the online redo logs and the tablespace’s datafiles. Think of it this way…your LOG BUFFER is 1MB in size but you are inserting 10MB of data into a table. Once the Log Buffer gets filled, where do the other 9MB of changes get stored before the COMMIT? The Log Buffer writes the change vectors to the online redo logs even though a COMMIT was never issued. Also, assume your DB_BUFFER_CACHE is 1MB in size (not realistic I know but the example is valid). Once the buffer cache is filled with changed blocks, those changes need to be written to the database datafiles before new blocks can be modified. Datafiles and online redo logs can contain uncommitted changes. Therefore, when a SHUTDOWN IMMEDIATE is issued, Oracle rolls back any uncommitted changes before completing the shutdown.
Have a question for Brian Peasland? Send an e-mail to email@example.com
Dig Deeper on Oracle database backup and recovery
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading