Transactions

Using SET TRANSACTION to begin a transaction is optional. A new transaction begins implicitly with the first DML statement that you execute after you make a database connection or with the first DML statement that you execute following a COMMIT or a ROLLBACK (or any DDL statement such as TRUNCATE). You need to use SET TRANSACTION only when you want transaction attributes such as READ ONLY that are not the default.

Posted from: https://www.oreilly.com/library/view/oracle-sqlplus-the/0596007469/ch04s02.html

Beginning of a Transaction

A transaction begins when the first executable SQL statement is encountered.

An executable SQL statement is a SQL statement that generates calls to a database instance, including DML and DDL statements and the SET TRANSACTION statement.

When a transaction begins, Oracle Database assigns the transaction to an available undo data segment to record the undo entries for the new transaction. A transaction ID is not allocated until an undo segment and transaction table slot are allocated, which occurs during the first DML statement. A transaction ID is unique to a transaction and represents the undo segment number, slot, and sequence number.

End of a Transaction

A transaction can end under different circumstances.

A transaction ends when any of the following actions occurs:

• A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.In a commit, a user explicitly or implicitly requested that the changes in the transaction be made permanent. Changes made by the transaction are permanent and visible to other users only after a transaction commits.
• A user runs a DDL command such as CREATE, DROP, RENAME, or ALTER.The database issues an implicit COMMIT statement before and after every DDL statement. If the current transaction contains DML statements, then Oracle Database first commits the transaction and then runs and commits the DDL statement as a new, single-statement transaction.
• A user exits normally from most Oracle Database utilities and tools, causing the current transaction to be implicitly committed. The commit behavior when a user disconnects is application-dependent and configurable.Note:Applications should always explicitly commit or undo transactions before program termination.
• A client process terminates abnormally, causing the transaction to be implicitly rolled back using metadata stored in the transaction table and the undo segment.

After one transaction ends, the next executable SQL statement automatically starts the following transaction. The following example executes an UPDATE to start a transaction, ends the transaction with a ROLLBACK statement, and then executes an UPDATE to start a new transaction (note that the transaction IDs are different):

Posted from

In which three situations does a new transaction always start? (Choose three.)

• A. when issuing a SELECT FOR UPDATE statement after a CREATE TABLE AS SELECT statement was issued in the same session
• B. when issuing a TRUNCATE statement after a SELECT statement was issued in the same session
• C. when issuing a CREATE TABLE statement after a SELECT statement was issued in the same session
• D. when issuing the first Data Manipulation Language (DML) statement after a COMMIT OR ROLLBACK statement was issued in the same session
• E. when issuing a CREATE INDEX statement after a CREATE TABLE statement completed successfully in the same session
• F. when issuing a DML statement after a DML statement failed in the same session