A transaction in Oracle begins when the first executable SQL statement is encountered. An executable SQL statement is a SQL statement that generates calls to an instance, including DML and DDL statements.
When a transaction begins, Oracle assigns the transaction to an available undo tablespace to record the rollback entries for the new transaction.
A transaction ends when any of the following occurs:
■ A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
■ A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements, Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.
■ A user disconnects from Oracle. The current transaction is committed.
■ A user process terminates abnormally. The current transaction is rolled back.

After one transaction ends, the next executable SQL statement automatically starts the following transaction.

Note: Applications should always explicitly commit or undo transactions before program termination.

Committing a transaction means making permanent the changes performed by the SQL statements within the transaction.Before a transaction that modifies data is committed, the following has occurred:

■ Oracle has generated undo information. The undo information contains the old data values changed by the SQL statements of the transaction.

■ Oracle has generated redo log entries in the redo log buffer of the SGA. The redo log record contains the change to the data block and the change to the rollback block. These changes may go to disk before a transaction is committed.

■ The changes have been made to the database buffers of the SGA. These changes may go to disk before a transaction is committed.

Note: The data changes for a committed transaction, stored in the database buffers of the SGA, are not necessarily written immediately to the datafiles by the database writer (DBWn) background process. This writing takes place when it is most efficient for the database to do so. It can happen before the transaction commits or, alternatively, it can happen some time after the transaction commits.

When a transaction is committed, the following occurs:

1. The internal transaction table for the associated undo tablespace that the transaction has committed, and the corresponding unique system change number (SCN) of the transaction is assigned and recorded in the table.

2. The log writer process (LGWR) writes redo log entries in the SGA’s redo log buffers to the redo log file. It also writes the transaction’s SCN to the redo log file. This atomic event constitutes the commit of the transaction.

3. Oracle releases locks held on rows and tables.

4. Oracle marks the transaction complete.