LEARN. PRACTICE. GROW.Concept and mechanism
A transaction groups changes until COMMIT or ROLLBACK. SAVEPOINT allows later work to be undone while earlier changes remain pending. An ordinary DML statement has its own atomicity: constraint failure undoes its changes without universally rolling back earlier statements. Special modes such as error logging need separate analysis. DDL on permanent tables introduces another boundary: Oracle commits before syntactically valid DDL even if it subsequently fails, and after successful DDL. An UPDATE, failing DDL, and ROLLBACK script can therefore retain the UPDATE. Change planning must reflect these rules rather than just the script’s last line. Record transaction boundaries in the operational procedure.
Guided application
In a fictional chunked load, identify committed batches before replaying. MERGE is not business deduplication: two source rows must not attempt to update the same target row in one statement. Resolve keys and value precedence before execution. Truncating a permanent table is not a DELETE reversible with rollback; recovery must be prepared. Under READ COMMITTED, each query can observe a different committed snapshot. Two totals obtained at different times can reflect concurrent commits without dirty reads. For consistent close, combine transaction design, application contract, and DBA coordination. Do not declare a load recovered merely because a retry completed without error.
UPDATE A; SAVEPOINT s; UPDATE B; ROLLBACK TO s; leaves A pending and undoes B without committing A.
Common pitfalls
Error as full rollback; failed DDL as no commit; MERGE as winner selection; retry as idempotency.
Related topics: Model, SELECT, and result population · Functions, conversions, and data contracts · Joins, grain, and aggregation
Recover from committed state and an explicit reconciliation rule.
Reference: Transactions and implicit DDL commits · 1Z0-071 public objectives inspected 2026-09-30; revision date not published