The entire goal of a transaction, a set of SQL statements executed together as a unit of work, is to take the database from one consistent state to another consistent state.
To accomplish this goal, transactions are atomic as well—the entire set of successful work performed by a transaction is either entirely committed and made permanent or rolled back and undone.
Just like a statement, the transaction is an atomic unit of work. Upon receipt of “success” from the database after committing a transaction, you know that all of the work performed by the transaction has been made persistent.
DDL andAtomicity
It is worth noting that there is a certain class of statements in Oracle that are atomic— but only at the statement level. Data Definition Language (DDL) statements are implemented in a manner such that
\ 1.\ They begin by committing any outstanding work, ending any transaction you might already have in place.
\2.\ They perform the DDL operation, such as a CREATE TABLE.
\ 3.\ They commit the DDL operation if it was successful or roll back the DDL operation otherwise.
This means that any time you issue a DDL statement such as CREATE, ALTER, and so on, you must expect your existing transaction to be immediately committed and the subsequent DDL command to be performed and either committed and made durable or rolled back in the event of any error.
DDL does not break the ACID concepts in any way, but the fact that it commits is something you definitely need to be aware of.
Durability
Normally, when a transaction is committed, its changes are permanent; you can rely on those changes being in the database, even if the database crashed the instant after the commit completed.
This is not true, however, in two specific cases:
•\ You use the WRITE extensions available in the COMMIT statement.
•\ You issue COMMITs in a nondistributed (accesses only a single database, no database links) PL/SQL block of code.
We’ll look at each in turn.
