By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.

You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

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.

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy