{"id":5444,"date":"2026-02-15T16:42:07","date_gmt":"2026-02-15T11:12:07","guid":{"rendered":"https:\/\/w3buddy.com\/?p=5444"},"modified":"2026-02-15T16:42:09","modified_gmt":"2026-02-15T11:12:09","slug":"oracle-rollback-statement-behind-the-scenes","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/oracle-rollback-statement-behind-the-scenes\/","title":{"rendered":"Oracle ROLLBACK Statement: Behind the Scenes"},"content":{"rendered":"\n

When you execute ROLLBACK;<\/code>, Oracle discards all uncommitted changes and restores data to its previous state. Understanding ROLLBACK is essential for transaction management and error handling in Oracle Database.<\/p>\n\n\n\n

The Complete ROLLBACK Execution Flow<\/h2>\n\n\n\n
\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                    USER EXECUTES: ROLLBACK;                     \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 1: LOCATE UNDO DATA                      \u2502\n\u2502  \u2022 Find transaction's undo segment                              \u2502\n\u2502  \u2022 Read undo records (before images)                            \u2502\n\u2502  \u2022 Identify all changes to reverse                              \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 2: APPLY UNDO DATA                       \u2502\n\u2502  \u2022 Read before images from undo                                 \u2502\n\u2502  \u2022 Restore original values to buffer cache                      \u2502\n\u2502  \u2022 Reverse changes in reverse order                             \u2502\n\u2502  \u2022 UPDATE: Restore old values                                   \u2502\n\u2502  \u2022 DELETE: Re-insert deleted rows                               \u2502\n\u2502  \u2022 INSERT: Remove inserted rows                                 \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 3: GENERATE REDO FOR ROLLBACK            \u2502\n\u2502  \u2022 Yes! ROLLBACK generates redo                                 \u2502\n\u2502  \u2022 Log the undo application                                     \u2502\n\u2502  \u2022 Ensure rollback is recoverable                               \u2502\n\u2502  \u2022 Write to redo log buffer                                     \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 4: UPDATE INDEXES                        \u2502\n\u2502  \u2022 Reverse index changes                                        \u2502\n\u2502  \u2022 UPDATE: Restore old index entries                            \u2502\n\u2502  \u2022 DELETE: Re-add deleted index entries                         \u2502\n\u2502  \u2022 INSERT: Remove new index entries                             \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 5: MARK TRANSACTION ABORTED              \u2502\n\u2502  \u2022 Update transaction table                                     \u2502\n\u2502  \u2022 Mark as rolled back                                          \u2502\n\u2502  \u2022 Free transaction slot                                        \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 6: RELEASE ALL LOCKS                     \u2502\n\u2502  \u2022 Release row locks (TX)                                       \u2502\n\u2502  \u2022 Release table locks (TM)                                     \u2502\n\u2502  \u2022 Wake up waiting sessions                                     \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 7: FREE UNDO SPACE                       \u2502\n\u2502  \u2022 Mark undo segment as available                               \u2502\n\u2502  \u2022 Space can be reused immediately                              \u2502\n\u2502  \u2022 No retention needed (transaction cancelled)                  \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                           \u2502\n                           \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                   STEP 8: RETURN SUCCESS                        \u2502\n\u2502  \u2022 Return \"Rollback complete\"                                   \u2502\n\u2502  \u2022 All changes discarded                                        \u2502\n\u2502  \u2022 Data restored to pre-transaction state                       \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
Key Concepts: ROLLBACK vs COMMIT<\/h2>\n\n\n\n
Aspect<\/th>COMMIT<\/th>ROLLBACK<\/th><\/tr><\/thead>
Purpose<\/strong><\/td>Make permanent<\/td>Discard changes<\/td><\/tr>
Redo Generation<\/strong><\/td>Yes (COMMIT record)<\/td>Yes (for undo application)<\/strong><\/td><\/tr>
Undo Usage<\/strong><\/td>Marks inactive<\/td>Applies undo data<\/strong><\/td><\/tr>
Speed<\/strong><\/td>Fast<\/td>Can be slower<\/td><\/tr>
Locks<\/strong><\/td>Releases<\/td>Releases<\/td><\/tr>
Data Visibility<\/strong><\/td>Visible to all<\/td>Never visible<\/td><\/tr>
Space<\/strong><\/td>Undo retained<\/td>Undo freed<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
How ROLLBACK Works: Step-by-Step<\/h2>\n\n\n\n
Step 1-2: Locate and Apply Undo Data<\/h3>\n\n\n\n
Oracle reads undo segments and restores original values.<\/p>\n\n\n\n
Undo Application Examples:<\/strong><\/p>\n\n\n\n
-- INSERT followed by ROLLBACK\nINSERT INTO employees (employee_id, first_name, salary)\nVALUES (101, 'John', 50000);\n\n-- Undo contains: \"Delete row with ROWID=xyz\"\n\nROLLBACK;\n-- Applies undo: Removes the inserted row\n-- Buffer cache updated: Row deleted<\/code><\/pre>\n\n\n\n
-- UPDATE followed by ROLLBACK\nUPDATE employees \nSET salary = 60000 \nWHERE employee_id = 100;\n-- Old value: 50000\n-- Undo contains: \"Restore salary to 50000 for ROWID=abc\"\n\nROLLBACK;\n-- Applies undo: Restores salary to 50000\n-- Buffer cache updated: Old value restored<\/code><\/pre>\n\n\n\n
-- DELETE followed by ROLLBACK\nDELETE FROM employees WHERE employee_id = 99;\n-- Undo contains: Complete row data\n\nROLLBACK;\n-- Applies undo: Re-inserts the entire row\n-- Buffer cache updated: Row restored with all columns<\/code><\/pre>\n\n\n\n
Undo Segment Structure:<\/strong><\/p>\n\n\n\n
\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502     UNDO SEGMENT (Before Rollback)     \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502  Transaction ID: 0x0a0b0c              \u2502\n\u2502  SCN: 1234567                          \u2502\n\u2502                                        \u2502\n\u2502  Undo Record 1: DELETE row             \u2502\n\u2502  Undo Record 2: Restore salary=50000   \u2502\n\u2502  Undo Record 3: Restore dept_id=10     \u2502\n\u2502  Undo Record 4: Remove inserted row    \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n         \u2502\n         \u2502 ROLLBACK applies in reverse order\n         \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502     BUFFER CACHE (After Rollback)      \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502  All changes reversed                  \u2502\n\u2502  Data restored to original state       \u2502\n\u2502  Changes never visible to others       \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
Monitor Undo Usage:<\/strong><\/p>\n\n\n\n
-- View active transactions and undo usage\nSELECT s.username,\n       s.sid,\n       t.used_ublk * 8192\/1024\/1024 undo_mb,\n       t.start_time\nFROM v$transaction t\nJOIN v$session s ON t.ses_addr = s.saddr;<\/code><\/pre>\n\n\n\n
Step 3: Generate Redo for ROLLBACK<\/h3>\n\n\n\n
Important:** ROLLBACK generates redo to log the undo application!<\/p>\n\n\n\n
Why ROLLBACK Generates Redo?<\/p>\n\n\n\n
\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502  Scenario: ROLLBACK in progress        \u2502\n\u2502  Database crashes during ROLLBACK      \u2502\n\u2502                                        \u2502\n\u2502  On restart:                           \u2502\n\u2502  \u2022 Oracle replays redo                 \u2502\n\u2502  \u2022 Completes the ROLLBACK operation    \u2502\n\u2502  \u2022 Ensures data consistency            \u2502\n\u2502                                        \u2502\n\u2502  Without redo:                         \u2502\n\u2502  \u2022 Partial rollback = corrupted data   \u2502\n\u2502  \u2022 Database inconsistent               \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n
View Redo Generation:<\/strong><\/p>\n\n\n\n
-- Check redo size for operations\nSELECT name, value\nFROM v$sysstat\nWHERE name IN ('redo size', 'transaction rollbacks');<\/code><\/pre>\n\n\n\n
Step 4-6: Update Indexes and Release Locks<\/h3>\n\n\n\n
All indexes are updated and locks released, just like COMMIT.<\/p>\n\n\n\n
-- Transaction with locks\nUPDATE employees SET salary = 60000 WHERE employee_id = 101;\n-- Row locked\n\n-- Session 2 tries to modify same row\nUPDATE employees SET department_id = 20 WHERE employee_id = 101;\n-- WAITS\n\n-- Session 1 rolls back\nROLLBACK;\n-- Locks released immediately\n\n-- Session 2 proceeds immediately\n-- No longer waiting<\/code><\/pre>\n\n\n\n
Check Locks:<\/strong><\/p>\n\n\n\n
-- View locked objects\nSELECT s.username,\n       o.object_name,\n       s.sid,\n       s.serial#\nFROM v$locked_object lo\nJOIN dba_objects o ON lo.object_id = o.object_id\nJOIN v$session s ON lo.session_id = s.sid;<\/code><\/pre>\n\n\n\n
ROLLBACK Variations<\/h2>\n\n\n\n
1. Complete ROLLBACK<\/h3>\n\n\n\n
-- Rollback entire transaction\nINSERT INTO employees VALUES (101, 'John', 50000);\nUPDATE employees SET salary = 55000 WHERE employee_id = 100;\nDELETE FROM employees WHERE employee_id = 99;\n\nROLLBACK;\n-- All three operations reversed<\/code><\/pre>\n\n\n\n
2. ROLLBACK TO SAVEPOINT<\/h3>\n\n\n\n
-- Partial rollback using savepoints\nINSERT INTO employees VALUES (101, 'John', 50000);\nSAVEPOINT sp1;\n\nUPDATE employees SET salary = 55000 WHERE employee_id = 100;\nSAVEPOINT sp2;\n\nDELETE FROM employees WHERE employee_id = 99;\n\n-- Rollback only the DELETE\nROLLBACK TO SAVEPOINT sp2;\n-- DELETE reversed, UPDATE and INSERT remain\n\n-- Rollback UPDATE and DELETE\nROLLBACK TO SAVEPOINT sp1;\n-- UPDATE and DELETE reversed, INSERT remains\n\n-- Rollback everything\nROLLBACK;\n-- All operations reversed<\/code><\/pre>\n\n\n\n
Savepoint Example:<\/strong><\/p>\n\n\n\n
BEGIN\n    INSERT INTO orders VALUES (1001, SYSDATE);\n    SAVEPOINT order_created;\n    \n    INSERT INTO order_items VALUES (1, 1001, 'Product X', 10);\n    INSERT INTO order_items VALUES (2, 1001, 'Product Y', 5);\n    \n    -- Error occurs\n    IF some_validation_fails THEN\n        ROLLBACK TO SAVEPOINT order_created;\n        -- Order items removed, order remains\n        -- Can retry or handle error\n    ELSE\n        COMMIT;\n    END IF;\nEND;\n\/<\/code><\/pre>\n\n\n\n
3. Implicit ROLLBACK<\/h3>\n\n\n\n
-- Session disconnect without COMMIT\nUPDATE employees SET salary = 60000;\n-- Session terminates (network failure, killed, etc.)\n-- Oracle automatically issues ROLLBACK\n\n-- Exception in PL\/SQL\nBEGIN\n    UPDATE employees SET salary = 60000;\n    -- Error occurs\n    RAISE_APPLICATION_ERROR(-20001, 'Error occurred');\n    -- No explicit ROLLBACK needed\nEXCEPTION\n    WHEN OTHERS THEN\n        ROLLBACK;  -- Good practice but optional\nEND;\n\/<\/code><\/pre>\n\n\n\n
4. Transaction-Level ROLLBACK<\/h3>\n\n\n\n
-- DDL statements cannot be rolled back\nCREATE TABLE test (id NUMBER);\n-- Auto-commits, cannot rollback\n\nINSERT INTO test VALUES (1);\nROLLBACK;\n-- Table still exists, only INSERT is rolled back<\/code><\/pre>\n\n\n\n
ROLLBACK Performance<\/h2>\n\n\n\n
Why ROLLBACK Can Be Slow<\/h3>\n\n\n\n
-- Large transaction\nBEGIN\n    FOR i IN 1..1000000 LOOP\n        INSERT INTO large_table VALUES (i, 'Data'||i);\n    END LOOP;\n    -- No commit yet\nEND;\n\/\n\n-- Rollback must reverse 1 million inserts\nROLLBACK;\n-- Can take considerable time\n-- Must read and apply 1 million undo records<\/code><\/pre>\n\n\n\n
Performance Factors:<\/strong><\/p>\n\n\n\n
Factor<\/th>Impact<\/th><\/tr><\/thead>
Transaction size<\/td>Larger = slower rollback<\/td><\/tr>
Number of changes<\/td>More changes = more undo to apply<\/td><\/tr>
Index count<\/td>More indexes = more updates<\/td><\/tr>
Undo location<\/td>Disk I\/O if undo not in memory<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Monitor Long-Running ROLLBACK:<\/strong><\/p>\n\n\n\n
-- View rollback progress\nSELECT sid,\n       serial#,\n       context,\n       sofar,\n       totalwork,\n       ROUND(sofar\/totalwork*100, 2) pct_complete\nFROM v$session_longops\nWHERE opname LIKE '%rollback%'\n  AND sofar <> totalwork;<\/code><\/pre>\n\n\n\n
Common ROLLBACK Scenarios<\/h2>\n\n\n\n
Scenario 1: Error Handling<\/h3>\n\n\n\n
-- Proper error handling with ROLLBACK\nDECLARE\n    v_error VARCHAR2(200);\nBEGIN\n    UPDATE accounts SET balance = balance - 1000 WHERE account_id = 101;\n    UPDATE accounts SET balance = balance + 1000 WHERE account_id = 102;\n    \n    -- Validation\n    IF (SELECT balance FROM accounts WHERE account_id = 101) < 0 THEN\n        RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds');\n    END IF;\n    \n    COMMIT;\n    \nEXCEPTION\n    WHEN OTHERS THEN\n        ROLLBACK;\n        v_error := SQLERRM;\n        DBMS_OUTPUT.PUT_LINE('Transaction failed: ' || v_error);\n        RAISE;\nEND;\n\/<\/code><\/pre>\n\n\n\n
Scenario 2: Batch Processing with Validation<\/h3>\n\n\n\n
-- Rollback on validation failure\nDECLARE\n    v_count NUMBER;\nBEGIN\n    -- Process batch\n    FOR rec IN (SELECT * FROM staging_table) LOOP\n        INSERT INTO production_table VALUES (rec.col1, rec.col2);\n    END LOOP;\n    \n    -- Validate\n    SELECT COUNT(*) INTO v_count \n    FROM production_table \n    WHERE created_date = TRUNC(SYSDATE);\n    \n    IF v_count < 1000 THEN\n        ROLLBACK;\n        RAISE_APPLICATION_ERROR(-20002, 'Insufficient records processed');\n    ELSE\n        COMMIT;\n    END IF;\nEND;\n\/<\/code><\/pre>\n\n\n\n
Scenario 3: Multi-Step Operation<\/h3>\n\n\n\n
-- Complex operation with rollback safety\nBEGIN\n    -- Step 1: Archive old data\n    INSERT INTO employees_archive \n    SELECT * FROM employees WHERE status = 'TERMINATED';\n    SAVEPOINT archived;\n    \n    -- Step 2: Delete from main table\n    DELETE FROM employees WHERE status = 'TERMINATED';\n    SAVEPOINT deleted;\n    \n    -- Step 3: Update statistics\n    UPDATE department_stats SET employee_count = employee_count - SQL%ROWCOUNT;\n    \n    -- Validation\n    IF SQL%ROWCOUNT = 0 THEN\n        ROLLBACK TO SAVEPOINT deleted;\n        -- Restore employees, keep archive\n    END IF;\n    \n    COMMIT;\nEXCEPTION\n    WHEN OTHERS THEN\n        ROLLBACK;  -- Rollback everything\n        RAISE;\nEND;\n\/<\/code><\/pre>\n\n\n\n
ROLLBACK Best Practices<\/h2>\n\n\n\n
1. Always Handle Errors<\/h3>\n\n\n\n
-- Bad: No error handling\nBEGIN\n    UPDATE employees SET salary = 60000;\n    INSERT INTO audit_log VALUES (USER, SYSDATE);\n    -- If INSERT fails, UPDATE remains uncommitted but not rolled back\nEND;\n\n-- Good: Explicit error handling\nBEGIN\n    UPDATE employees SET salary = 60000;\n    INSERT INTO audit_log VALUES (USER, SYSDATE);\n    COMMIT;\nEXCEPTION\n    WHEN OTHERS THEN\n        ROLLBACK;\n        RAISE;\nEND;<\/code><\/pre>\n\n\n\n
2. Use Savepoints for Complex Transactions<\/h3>\n\n\n\n
-- Good: Savepoints allow partial rollback\nBEGIN\n    INSERT INTO orders VALUES (1001, SYSDATE);\n    SAVEPOINT order_inserted;\n    \n    BEGIN\n        INSERT INTO order_items VALUES (1, 1001, 'Product', 10);\n    EXCEPTION\n        WHEN OTHERS THEN\n            ROLLBACK TO SAVEPOINT order_inserted;\n            -- Order preserved, can retry items\n    END;\n    \n    COMMIT;\nEND;\n\/<\/code><\/pre>\n\n\n\n
3. Avoid Large Transactions<\/h3>\n\n\n\n
-- Bad: Huge transaction\nUPDATE large_table SET status = 'PROCESSED';  -- 10 million rows\n-- If rollback needed, takes very long\n\n-- Good: Batch processing\nLOOP\n    UPDATE large_table \n    SET status = 'PROCESSED' \n    WHERE ROWNUM <= 10000;\n    \n    EXIT WHEN SQL%ROWCOUNT = 0;\n    COMMIT;  -- Smaller units, faster rollback if needed\nEND LOOP;<\/code><\/pre>\n\n\n\n
4. Test Rollback Scenarios<\/h3>\n\n\n\n
-- Test your rollback logic\nBEGIN\n    -- Simulate error condition\n    UPDATE employees SET salary = 60000;\n    \n    -- Force error for testing\n    RAISE_APPLICATION_ERROR(-20001, 'Test error');\n    \nEXCEPTION\n    WHEN OTHERS THEN\n        ROLLBACK;\n        DBMS_OUTPUT.PUT_LINE('Rollback successful: ' || SQLERRM);\nEND;\n\/\n\n-- Verify data unchanged\nSELECT salary FROM employees WHERE employee_id = 101;<\/code><\/pre>\n\n\n\n
Monitoring ROLLBACK Operations<\/h2>\n\n\n\n
-- View rollback statistics\nSELECT name, value\nFROM v$sysstat\nWHERE name LIKE '%rollback%'\nORDER BY name;\n\n-- Active rollback operations\nSELECT s.username,\n       s.sid,\n       s.status,\n       t.used_ublk,\n       t.start_time\nFROM v$transaction t\nJOIN v$session s ON t.ses_addr = s.saddr\nWHERE s.username IS NOT NULL;\n\n-- Wait events related to rollback\nSELECT event,\n       total_waits,\n       time_waited\/100 time_waited_sec\nFROM v$system_event\nWHERE event LIKE '%undo%'\n   OR event LIKE '%rollback%'\nORDER BY time_waited DESC;<\/code><\/pre>\n\n\n\n
Quick Reference<\/h2>\n\n\n\n
ROLLBACK Syntax<\/h3>\n\n\n\n
-- Complete rollback\nROLLBACK;\n\n-- Rollback to savepoint\nROLLBACK TO SAVEPOINT savepoint_name;\n\n-- Rollback distributed transaction (requires privileges)\nROLLBACK FORCE 'transaction_id';<\/code><\/pre>\n\n\n\n
Key Queries<\/h3>\n\n\n\n
-- Check for uncommitted transactions\nSELECT COUNT(*) FROM v$transaction;\n\n-- View undo usage\nSELECT tablespace_name, status, COUNT(*) segments\nFROM dba_undo_extents\nGROUP BY tablespace_name, status;\n\n-- Check undo retention\nSHOW PARAMETER undo_retention;<\/code><\/pre>\n\n\n\n
Conclusion<\/h2>\n\n\n\n
ROLLBACK is essential for maintaining data integrity by discarding unwanted changes. Understanding its execution helps in proper error handling and transaction management.<\/p>\n\n\n\n
Key Takeaways:<\/strong><\/p>\n\n\n\n
    \n
  1. ROLLBACK applies undo data<\/strong> – Restores original values<\/li>\n\n\n\n
  2. Generates redo<\/strong> – Ensures rollback itself is recoverable<\/li>\n\n\n\n
  3. Can be slow<\/strong> – Proportional to transaction size<\/li>\n\n\n\n
  4. Releases locks<\/strong> – Like COMMIT, frees resources<\/li>\n\n\n\n
  5. Use savepoints<\/strong> – For partial rollback capability<\/li>\n\n\n\n
  6. Always handle errors<\/strong> – Explicit ROLLBACK in exceptions<\/li>\n\n\n\n
  7. Avoid large transactions<\/strong> – Makes rollback faster if needed<\/li>\n\n\n\n
  8. Undo freed immediately<\/strong> – No retention after rollback<\/li>\n<\/ol>\n\n\n\n
    Remember:<\/strong> ROLLBACK is your safety net – use it wisely to handle errors and maintain data consistency!<\/p>\n","protected":false},"excerpt":{"rendered":"
    When you execute ROLLBACK;, Oracle discards all uncommitted changes and restores data to its previous state. Understanding ROLLBACK is essential for transaction management and error handling in Oracle Database. The Complete ROLLBACK Execution Flow Key Concepts: ROLLBACK vs COMMIT Aspect COMMIT ROLLBACK Purpose Make permanent Discard changes Redo Generation Yes (COMMIT record) Yes (for undo […]<\/p>\n","protected":false},"author":1,"featured_media":5446,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-5444","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5444","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=5444"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5444\/revisions"}],"predecessor-version":[{"id":5445,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5444\/revisions\/5445"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media\/5446"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=5444"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=5444"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=5444"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}