{"id":5430,"date":"2026-02-15T15:32:37","date_gmt":"2026-02-15T10:02:37","guid":{"rendered":"https:\/\/w3buddy.com\/?p=5430"},"modified":"2026-02-15T15:34:13","modified_gmt":"2026-02-15T10:04:13","slug":"oracle-insert-statement-behind-the-scenes","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/oracle-insert-statement-behind-the-scenes\/","title":{"rendered":"Oracle INSERT Statement: Behind the Scenes"},"content":{"rendered":"\n

When you execute a simple INSERT INTO employees VALUES (101, 'John', 'Doe', 50000);<\/code> statement, Oracle performs a complex series of operations involving memory structures, redo logs, undo segments, and data files. Understanding this internal process is crucial for database administrators and developers, especially during performance tuning and troubleshooting.<\/p>\n\n\n\n

In this comprehensive guide, we’ll explore the complete journey of an INSERT statement from the moment you execute it until the data is permanently stored in the database.<\/p>\n\n\n\n

The Complete INSERT Statement 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 INSERT STATEMENT                    \u2502\n\u2502   INSERT INTO employees VALUES (101,'John','Doe',50000);        \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: SYNTAX CHECK                           \u2502\n\u2502  \u2022 Validate SQL syntax                                           \u2502\n\u2502  \u2022 Check INSERT keyword usage                                    \u2502\n\u2502  \u2022 Verify parentheses and commas                                 \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                    [Valid Syntax?]\n                           \u2502\n                    Yes \u2500\u2500\u2500\u253c\u2500\u2500\u2500 No \u2192 Error: ORA-00936\n                           \u2502         (Missing expression)\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: SEMANTIC CHECK                         \u2502\n\u2502  \u2022 Does table exist?                                             \u2502\n\u2502  \u2022 Do columns exist?                                             \u2502\n\u2502  \u2022 Does user have INSERT privilege?                              \u2502\n\u2502  \u2022 Check NOT NULL constraints                                    \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                    [Objects Valid?]\n                           \u2502\n                    Yes \u2500\u2500\u2500\u253c\u2500\u2500\u2500 No \u2192 Error: ORA-00942\/ORA-01400\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: SHARED POOL CHECK                      \u2502\n\u2502  \u2022 Generate SQL hash value                                       \u2502\n\u2502  \u2022 Search Library Cache for execution plan                       \u2502\n\u2502  \u2022 Hard parse vs Soft parse decision                             \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                    [Plan Found?]\n                           \u2502\n            \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n            \u2502                             \u2502\n           Yes                           No\n       (Soft Parse)                 (Hard Parse)\n            \u2502                             \u2502\n            \u2502                             \u25bc\n            \u2502              \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\u2510\n            \u2502              \u2502  Create Execution Plan              \u2502\n            \u2502              \u2502  \u2022 Parse SQL                        \u2502\n            \u2502              \u2502  \u2022 Validate constraints             \u2502\n            \u2502              \u2502  \u2022 Check triggers                   \u2502\n            \u2502              \u2502  \u2022 Store plan in cache              \u2502\n            \u2502              \u2514\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\u2518\n            \u2502                             \u2502\n            \u2514\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\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: ALLOCATE UNDO SPACE                    \u2502\n\u2502  \u2022 Reserve space in UNDO tablespace                              \u2502\n\u2502  \u2022 Create undo record (before image)                             \u2502\n\u2502  \u2022 Link transaction to undo segment                              \u2502\n\u2502  \u2022 Generate unique Transaction ID                                \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: ACQUIRE LOCKS                          \u2502\n\u2502  \u2022 Get TX (Transaction) lock on row                              \u2502\n\u2502  \u2022 Lock the table in Row Exclusive (RX) mode                     \u2502\n\u2502  \u2022 Prevent conflicting DML operations                            \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: DATA BLOCK SEARCH                      \u2502\n\u2502  \u2022 Find appropriate data block with free space                   \u2502\n\u2502  \u2022 Check high water mark (HWM)                                   \u2502\n\u2502  \u2022 Use freelist or ASSM (Automatic Segment Space Mgmt)          \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                    [Free Space Found?]\n                           \u2502\n            \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n            \u2502                             \u2502\n           Yes                           No\n            \u2502                             \u2502\n            \u2502                             \u25bc\n            \u2502              \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\u2510\n            \u2502              \u2502  Allocate New Data Block            \u2502\n            \u2502              \u2502  \u2022 Extend segment if needed         \u2502\n            \u2502              \u2502  \u2022 Format new block                 \u2502\n            \u2502              \u2502  \u2022 Update HWM                       \u2502\n            \u2502              \u2514\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\u2518\n            \u2502                             \u2502\n            \u2514\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\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: CHECK CONSTRAINTS                      \u2502\n\u2502  \u2022 Validate NOT NULL constraints                                \u2502\n\u2502  \u2022 Check CHECK constraints                                       \u2502\n\u2502  \u2022 Verify UNIQUE constraints                                     \u2502\n\u2502  \u2022 Validate FOREIGN KEY constraints                              \u2502\n\u2502  \u2022 Verify PRIMARY KEY uniqueness                                 \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                    [Constraints Valid?]\n                           \u2502\n                    Yes \u2500\u2500\u2500\u253c\u2500\u2500\u2500 No \u2192 Error: ORA-00001\/ORA-02291\n                           \u2502         Rollback changes\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: GENERATE REDO RECORD                   \u2502\n\u2502  \u2022 Create redo change vector                                     \u2502\n\u2502  \u2022 Write to Redo Log Buffer (memory)                             \u2502\n\u2502  \u2022 Record: Block address, SCN, change info                       \u2502\n\u2502  \u2022 Ensure recoverability                                         \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 9: INSERT INTO BUFFER CACHE               \u2502\n\u2502  \u2022 Copy data block to Database Buffer Cache                      \u2502\n\u2502  \u2022 Mark block as \"dirty\" (modified)                              \u2502\n\u2502  \u2022 Insert new row into block                                     \u2502\n\u2502  \u2022 Update block header (row directory)                           \u2502\n\u2502  \u2022 Assign ROWID to new 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\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 10: UPDATE INDEXES                        \u2502\n\u2502  \u2022 Insert entries in ALL indexes on table                        \u2502\n\u2502  \u2022 Generate redo for index changes                               \u2502\n\u2502  \u2022 Update index blocks in buffer cache                           \u2502\n\u2502  \u2022 Mark index blocks as dirty                                    \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 11: FIRE TRIGGERS (if any)                \u2502\n\u2502  \u2022 Execute BEFORE INSERT triggers                                \u2502\n\u2502  \u2022 Execute AFTER INSERT triggers                                 \u2502\n\u2502  \u2022 Triggers can modify data or reject INSERT                     \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 12: RETURN SUCCESS                        \u2502\n\u2502  \u2022 Return \"1 row inserted\" message                               \u2502\n\u2502  \u2022 Transaction remains UNCOMMITTED                               \u2502\n\u2502  \u2022 Changes visible only to current session                       \u2502\n\u2502  \u2022 Locks held until COMMIT or 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            WAITING FOR COMMIT OR ROLLBACK                        \u2502\n\u2502  Changes are in memory (Buffer Cache) - NOT on disk yet         \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\n                           \u2502\n                \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n                \u2502                     \u2502\n             COMMIT              ROLLBACK\n                \u2502                     \u2502\n                \u25bc                     \u25bc\n    \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510  \u250c\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 13: COMMIT  \u2502  \u2502 STEP 13: ROLLBACK    \u2502\n    \u2502 \u2022 Write redo to  \u2502  \u2502 \u2022 Read undo segment  \u2502\n    \u2502   disk (LGWR)    \u2502  \u2502 \u2022 Restore old values \u2502\n    \u2502 \u2022 Release locks  \u2502  \u2502 \u2022 Release locks      \u2502\n    \u2502 \u2022 Make permanent \u2502  \u2502 \u2022 Discard changes    \u2502\n    \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518  \u2514\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
Detailed Step-by-Step Breakdown<\/h2>\n\n\n\n
Step 1: Syntax Check<\/h3>\n\n\n\n
Oracle’s SQL parser first validates the syntax of your INSERT statement.<\/p>\n\n\n\n
What Oracle Checks:<\/strong><\/p>\n\n\n\n
    \n
  • Proper INSERT syntax<\/li>\n\n\n\n
  • Correct keyword usage (INSERT, INTO, VALUES)<\/li>\n\n\n\n
  • Balanced parentheses and proper comma placement<\/li>\n\n\n\n
  • Valid SQL structure<\/li>\n<\/ul>\n\n\n\n
    Examples of Syntax Errors:<\/strong><\/p>\n\n\n\n
    -- Missing INTO keyword\nINSERT employees VALUES (101, 'John');  \n-- ORA-00928: missing SELECT keyword\n\n-- Missing VALUES keyword\nINSERT INTO employees (101, 'John');  \n-- ORA-00936: missing expression\n\n-- Unbalanced parentheses\nINSERT INTO employees VALUES (101, 'John';  \n-- ORA-00907: missing right parenthesis\n\n-- Correct syntax\nINSERT INTO employees VALUES (101, 'John', 'Doe', 50000);\n-- Success<\/code><\/pre>\n\n\n\n
    Step 2: Semantic Check<\/h3>\n\n\n\n
    After syntax validation, Oracle verifies the logical correctness.<\/p>\n\n\n\n
    What Oracle Verifies:<\/strong><\/p>\n\n\n\n
      \n
    • Table exists in the database<\/li>\n\n\n\n
    • Columns exist (if column list specified)<\/li>\n\n\n\n
    • User has INSERT privilege on the table<\/li>\n\n\n\n
    • Data types are compatible<\/li>\n\n\n\n
    • Column count matches value count<\/li>\n<\/ul>\n\n\n\n
      Examples:<\/strong><\/p>\n\n\n\n
      -- Table doesn't exist\nINSERT INTO employee_data VALUES (101, 'John');\n-- ORA-00942: table or view does not exist\n\n-- Column doesn't exist\nINSERT INTO employees (emp_id, first_name, invalid_col) \nVALUES (101, 'John', 'Value');\n-- ORA-00904: \"INVALID_COL\": invalid identifier\n\n-- Column count mismatch\nINSERT INTO employees (employee_id, first_name) \nVALUES (101, 'John', 'Doe');\n-- ORA-00913: too many values\n\n-- No INSERT privilege\nINSERT INTO hr.salary_data VALUES (101, 50000);\n-- ORA-01031: insufficient privileges\n\n-- Correct\nINSERT INTO employees (employee_id, first_name, last_name) \nVALUES (101, 'John', 'Doe');\n-- Success<\/code><\/pre>\n\n\n\n
      Step 3: Shared Pool Check (Parse Phase)<\/h3>\n\n\n\n
      Oracle generates a hash value and searches the Library Cache.<\/p>\n\n\n\n
      Two Scenarios:<\/strong><\/p>\n\n\n\n
      A) Soft Parse (Fast – Cache Hit)<\/h4>\n\n\n\n
      -- First execution (Hard Parse)\nINSERT INTO employees VALUES (101, 'John', 'Doe', 50000);\n\n-- Second execution with same literal values (Soft Parse)\nINSERT INTO employees VALUES (101, 'John', 'Doe', 50000);\n-- \u2713 Plan found in cache - reused<\/code><\/pre>\n\n\n\n
      B) Hard Parse (Slow – Cache Miss)<\/h4>\n\n\n\n
      -- Different literal values = different SQL hash\nINSERT INTO employees VALUES (102, 'Jane', 'Smith', 60000);\n-- \u2717 New SQL hash - requires hard parse<\/code><\/pre>\n\n\n\n
      Best Practice – Use Bind Variables:<\/strong><\/p>\n\n\n\n
      -- Without bind variables (Bad - multiple hard parses)\nINSERT INTO employees VALUES (101, 'John', 'Doe', 50000);\nINSERT INTO employees VALUES (102, 'Jane', 'Smith', 60000);\nINSERT INTO employees VALUES (103, 'Bob', 'Johnson', 55000);\n-- Result: 3 different SQL statements = 3 hard parses\n\n-- With bind variables (Good - single hard parse)\nINSERT INTO employees VALUES (:emp_id, :fname, :lname, :sal);\n-- Result: 1 hard parse, reused for all executions<\/code><\/pre>\n\n\n\n
      Check Parse Statistics:<\/strong><\/p>\n\n\n\n
      SET LINESIZE 150\nSET PAGESIZE 50\nCOLUMN name FORMAT A30\nCOLUMN value FORMAT 999,999,999\n\nSELECT name, value\nFROM v$sysstat\nWHERE name LIKE '%parse%'\nORDER BY name;<\/code><\/pre>\n\n\n\n
      Step 4: Allocate Undo Space<\/h3>\n\n\n\n
      Before making any changes, Oracle creates a “before image” in the undo tablespace.<\/p>\n\n\n\n
      Why Undo is Needed:<\/strong><\/p>\n\n\n\n
        \n
      • ROLLBACK capability: Restore data if transaction fails<\/li>\n\n\n\n
      • Read Consistency: Other users see data as it was before your change<\/li>\n\n\n\n
      • Flashback queries: Query data as of past time<\/li>\n\n\n\n
      • Recovery: Rollback uncommitted transactions during crash recovery<\/li>\n<\/ul>\n\n\n\n
        Undo Process:<\/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\u2500\u2510\n\u2502      BEFORE INSERT EXECUTION            \u2502\n\u2502                                         \u2502\n\u2502  employees Table:                       \u2502\n\u2502  \u250c\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510         \u2502\n\u2502  \u2502 100 \u2502 King \u2502 CEO  \u2502 100000 \u2502         \u2502\n\u2502  \u2514\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518         \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\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\u2510\n\u2502      UNDO SEGMENT CREATION              \u2502\n\u2502                                         \u2502\n\u2502  Undo Record:                           \u2502\n\u2502  \u2022 Transaction ID: 0x0a0b0c             \u2502\n\u2502  \u2022 SCN: 1234567                         \u2502\n\u2502  \u2022 Operation: INSERT (no before image)  \u2502\n\u2502  \u2022 Table: EMPLOYEES                     \u2502\n\u2502  \u2022 Space reserved for potential 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\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
        SET LINESIZE 180\nSET PAGESIZE 100\nCOLUMN tablespace_name FORMAT A20\nCOLUMN status FORMAT A10\nCOLUMN size_mb FORMAT 999,999.99\nCOLUMN used_mb FORMAT 999,999.99\nCOLUMN free_mb FORMAT 999,999.99\n\nSELECT tablespace_name,\n       status,\n       ROUND(SUM(bytes)\/1024\/1024, 2) size_mb,\n       ROUND(SUM(bytes - NVL(maxbytes,bytes))\/1024\/1024, 2) used_mb,\n       ROUND(SUM(maxbytes - bytes)\/1024\/1024, 2) free_mb\nFROM dba_data_files\nWHERE tablespace_name LIKE '%UNDO%'\nGROUP BY tablespace_name, status;<\/code><\/pre>\n\n\n\n
        Check Active Transactions:<\/strong><\/p>\n\n\n\n
        SET LINESIZE 180\nCOLUMN username FORMAT A15\nCOLUMN status FORMAT A10\nCOLUMN undo_seg FORMAT A15\nCOLUMN used_undo_mb FORMAT 999.99\n\nSELECT s.username,\n       s.status,\n       t.used_ublk * 8192\/1024\/1024 used_undo_mb,\n       t.start_time,\n       s.sql_id\nFROM v$transaction t\nJOIN v$session s ON t.ses_addr = s.saddr\nORDER BY t.used_ublk DESC;<\/code><\/pre>\n\n\n\n
        Step 5: Acquire Locks<\/h3>\n\n\n\n
        Oracle automatically acquires locks to prevent conflicting operations.<\/p>\n\n\n\n
        Lock Types During INSERT:<\/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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502              LOCK HIERARCHY                          \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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502                                                      \u2502\n\u2502  1. TABLE LOCK (TM Lock)                             \u2502\n\u2502     Mode: RX (Row Exclusive)                         \u2502\n\u2502     Purpose: Prevent DDL on table                    \u2502\n\u2502     Allows: Other DML operations                     \u2502\n\u2502                                                      \u2502\n\u2502  2. ROW LOCK (TX Lock)                               \u2502\n\u2502     Mode: Exclusive on new row                       \u2502\n\u2502     Purpose: Prevent duplicate inserts (if PK)       \u2502\n\u2502     Scope: Specific row being inserted               \u2502\n\u2502                                                      \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\u2518<\/code><\/pre>\n\n\n\n
        Lock Compatibility Matrix:<\/strong><\/p>\n\n\n\n
        Current Lock<\/th>Can Another Session INSERT?<\/th>Can Another Session UPDATE?<\/th>Can Another Session DELETE?<\/th>Can Another Session SELECT?<\/th><\/tr><\/thead>
        RX (Row Exclusive)<\/td>\u2705 Yes<\/td>\u2705 Yes (different rows)<\/td>\u2705 Yes (different rows)<\/td>\u2705 Yes<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
        View Active Locks:<\/strong><\/p>\n\n\n\n
        SET LINESIZE 200\nSET PAGESIZE 100\nCOLUMN object_name FORMAT A30\nCOLUMN lock_type FORMAT A15\nCOLUMN mode_held FORMAT A15\nCOLUMN username FORMAT A15\n\nSELECT s.username,\n       o.object_name,\n       DECODE(l.type,\n              'TM', 'Table Lock',\n              'TX', 'Transaction Lock',\n              l.type) lock_type,\n       DECODE(l.lmode,\n              0, 'None',\n              1, 'Null',\n              2, 'Row Share',\n              3, 'Row Exclusive',\n              4, 'Share',\n              5, 'Share Row Exclusive',\n              6, 'Exclusive',\n              l.lmode) mode_held,\n       s.sid,\n       s.serial#\nFROM v$lock l\nJOIN v$session s ON l.sid = s.sid\nJOIN dba_objects o ON l.id1 = o.object_id\nWHERE s.username IS NOT NULL\nORDER BY o.object_name, s.username;<\/code><\/pre>\n\n\n\n
        Example – Lock Contention:<\/strong><\/p>\n\n\n\n
        -- Session 1\nINSERT INTO employees VALUES (101, 'John', 'Doe', 50000);\n-- Lock acquired, transaction open (not committed)\n\n-- Session 2 (tries to insert same primary key)\nINSERT INTO employees VALUES (101, 'Jane', 'Smith', 60000);\n-- Waits for Session 1 to COMMIT or ROLLBACK\n-- If Session 1 commits: ORA-00001 (unique constraint violated)\n-- If Session 1 rollbacks: Insert succeeds<\/code><\/pre>\n\n\n\n
        Step 6: Data Block Search and Space Allocation<\/h3>\n\n\n\n
        Oracle searches for a data block with sufficient free space.<\/p>\n\n\n\n
        Free Space Management Methods:<\/strong><\/p>\n\n\n\n
        A) Manual Segment Space Management (MSSM) – Legacy<\/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\u2510\n\u2502        FREELIST METHOD              \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\u2524\n\u2502                                     \u2502\n\u2502  1. Check segment header            \u2502\n\u2502  2. Traverse freelist               \u2502\n\u2502  3. Find block with PCTFREE space   \u2502\n\u2502  4. Allocate row in block           \u2502\n\u2502                                     \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\u2518<\/code><\/pre>\n\n\n\n
        B) Automatic Segment Space Management (ASSM) – Modern<\/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\u2510\n\u2502        ASSM METHOD (Bitmap)         \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\u2524\n\u2502                                     \u2502\n\u2502  1. Check bitmap blocks             \u2502\n\u2502  2. Find block with adequate space  \u2502\n\u2502  3. Use level 1-3 bitmaps           \u2502\n\u2502  4. More efficient, less contention \u2502\n\u2502                                     \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\u2518<\/code><\/pre>\n\n\n\n
        High Water Mark (HWM) Concept:<\/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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                  TABLE SEGMENT                         \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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502                                                        \u2502\n\u2502  \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2510    \u2502\n\u2502  \u2502Block1\u2502Block2\u2502Block3\u2502Block4\u2502      \u2502      \u2502      \u2502    \u2502\n\u2502  \u2502 Used \u2502 Used \u2502 Used \u2502 Used \u2502 Free \u2502 Free \u2502 Free \u2502    \u2502\n\u2502  \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2518    \u2502\n\u2502                         \u2191                              \u2502\n\u2502                    High Water Mark                     \u2502\n\u2502                                                        \u2502\n\u2502  \u2022 Full table scans read up to HWM                     \u2502\n\u2502  \u2022 New INSERTs may use blocks below HWM if space exists\u2502\n\u2502  \u2022 HWM moves forward, never backward                   \u2502\n\u2502                                                        \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\u2518<\/code><\/pre>\n\n\n\n
        Check Table Block Usage:<\/strong><\/p>\n\n\n\n
        SET LINESIZE 180\nSET PAGESIZE 100\nCOLUMN owner FORMAT A15\nCOLUMN table_name FORMAT A30\nCOLUMN blocks FORMAT 999,999\nCOLUMN empty_blocks FORMAT 999,999\nCOLUMN avg_row_len FORMAT 9,999\n\nSELECT owner,\n       table_name,\n       blocks,\n       empty_blocks,\n       num_rows,\n       avg_row_len,\n       ROUND((blocks * 8192)\/1024\/1024, 2) size_mb\nFROM dba_tables\nWHERE owner = 'HR'\n  AND table_name = 'EMPLOYEES';<\/code><\/pre>\n\n\n\n
        Analyze Block Space Usage:<\/strong><\/p>\n\n\n\n
        -- Gather detailed segment statistics\nEXEC DBMS_SPACE.SPACE_USAGE('HR','EMPLOYEES','TABLE', -\n     null, null);\n\n-- View block allocation\nSET LINESIZE 150\nCOLUMN segment_name FORMAT A30\nCOLUMN blocks FORMAT 999,999\nCOLUMN extents FORMAT 9,999\n\nSELECT segment_name,\n       segment_type,\n       tablespace_name,\n       blocks,\n       extents,\n       ROUND(bytes\/1024\/1024, 2) size_mb\nFROM dba_segments\nWHERE owner = 'HR'\n  AND segment_name = 'EMPLOYEES';<\/code><\/pre>\n\n\n\n
        Step 7: Constraint Validation<\/h3>\n\n\n\n
        Oracle validates all constraints defined on the table.<\/p>\n\n\n\n
        Constraint Types Checked:<\/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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502           CONSTRAINT VALIDATION ORDER              \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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502                                                    \u2502\n\u2502  1. NOT NULL Constraints                           \u2502\n\u2502     \u2514\u2500 Check mandatory columns have values         \u2502\n\u2502                                                    \u2502\n\u2502  2. CHECK Constraints                              \u2502\n\u2502     \u2514\u2500 Validate business rules                     \u2502\n\u2502                                                    \u2502\n\u2502  3. UNIQUE Constraints                             \u2502\n\u2502     \u2514\u2500 Ensure no duplicate values                  \u2502\n\u2502                                                    \u2502\n\u2502  4. PRIMARY KEY Constraints                        \u2502\n\u2502     \u2514\u2500 Combination of NOT NULL + UNIQUE            \u2502\n\u2502                                                    \u2502\n\u2502  5. FOREIGN KEY Constraints                        \u2502\n\u2502     \u2514\u2500 Verify parent record exists                 \u2502\n\u2502                                                    \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\u2518<\/code><\/pre>\n\n\n\n
        Constraint Examples:<\/strong><\/p>\n\n\n\n
        -- Create table with constraints\nCREATE TABLE employees (\n    employee_id    NUMBER(6) PRIMARY KEY,\n    first_name     VARCHAR2(20) NOT NULL,\n    last_name      VARCHAR2(25) NOT NULL,\n    email          VARCHAR2(50) UNIQUE,\n    salary         NUMBER(8,2) CHECK (salary > 0),\n    department_id  NUMBER(4),\n    hire_date      DATE DEFAULT SYSDATE,\n    CONSTRAINT emp_dept_fk \n        FOREIGN KEY (department_id) \n        REFERENCES departments(department_id)\n);\n\n-- NOT NULL violation\nINSERT INTO employees (employee_id, last_name) \nVALUES (101, 'Doe');\n-- ORA-01400: cannot insert NULL into (\"HR\".\"EMPLOYEES\".\"FIRST_NAME\")\n\n-- CHECK constraint violation\nINSERT INTO employees (employee_id, first_name, last_name, salary) \nVALUES (101, 'John', 'Doe', -5000);\n-- ORA-02290: check constraint (HR.SYS_C007123) violated\n\n-- UNIQUE constraint violation\nINSERT INTO employees (employee_id, first_name, last_name, email) \nVALUES (101, 'John', 'Doe', 'john@company.com');\nINSERT INTO employees (employee_id, first_name, last_name, email) \nVALUES (102, 'Jane', 'Smith', 'john@company.com');\n-- ORA-00001: unique constraint (HR.SYS_C007124) violated\n\n-- PRIMARY KEY violation\nINSERT INTO employees (employee_id, first_name, last_name) \nVALUES (101, 'John', 'Doe');\nINSERT INTO employees (employee_id, first_name, last_name) \nVALUES (101, 'Jane', 'Smith');\n-- ORA-00001: unique constraint (HR.SYS_C007125) violated\n\n-- FOREIGN KEY violation\nINSERT INTO employees (employee_id, first_name, last_name, department_id) \nVALUES (101, 'John', 'Doe', 999);\n-- ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found\n\n-- Correct INSERT\nINSERT INTO employees (employee_id, first_name, last_name, email, salary, department_id) \nVALUES (101, 'John', 'Doe', 'john@company.com', 50000, 10);\n-- 1 row inserted<\/code><\/pre>\n\n\n\n
        View Table Constraints:<\/strong><\/p>\n\n\n\n
        SET LINESIZE 180\nSET PAGESIZE 100\nCOLUMN constraint_name FORMAT A30\nCOLUMN constraint_type FORMAT A10\nCOLUMN search_condition FORMAT A40\nCOLUMN r_constraint_name FORMAT A30\n\nSELECT constraint_name,\n       DECODE(constraint_type,\n              'P', 'PRIMARY',\n              'U', 'UNIQUE',\n              'R', 'FOREIGN',\n              'C', 'CHECK',\n              constraint_type) constraint_type,\n       search_condition,\n       r_constraint_name,\n       status\nFROM dba_constraints\nWHERE owner = 'HR'\n  AND table_name = 'EMPLOYEES'\nORDER BY constraint_type;<\/code><\/pre>\n\n\n\n
        Step 8: Generate Redo Record<\/h3>\n\n\n\n
        Oracle generates redo entries for crash recovery and replication.<\/p>\n\n\n\n
        Redo Record Components:<\/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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502              REDO RECORD STRUCTURE                   \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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502                                                      \u2502\n\u2502  1. Change Vector                                    \u2502\n\u2502     \u2022 Operation type: INSERT                         \u2502\n\u2502     \u2022 Object identifier (table)                      \u2502\n\u2502     \u2022 Data block address (DBA)                       \u2502\n\u2502                                                      \u2502\n\u2502  2. SCN (System Change Number)                       \u2502\n\u2502     \u2022 Unique identifier for change                   \u2502\n\u2502     \u2022 Used for recovery ordering                     \u2502\n\u2502                                                      \u2502\n\u2502  3. Data Changes                                     \u2502\n\u2502     \u2022 New row data                                   \u2502\n\u2502     \u2022 Block header changes                           \u2502\n\u2502     \u2022 Index changes                                  \u2502\n\u2502                                                      \u2502\n\u2502  4. Transaction Information                          \u2502\n\u2502     \u2022 Transaction ID                                 \u2502\n\u2502     \u2022 Undo segment information                       \u2502\n\u2502                                                      \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\u2518<\/code><\/pre>\n\n\n\n
        Redo Generation Flow:<\/strong><\/p>\n\n\n\n
        INSERT Statement\n       \u2502\n       \u25bc\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502  Generate Redo   \u2502\n\u2502  Change Vector   \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\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\u2510\n\u2502  Write to Redo   \u2502\n\u2502  Log Buffer      \u2502\n\u2502  (Memory - SGA)  \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\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\u2510      COMMIT or\n\u2502  Wait for LGWR   \u2502\u25c4\u2500\u2500\u2500 Log Buffer Full or\n\u2502  (Log Writer)    \u2502      3 Seconds Timeout\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\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\u2510\n\u2502  Write to Redo   \u2502\n\u2502  Log Files       \u2502\n\u2502  (Disk)          \u2502\n\u2514\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 Redo Generation:<\/strong><\/p>\n\n\n\n
        SET LINESIZE 150\nSET PAGESIZE 50\nCOLUMN name FORMAT A30\nCOLUMN value FORMAT 999,999,999,999\n\n-- Check redo size generated\nSELECT name, value\nFROM v$sysstat\nWHERE name IN ('redo size', \n               'redo writes',\n               'redo blocks written',\n               'redo write time');\n\n-- Check redo log buffer usage\nSELECT name, value\nFROM v$sysstat\nWHERE name LIKE '%redo%buffer%';<\/code><\/pre>\n\n\n\n
        View Redo Log Files:<\/strong><\/p>\n\n\n\n
        SET LINESIZE 180\nCOLUMN member FORMAT A60\nCOLUMN status FORMAT A10\n\nSELECT l.group#,\n       l.thread#,\n       l.sequence#,\n       l.bytes\/1024\/1024 size_mb,\n       l.status,\n       lf.member\nFROM v$log l\nJOIN v$logfile lf ON l.group# = lf.group#\nORDER BY l.group#;<\/code><\/pre>\n\n\n\n
        Check Current Redo Log:<\/strong><\/p>\n\n\n\n
        SELECT group#, \n       sequence#, \n       bytes\/1024\/1024 size_mb,\n       members,\n       status\nFROM v$log\nWHERE status = 'CURRENT';<\/code><\/pre>\n\n\n\n
        Step 9: Insert Data into Buffer Cache<\/h3>\n\n\n\n
        The actual row insertion happens in memory (Database Buffer Cache).<\/p>\n\n\n\n
        Buffer Cache Process:<\/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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502             DATABASE BUFFER CACHE                      \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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502                                                        \u2502\n\u2502  BEFORE INSERT:                                        \u2502\n\u2502  \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\u2510               \u2502\n\u2502  \u2502 Data Block 1234                      \u2502              \u2502\n\u2502  \u2502 \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\u2510 \u2502               \u2502\n\u2502  \u2502 \u2502 Row 1: Emp 100, King, CEO       \u2502 \u2502               \u2502\n\u2502  \u2502 \u2502 Row 2: Emp 99, Smith, Manager   \u2502 \u2502               \u2502\n\u2502  \u2502 \u2502 Free Space: 4000 bytes          \u2502 \u2502               \u2502\n\u2502  \u2502 \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\u2518 \u2502               \u2502\n\u2502  \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\u2518               \u2502\n\u2502                                                        \u2502\n\u2502  AFTER INSERT:                                         \u2502\n\u2502  \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\u2510               \u2502\n\u2502  \u2502 Data Block 1234 (DIRTY)             \u2502               \u2502\n\u2502  \u2502 \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\u2510 \u2502               \u2502\n\u2502  \u2502 \u2502 Row 1: Emp 100, King, CEO       \u2502 \u2502               \u2502\n\u2502  \u2502 \u2502 Row 2: Emp 99, Smith, Manager   \u2502 \u2502               \u2502\n\u2502  \u2502 \u2502 Row 3: Emp 101, Doe, Analyst    \u2502 \u2502 \u2190 NEW ROW     \u2502\n\u2502  \u2502 \u2502 Free Space: 3920 bytes          \u2502 \u2502               \u2502\n\u2502  \u2502 \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\u2518 \u2502               \u2502\n\u2502  \u2502 Status: DIRTY (modified)            \u2502               \u2502\n\u2502  \u2502 SCN: 1234567                        \u2502               \u2502\n\u2502  \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\u2518               \u2502\n\u2502                                                        \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\u2518<\/code><\/pre>\n\n\n\n
        Row Insertion Details:<\/strong><\/p>\n\n\n\n
          \n
        • 1. Block Header Update: Row directory entry added <\/li>\n\n\n\n
        • 2. ROWID Assignment: Unique identifier for the row <\/li>\n\n\n\n
        • 3. Row Data Storage: Actual column values stored <\/li>\n\n\n\n
        • 4. Block SCN Update: System Change Number updated <\/li>\n\n\n\n
        • 5. Dirty Flag Set: Block marked for future write to disk<\/li>\n<\/ul>\n\n\n\n
          ROWID Structure:<\/strong><\/p>\n\n\n\n
          ROWID Format: OOOOOOFFFBBBBBBRRR\n\nExample: AAAHYtAAEAAAACXAAA\n\nWhere:\n  OOOOOO = Data Object Number (6 chars)\n  FFF    = Relative File Number (3 chars)\n  BBBBBB = Block Number (6 chars)\n  RRR    = Row Number (3 chars)<\/code><\/pre>\n\n\n\n
          View Buffer Cache Statistics:<\/strong><\/p>\n\n\n\n
          SET LINESIZE 180\nSET PAGESIZE 100\nCOLUMN name FORMAT A30\nCOLUMN value FORMAT 999,999,999,999\n\nSELECT name, value\nFROM v$sysstat\nWHERE name IN ('db block gets',\n               'consistent gets',\n               'physical reads',\n               'physical writes',\n               'db block changes');\n\n-- Calculate buffer cache hit ratio\nSELECT ROUND((1 - (phy.value \/ (bg.value + cg.value))) * 100, 2) || '%' \n       AS \"Buffer Cache Hit Ratio\"\nFROM v$sysstat phy,\n     v$sysstat bg,\n     v$sysstat cg\nWHERE phy.name = 'physical reads'\n  AND bg.name = 'db block gets'\n  AND cg.name = 'consistent gets';<\/code><\/pre>\n\n\n\n
          View Dirty Blocks:<\/strong><\/p>\n\n\n\n
          SELECT COUNT(*) dirty_blocks\nFROM v$bh\nWHERE status = 'xcur'  -- Exclusive current (dirty)\n  AND dirty = 'Y';<\/code><\/pre>\n\n\n\n
          Step 10: Update Indexes<\/h3>\n\n\n\n
          All indexes on the table must be updated with the new row entry.<\/p>\n\n\n\n
          Index Update Process:<\/p>\n\n\n\n
          Table: EMPLOYEES\n\u251c\u2500 Primary Key Index: EMP_PK (employee_id)\n\u251c\u2500 Unique Index: EMP_EMAIL_UK (email)\n\u2514\u2500 Non-Unique Index: EMP_DEPT_IDX (department_id)\n\nINSERT INTO employees VALUES (101, 'John', 'Doe', 'john@co.com', 50000, 10);\n\nIndex Updates Required:\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\u2510\n\u2502  1. EMP_PK Index                     \u2502\n\u2502     Insert: 101 \u2192 ROWID(Block,Row)   \u2502\n\u2502     Check uniqueness                 \u2502\n\u2502                                      \u2502\n\u2502  2. EMP_EMAIL_UK Index               \u2502\n\u2502     Insert: 'john@co.com' \u2192 ROWID    \u2502\n\u2502     Check uniqueness                 \u2502\n\u2502                                      \u2502\n\u2502  3. EMP_DEPT_IDX Index               \u2502\n\u2502     Insert: 10 \u2192 ROWID               \u2502\n\u2502     (no uniqueness check)            \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\u2518<\/code><\/pre>\n\n\n\n
          Impact of Multiple Indexes:<\/strong><\/p>\n\n\n\n
          -- Table with many indexes (example)\nCREATE TABLE test_table (\n    id NUMBER PRIMARY KEY,\n    col1 VARCHAR2(100),\n    col2 VARCHAR2(100),\n    col3 DATE,\n    col4 NUMBER\n);\n\nCREATE INDEX idx1 ON test_table(col1);\nCREATE INDEX idx2 ON test_table(col2);\nCREATE INDEX idx3 ON test_table(col3);\nCREATE INDEX idx4 ON test_table(col4);\nCREATE INDEX idx5 ON test_table(col1, col2);\n\n-- Single INSERT must update:\n-- 1 Primary Key Index + 5 Secondary Indexes = 6 index updates!\n-- More indexes = Slower INSERT performance<\/code><\/pre>\n\n\n\n
          Monitor Index Usage and Cost:<\/strong><\/p>\n\n\n\n
          SET LINESIZE 180\nSET PAGESIZE 100\nCOLUMN table_name FORMAT A20\nCOLUMN index_name FORMAT A30\nCOLUMN uniqueness FORMAT A10\nCOLUMN status FORMAT A10\n\nSELECT table_name,\n       index_name,\n       uniqueness,\n       status,\n       num_rows,\n       distinct_keys,\n       leaf_blocks,\n       blevel\nFROM dba_indexes\nWHERE owner = 'HR'\n  AND table_name = 'EMPLOYEES'\nORDER BY index_name;<\/code><\/pre>\n\n\n\n
          Check Index Fragmentation:<\/strong><\/p>\n\n\n\n
          ANALYZE INDEX hr.emp_pk VALIDATE STRUCTURE;\n\nSELECT name,\n       height,\n       lf_rows,\n       del_lf_rows,\n       ROUND((del_lf_rows \/ NULLIF(lf_rows,0)) * 100, 2) pct_deleted\nFROM index_stats;<\/code><\/pre>\n\n\n\n
          Tip: For bulk inserts, consider: <\/strong><\/p>\n\n\n\n
            \n
          • Dropping indexes before insert <\/li>\n\n\n\n
          • Rebuilding indexes after insert <\/li>\n\n\n\n
          • Using `APPEND` hint to bypass index maintenance temporarily<\/li>\n<\/ul>\n\n\n\n
            Step 11: Fire Triggers (If Defined)<\/strong><\/p>\n\n\n\n
            If triggers are defined on the table, they execute at specific points.<\/p>\n\n\n\n
            Trigger Types and Timing:<\/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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502              TRIGGER EXECUTION ORDER                    \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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502                                                         \u2502\n\u2502  1. BEFORE STATEMENT Trigger                           \u2502\n\u2502     \u2514\u2500 Executes once before INSERT                     \u2502\n\u2502                                                         \u2502\n\u2502  2. BEFORE ROW Trigger                                 \u2502\n\u2502     \u2514\u2500 Executes for each row being inserted           \u2502\n\u2502     \u2514\u2500 Can modify :NEW values                         \u2502\n\u2502                                                         \u2502\n\u2502  3. ACTUAL INSERT OPERATION                            \u2502\n\u2502     \u2514\u2500 Row inserted into table                        \u2502\n\u2502                                                         \u2502\n\u2502  4. AFTER ROW Trigger                                  \u2502\n\u2502     \u2514\u2500 Executes for each row inserted                 \u2502\n\u2502     \u2514\u2500 Cannot modify :NEW values                      \u2502\n\u2502                                                         \u2502\n\u2502  5. AFTER STATEMENT Trigger                            \u2502\n\u2502     \u2514\u2500 Executes once after all rows inserted          \u2502\n\u2502                                                         \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\u2518<\/code><\/pre>\n\n\n\n
            Trigger Examples:<\/strong><\/p>\n\n\n\n
            -- Auto-generate primary key\nCREATE OR REPLACE TRIGGER emp_bir\nBEFORE INSERT ON employees\nFOR EACH ROW\nBEGIN\n    IF :NEW.employee_id IS NULL THEN\n        SELECT emp_seq.NEXTVAL INTO :NEW.employee_id FROM dual;\n    END IF;\nEND;\n\/\n\n-- Audit trigger\nCREATE OR REPLACE TRIGGER emp_air\nAFTER INSERT ON employees\nFOR EACH ROW\nBEGIN\n    INSERT INTO audit_log (\n        table_name,\n        operation,\n        username,\n        timestamp,\n        new_values\n    ) VALUES (\n        'EMPLOYEES',\n        'INSERT',\n        USER,\n        SYSDATE,\n        'EmpID: ' || :NEW.employee_id\n    );\nEND;\n\/\n\n-- Validation trigger\nCREATE OR REPLACE TRIGGER emp_salary_check\nBEFORE INSERT ON employees\nFOR EACH ROW\nBEGIN\n    IF :NEW.salary < 1000 THEN\n        RAISE_APPLICATION_ERROR(-20001, \n            'Salary must be at least 1000');\n    END IF;\nEND;\n\/\n\n-- Test the triggers\nINSERT INTO employees (first_name, last_name, salary, department_id)\nVALUES ('John', 'Doe', 50000, 10);\n-- Trigger emp_bir: Generates employee_id automatically\n-- INSERT operation: Inserts the row\n-- Trigger emp_air: Logs to audit_log table\n-- Success: 1 row inserted<\/code><\/pre>\n\n\n\n
            View Triggers on Table:<\/strong><\/p>\n\n\n\n
            SET LINESIZE 200\nSET PAGESIZE 100\nCOLUMN trigger_name FORMAT A30\nCOLUMN triggering_event FORMAT A20\nCOLUMN status FORMAT A10\n\nSELECT trigger_name,\n       trigger_type,\n       triggering_event,\n       status,\n       SUBSTR(trigger_body, 1, 50) || '...' body_preview\nFROM dba_triggers\nWHERE owner = 'HR'\n  AND table_name = 'EMPLOYEES'\nORDER BY trigger_name;<\/code><\/pre>\n\n\n\n
            Disable\/Enable Triggers:<\/strong><\/p>\n\n\n\n
            sql<\/p>\n\n\n\n
            -- Disable specific trigger\nALTER TRIGGER hr.emp_bir DISABLE;\n\n-- Disable all triggers on table\nALTER TABLE hr.employees DISABLE ALL TRIGGERS;\n\n-- Enable trigger\nALTER TRIGGER hr.emp_bir ENABLE;\n\n-- Enable all triggers\nALTER TABLE hr.employees ENABLE ALL TRIGGERS;<\/code><\/pre>\n\n\n\n
            Performance Tip:<\/strong> Triggers add overhead to INSERT operations. For bulk inserts:<\/p>\n\n\n\n
              \n
            • Consider disabling triggers temporarily<\/li>\n\n\n\n
            • Re-enable after bulk load completes<\/li>\n\n\n\n
            • Manually run validation\/audit logic if needed<\/li>\n<\/ul>\n\n\n\n
              Step 12: Return Success Message<\/h3>\n\n\n\n
               After all operations complete successfully, Oracle returns a success message.<\/p>\n\n\n\n
              Transaction State at This Point:<\/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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502          TRANSACTION STATE: UNCOMMITTED                 \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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502                                                         \u2502\n\u2502  Changes Made (in memory):                             \u2502\n\u2502  \u2713 Row inserted in Buffer Cache                        \u2502\n\u2502  \u2713 Redo records in Redo Log Buffer                     \u2502\n\u2502  \u2713 Undo records created                                \u2502\n\u2502  \u2713 Indexes updated in Buffer Cache                     \u2502\n\u2502  \u2713 Locks acquired and held                             \u2502\n\u2502                                                         \u2502\n\u2502  What's on Disk:                                       \u2502\n\u2502  \u2717 Table data NOT written to datafile yet              \u2502\n\u2502  \u2717 Index data NOT written to datafile yet              \u2502\n\u2502  \u2713 Redo MAY be written (if buffer flushed)            \u2502\n\u2502                                                         \u2502\n\u2502  Visibility:                                           \u2502\n\u2502  \u2713 Current session can see changes                     \u2502\n\u2502  \u2717 Other sessions CANNOT see changes                   \u2502\n\u2502  \u2717 Changes NOT permanent yet                           \u2502\n\u2502                                                         \u2502\n\u2502  Next Steps Required:                                  \u2502\n\u2502  \u2192 COMMIT to make changes permanent                    \u2502\n\u2502  \u2192 ROLLBACK to discard changes                         \u2502\n\u2502                                                         \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\u2518\nTest Transaction Isolation:<\/code><\/pre>\n\n\n\n
              -- Session 1\nINSERT INTO employees (employee_id, first_name, last_name, salary)\nVALUES (101, 'John', 'Doe', 50000);\n-- 1 row inserted (not committed)\n\nSELECT employee_id, first_name FROM employees WHERE employee_id = 101;\n-- Result: 101, John (visible to Session 1)\n\n-- Session 2 (different user or connection)\nSELECT employee_id, first_name FROM employees WHERE employee_id = 101;\n-- Result: No rows found (not visible to Session 2)\n\n-- Session 1\nCOMMIT;\n-- Commit complete\n\n-- Session 2\nSELECT employee_id, first_name FROM employees WHERE employee_id = 101;\n-- Result: 101, John (now visible to all sessions)<\/code><\/pre>\n\n\n\n
              Check Uncommitted Transactions:<\/strong><\/p>\n\n\n\n
              sql<\/p>\n\n\n\n
              SET LINESIZE 180\nCOLUMN username FORMAT A15\nCOLUMN status FORMAT A10\nCOLUMN sql_text FORMAT A60\n\nSELECT s.sid,\n       s.serial#,\n       s.username,\n       s.status,\n       t.start_time,\n       t.used_ublk,\n       sq.sql_text\nFROM v$transaction t\nJOIN v$session s ON t.ses_addr = s.saddr\nLEFT JOIN v$sql sq ON s.sql_id = sq.sql_id\nWHERE s.username IS NOT NULL;<\/code><\/pre>\n\n\n\n
              Step 13A: COMMIT Process<\/h3>\n\n\n\n
              When you issue COMMIT, Oracle makes all changes permanent.<\/p>\n\n\n\n
              COMMIT Execution Flow:<\/p>\n\n\n\n
              USER ISSUES: COMMIT;\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\u2510\n\u2502  1. Generate Commit SCN              \u2502\n\u2502     \u2022 Unique system change number    \u2502\n\u2502     \u2022 Marks point of consistency     \u2502\n\u2514\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\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\u2510\n\u2502  2. Write Redo to Disk (LGWR)        \u2502\n\u2502     \u2022 Force redo log buffer flush    \u2502\n\u2502     \u2022 Write commit record to redo    \u2502\n\u2502     \u2022 Wait for write completion      \u2502\n\u2514\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\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\u2510\n\u2502  3. Release Locks                    \u2502\n\u2502     \u2022 Release TX (row) locks         \u2502\n\u2502     \u2022 Release TM (table) locks       \u2502\n\u2502     \u2022 Other sessions can proceed     \u2502\n\u2514\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\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\u2510\n\u2502  4. Mark Transaction Complete        \u2502\n\u2502     \u2022 Update transaction table       \u2502\n\u2502     \u2022 Clear transaction ID           \u2502\n\u2502     \u2022 Return \"Commit complete\"       \u2502\n\u2514\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\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\u2510\n\u2502  5. Background Process (DBWR)        \u2502\n\u2502     \u2022 Eventually writes dirty blocks \u2502\n\u2502     \u2022 Writes to datafiles            \u2502\n\u2502     \u2022 Done asynchronously            \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\u2518<\/code><\/pre>\n\n\n\n
              Important:<\/strong> COMMIT does NOT immediately write data blocks to disk. It only guarantees redo is written, which is sufficient for recovery.<\/p>\n\n\n\n
              Monitor COMMIT Performance:<\/strong><\/p>\n\n\n\n
              SET LINESIZE 150\nCOLUMN name FORMAT A40\nCOLUMN value FORMAT 999,999,999\n\nSELECT name, value\nFROM v$sysstat\nWHERE name IN ('user commits',\n               'transaction rollbacks',\n               'redo writes',\n               'redo write time'\n);<\/code><\/pre>\n\n\n\n
              Step 13B: ROLLBACK Process<\/h3>\n\n\n\n
              If you issue ROLLBACK, Oracle discards all changes. <\/p>\n\n\n\n
              ROLLBACK Execution Flow:<\/p>\n\n\n\n
              USER ISSUES: ROLLBACK;\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\u2510\n\u2502  1. Read Undo Segment                \u2502\n\u2502     \u2022 Locate undo records            \u2502\n\u2502     \u2022 Read \"before image\" data       \u2502\n\u2514\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\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\u2510\n\u2502  2. Restore Original Values          \u2502\n\u2502     \u2022 Apply undo to buffer cache     \u2502\n\u2502     \u2022 Remove inserted row            \u2502\n\u2502     \u2022 Restore block to previous state\u2502\n\u2514\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\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\u2510\n\u2502  3. Generate Redo for Rollback       \u2502\n\u2502     \u2022 Yes, rollback is logged!       \u2502\n\u2502     \u2022 Ensures recoverability         \u2502\n\u2514\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\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\u2510\n\u2502  4. Release Locks                    \u2502\n\u2502     \u2022 Release all locks              \u2502\n\u2502     \u2022 Other sessions can proceed     \u2502\n\u2514\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\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\u2510\n\u2502  5. Mark Transaction Aborted         \u2502\n\u2502     \u2022 Clear transaction ID           \u2502\n\u2502     \u2022 Free undo space                \u2502\n\u2502     \u2022 Return \"Rollback complete\"     \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\u2518<\/code><\/pre>\n\n\n\n
              Example:<\/strong><\/p>\n\n\n\n
              -- Start transaction\nINSERT INTO employees (employee_id, first_name, last_name, salary)\nVALUES (101, 'John', 'Doe', 50000);\n-- 1 row inserted\n\n-- Check the data\nSELECT employee_id, first_name FROM employees WHERE employee_id = 101;\n-- Result: 101, John\n\n-- Decide to cancel\nROLLBACK;\n-- Rollback complete\n\n-- Verify data is gone\nSELECT employee_id, first_name FROM employees WHERE employee_id = 101;\n-- Result: No rows found<\/code><\/pre>\n\n\n\n
              Performance Optimization for INSERT Operations<\/h2>\n\n\n\n
              1. Use Bulk INSERT Operations<\/h3>\n\n\n\n
              Single Row INSERT (Slow):<\/strong><\/p>\n\n\n\n
              BEGIN\n    FOR i IN 1..10000 LOOP\n        INSERT INTO employees VALUES (i, 'Name'||i, 'Last'||i, 50000, 10);\n    END LOOP;\n    COMMIT;\nEND;\n\/\n-- Very slow: 10,000 individual INSERT operations<\/code><\/pre>\n\n\n\n
              Bulk INSERT with FORALL (Fast):<\/strong><\/p>\n\n\n\n
              DECLARE\n    TYPE t_emp_id IS TABLE OF NUMBER INDEX BY PLS_INTEGER;\n    TYPE t_fname IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;\n    \n    l_emp_ids t_emp_id;\n    l_fnames  t_fname;\nBEGIN\n    FOR i IN 1..10000 LOOP\n        l_emp_ids(i) := i;\n        l_fnames(i) := 'Name' || i;\n    END LOOP;\n    \n    FORALL i IN 1..10000\n        INSERT INTO employees (employee_id, first_name, last_name, salary, department_id)\n        VALUES (l_emp_ids(i), l_fnames(i), 'Last'||i, 50000, 10);\n    \n    COMMIT;\nEND;\n\/\n-- Much faster: Bulk operation with single context switch<\/code><\/pre>\n\n\n\n
              Direct Path INSERT (Fastest):<\/strong><\/p>\n\n\n\n
              INSERT \/*+ APPEND *\/ INTO employees\nSELECT employee_id, first_name, last_name, salary, department_id\nFROM temp_employees;\nCOMMIT;<\/code><\/pre>\n\n\n\n
              Benefits:<\/p>\n\n\n\n
                \n
              • Writes directly above HWM<\/li>\n\n\n\n
              • Minimal redo generation<\/li>\n\n\n\n
              • No index maintenance during insert (indexes invalidated)<\/li>\n\n\n\n
              • Requires exclusive lock on table<\/li>\n<\/ul>\n\n\n\n
                2. Disable Constraints and Triggers for Bulk Loads<\/h3>\n\n\n\n
                -- Disable constraints\nALTER TABLE employees DISABLE CONSTRAINT emp_dept_fk;\nALTER TABLE employees DISABLE CONSTRAINT emp_email_uk;\n\n-- Disable triggers\nALTER TABLE employees DISABLE ALL TRIGGERS;\n\n-- Perform bulk insert\nINSERT \/*+ APPEND *\/ INTO employees\nSELECT * FROM external_data;\nCOMMIT;\n\n-- Re-enable constraints (validates data)\nALTER TABLE employees ENABLE CONSTRAINT emp_dept_fk;\nALTER TABLE employees ENABLE CONSTRAINT emp_email_uk;\n\n-- Re-enable triggers\nALTER TABLE employees ENABLE ALL TRIGGERS;<\/code><\/pre>\n\n\n\n
                3. Use NOLOGGING for Large Inserts<\/h3>\n\n\n\n
                -- Set table to NOLOGGING\nALTER TABLE employees NOLOGGING;\n\n-- Perform insert with minimal redo\nINSERT \/*+ APPEND *\/ INTO employees\nSELECT * FROM staging_table;\nCOMMIT;\n\n-- Set back to LOGGING\nALTER TABLE employees LOGGING;\n\n-- Note: Take backup after bulk load\n-- NOLOGGING operations are not recoverable<\/code><\/pre>\n\n\n\n
                4. Optimize Index Strategy<\/h3>\n\n\n\n
                -- For large bulk inserts:\n\n-- Step 1: Drop indexes\nDROP INDEX emp_dept_idx;\nDROP INDEX emp_salary_idx;\n\n-- Step 2: Perform bulk insert\nINSERT \/*+ APPEND *\/ INTO employees\nSELECT * FROM source_data;\nCOMMIT;\n\n-- Step 3: Rebuild indexes (faster than maintaining during insert)\nCREATE INDEX emp_dept_idx ON employees(department_id) NOLOGGING;\nCREATE INDEX emp_salary_idx ON employees(salary) NOLOGGING;\n\n-- Step 4: Gather statistics\nEXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES', CASCADE => TRUE);<\/code><\/pre>\n\n\n\n
                5. Use Partitioning for Very Large Tables<\/h3>\n\n\n\n
                -- Create partitioned table\nCREATE TABLE employees (\n    employee_id NUMBER,\n    first_name VARCHAR2(20),\n    hire_date DATE,\n    department_id NUMBER\n)\nPARTITION BY RANGE (hire_date) (\n    PARTITION emp_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),\n    PARTITION emp_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),\n    PARTITION emp_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'))\n);\n\n-- Insert into specific partition (faster)\nINSERT INTO employees PARTITION (emp_2025)\nVALUES (101, 'John', TO_DATE('2025-06-15', 'YYYY-MM-DD'), 10);<\/code><\/pre>\n\n\n\n
                6. Monitor and Tune INSERT Performance<\/h3>\n\n\n\n
                Track INSERT Statistics:<\/strong><\/p>\n\n\n\n
                SET LINESIZE 200\nCOLUMN sql_text FORMAT A60\nCOLUMN executions FORMAT 999,999\nCOLUMN rows_processed FORMAT 999,999,999\nCOLUMN elapsed_sec FORMAT 999,990.99\n\nSELECT sql_id,\n       sql_text,\n       executions,\n       rows_processed,\n       ROUND(elapsed_time\/1000000, 2) elapsed_sec,\n       ROUND(cpu_time\/1000000, 2) cpu_sec,\n       buffer_gets,\n       disk_reads,\n       ROUND(rows_processed\/NULLIF(elapsed_time\/1000000,0), 2) rows_per_sec\nFROM v$sql\nWHERE UPPER(sql_text) LIKE '%INSERT INTO%'\n  AND sql_text NOT LIKE '%v$sql%'\n  AND executions > 0\nORDER BY elapsed_time DESC\nFETCH FIRST 10 ROWS ONLY;<\/code><\/pre>\n\n\n\n
                Check Wait Events:<\/strong><\/p>\n\n\n\n
                SET LINESIZE 180\nCOLUMN event FORMAT A40\nCOLUMN total_waits FORMAT 999,999,999\nCOLUMN time_waited_sec FORMAT 999,999.99\n\nSELECT event,\n       total_waits,\n       ROUND(time_waited\/100, 2) time_waited_sec,\n       ROUND(average_wait\/100, 4) avg_wait_sec\nFROM v$system_event\nWHERE event LIKE '%log%'\n   OR event LIKE '%write%'\n   OR event LIKE '%lock%'\nORDER BY time_waited DESC\nFETCH FIRST 15 ROWS ONLY;<\/code><\/pre>\n\n\n\n
                Common INSERT Errors and Solutions<\/h2>\n\n\n\n
                Error: ORA-00001 (Unique Constraint Violated)<\/h3>\n\n\n\n
                -- Problem\nINSERT INTO employees (employee_id, first_name, last_name)\nVALUES (100, 'John', 'Doe');\n-- ORA-00001: unique constraint (HR.EMP_PK) violated\n\n-- Solution 1: Check existing data\nSELECT employee_id FROM employees WHERE employee_id = 100;\n\n-- Solution 2: Use different value\nINSERT INTO employees (employee_id, first_name, last_name)\nVALUES (101, 'John', 'Doe');\n\n-- Solution 3: Use sequence\nINSERT INTO employees (employee_id, first_name, last_name)\nVALUES (emp_seq.NEXTVAL, 'John', 'Doe');<\/code><\/pre>\n\n\n\n
                Error: ORA-01400 (Cannot Insert NULL)<\/h3>\n\n\n\n
                -- Problem\nINSERT INTO employees (employee_id, last_name)\nVALUES (101, 'Doe');\n-- ORA-01400: cannot insert NULL into (\"HR\".\"EMPLOYEES\".\"FIRST_NAME\")\n\n-- Solution: Provide value for NOT NULL column\nINSERT INTO employees (employee_id, first_name, last_name)\nVALUES (101, 'John', 'Doe');<\/code><\/pre>\n\n\n\n
                Error: ORA-02291 (Integrity Constraint Violated – Parent Key Not Found)<\/h3>\n\n\n\n
                -- Problem\nINSERT INTO employees (employee_id, first_name, last_name, department_id)\nVALUES (101, 'John', 'Doe', 999);\n-- ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated\n\n-- Solution 1: Check parent table\nSELECT department_id FROM departments WHERE department_id = 999;\n-- No rows found\n\n-- Solution 2: Use valid foreign key\nSELECT department_id FROM departments;\n-- Result: 10, 20, 30, 40\n\nINSERT INTO employees (employee_id, first_name, last_name, department_id)\nVALUES (101, 'John', 'Doe', 10);\n-- Success<\/code><\/pre>\n\n\n\n
                Error: ORA-01654 (Unable to Extend Index)<\/h3>\n\n\n\n
                -- Problem: Index tablespace full\nINSERT INTO employees VALUES (...);\n-- ORA-01654: unable to extend index HR.EMP_PK\n\n-- Solution 1: Add datafile to tablespace\nALTER TABLESPACE users ADD DATAFILE \n'\/u01\/app\/oracle\/oradata\/orcl\/users02.dbf' SIZE 100M;\n\n-- Solution 2: Resize existing datafile\nALTER DATABASE DATAFILE '\/u01\/app\/oracle\/oradata\/orcl\/users01.dbf' \nRESIZE 500M;\n\n-- Solution 3: Enable autoextend\nALTER DATABASE DATAFILE '\/u01\/app\/oracle\/oradata\/orcl\/users01.dbf' \nAUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;<\/code><\/pre>\n\n\n\n
                Error: ORA-30036 (Unable to Extend Undo Segment)<\/h3>\n\n\n\n
                -- Problem: Undo tablespace full\nINSERT INTO large_table SELECT * FROM source_table;\n-- ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'\n\n-- Solution 1: Add datafile to undo tablespace\nALTER TABLESPACE undotbs1 ADD DATAFILE \n'\/u01\/app\/oracle\/oradata\/orcl\/undotbs02.dbf' SIZE 1G;\n\n-- Solution 2: Increase undo retention (if needed)\nALTER SYSTEM SET undo_retention = 900;  -- 15 minutes\n\n-- Solution 3: Commit more frequently for large operations\nBEGIN\n    FOR i IN 1..1000000 LOOP\n        INSERT INTO large_table VALUES (...);\n        IF MOD(i, 10000) = 0 THEN\n            COMMIT;  -- Commit every 10,000 rows\n        END IF;\n    END LOOP;\n    COMMIT;\nEND;<\/code><\/pre>\n\n\n\n
                Best Practices for INSERT Operations<\/h2>\n\n\n\n
                1. Always Use Bind Variables<\/h3>\n\n\n\n
                -- Bad (hard parse for each execution)\nINSERT INTO employees VALUES (101, 'John', 'Doe', 50000);\nINSERT INTO employees VALUES (102, 'Jane', 'Smith', 60000);\n\n-- Good (single hard parse, reused plan)\nINSERT INTO employees VALUES (:emp_id, :fname, :lname, :sal);<\/code><\/pre>\n\n\n\n
                2. Commit at Appropriate Intervals<\/h3>\n\n\n\n
                -- For OLTP: Commit after each business transaction\nINSERT INTO orders VALUES (...);\nINSERT INTO order_items VALUES (...);\nCOMMIT;  -- Small, frequent commits\n\n-- For Batch Loads: Commit in batches\nBEGIN\n    FOR i IN 1..1000000 LOOP\n        INSERT INTO staging VALUES (...);\n        IF MOD(i, 50000) = 0 THEN\n            COMMIT;  -- Commit every 50,000 rows\n        END IF;\n    END LOOP;\n    COMMIT;\nEND;<\/code><\/pre>\n\n\n\n
                3. Use Appropriate INSERT Method<\/h3>\n\n\n\n
                -- Single row: Regular INSERT\nINSERT INTO employees VALUES (101, 'John', 'Doe', 50000);\n\n-- Multiple rows from SELECT: INSERT...SELECT\nINSERT INTO employees_archive\nSELECT * FROM employees WHERE hire_date < '2020-01-01';\n\n-- Bulk load: Direct path with APPEND\nINSERT \/*+ APPEND *\/ INTO employees\nSELECT * FROM external_table;<\/code><\/pre>\n\n\n\n
                4. Monitor Resource Usage<\/h3>\n\n\n\n
                -- Create monitoring script\nSET LINESIZE 180\nSET PAGESIZE 100\n\nPROMPT ========================================\nPROMPT   INSERT OPERATION MONITORING\nPROMPT ========================================\n\nPROMPT Undo Usage:\nSELECT tablespace_name, \n       ROUND(SUM(bytes)\/1024\/1024, 2) used_mb\nFROM dba_undo_extents\nWHERE status = 'ACTIVE'\nGROUP BY tablespace_name;\n\nPROMPT \nPROMPT Active Transactions:\nSELECT s.username, t.used_ublk, t.start_time\nFROM v$transaction t\nJOIN v$session s ON t.ses_addr = s.saddr;\n\nPROMPT \nPROMPT Locked Objects:\nSELECT object_name, locked_mode\nFROM v$locked_object lo\nJOIN dba_objects o ON lo.object_id = o.object_id;<\/code><\/pre>\n\n\n\n
                5. Handle Errors Gracefully<\/h3>\n\n\n\n
                -- Use exception handling in PL\/SQL\nBEGIN\n    INSERT INTO employees (employee_id, first_name, last_name, salary)\n    VALUES (101, 'John', 'Doe', 50000);\n    COMMIT;\nEXCEPTION\n    WHEN DUP_VAL_ON_INDEX THEN\n        DBMS_OUTPUT.PUT_LINE('Error: Duplicate employee ID');\n        ROLLBACK;\n    WHEN OTHERS THEN\n        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);\n        ROLLBACK;\nEND;\n\/<\/code><\/pre>\n\n\n\n
                Quick Reference Card<\/h2>\n\n\n\n
                INSERT Syntax Variations<\/h3>\n\n\n\n
                -- Basic INSERT\nINSERT INTO table_name VALUES (val1, val2, val3);\n\n-- INSERT with column list\nINSERT INTO table_name (col1, col2) VALUES (val1, val2);\n\n-- INSERT multiple rows (Oracle 23c+)\nINSERT INTO employees VALUES \n    (101, 'John', 'Doe'),\n    (102, 'Jane', 'Smith'),\n    (103, 'Bob', 'Johnson');\n\n-- INSERT from SELECT\nINSERT INTO target_table\nSELECT * FROM source_table WHERE condition;\n\n-- INSERT with subquery\nINSERT INTO employees (employee_id, first_name, department_name)\nSELECT emp_seq.NEXTVAL, first_name, \n       (SELECT department_name FROM departments WHERE department_id = 10)\nFROM temp_employees;\n\n-- INSERT with DEFAULT values\nINSERT INTO employees (employee_id, first_name, hire_date)\nVALUES (101, 'John', DEFAULT);  -- Uses column default\n\n-- INSERT with RETURNING clause\nINSERT INTO employees (employee_id, first_name, last_name)\nVALUES (emp_seq.NEXTVAL, 'John', 'Doe')\nRETURNING employee_id INTO :new_id;<\/code><\/pre>\n\n\n\n
                Conclusion<\/h2>\n\n\n\n
                Understanding the internal execution of an INSERT statement is crucial for effective Oracle database management and performance tuning. From the initial syntax check through constraint validation, redo generation, and final commit or rollback, each step plays a vital role in maintaining data integrity and consistency.<\/p>\n\n\n\n
                Key Takeaways:<\/strong><\/p>\n\n\n\n
                  \n
                1. INSERT is not immediate<\/strong> – Changes are in memory until COMMIT<\/li>\n\n\n\n
                2. Undo segments are crucial<\/strong> – Enable ROLLBACK capability and read consistency<\/li>\n\n\n\n
                3. Redo logs ensure recoverability<\/strong> – Even if data blocks aren’t written to disk yet<\/li>\n\n\n\n
                4. Indexes add overhead<\/strong> – Each index must be updated during INSERT<\/li>\n\n\n\n
                5. Constraints are validated<\/strong> – Before the row is physically inserted<\/li>\n\n\n\n
                6. Locks are automatically acquired<\/strong> – Released only at COMMIT or ROLLBACK<\/li>\n\n\n\n
                7. Bulk operations are faster<\/strong> – Use FORALL, APPEND hint, or direct path loads<\/li>\n\n\n\n
                8. Transaction control matters<\/strong> – Commit at appropriate intervals<\/li>\n<\/ol>\n\n\n\n
                  By mastering these concepts and using the monitoring scripts provided, you’ll be well-prepared for database administration interviews and equipped to optimize INSERT performance in production environments.<\/p>\n\n\n\n
                  Remember:<\/strong> A well-understood INSERT process is the foundation of efficient data manipulation and transaction management!<\/p>\n","protected":false},"excerpt":{"rendered":"
                  When you execute a simple INSERT INTO employees VALUES (101, ‘John’, ‘Doe’, 50000); statement, Oracle performs a complex series of operations involving memory structures, redo logs, undo segments, and data files. Understanding this internal process is crucial for database administrators and developers, especially during performance tuning and troubleshooting. In this comprehensive guide, we’ll explore the […]<\/p>\n","protected":false},"author":1,"featured_media":5432,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-5430","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\/5430","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=5430"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5430\/revisions"}],"predecessor-version":[{"id":5433,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5430\/revisions\/5433"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media\/5432"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=5430"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=5430"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=5430"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}