Oracle in a Nutshell

SET TRANSACTION

SET TRANSACTION trans_type  NAME name

Controls the type of transaction.

Keyword

trans_type

Can be one of these values:

READ ONLY

Marks the beginning of a read-only transaction. Tells the RDBMS to enforce a read-consistent view of the database for the transaction (the default is for the statement). The transaction is ended with either a COMMIT or a ROLLBACK. Only LOCK TABLE, SELECT, SELECT INTO, OPEN, FETCH, CLOSE, COMMIT, and ROLLBACK statements are permitted during a read-only transaction. Issuing other statements, such as INSERT or UPDATE, in a read-only transaction results in an ORA-1456 error.

READ WRITE

Marks the beginning of a READ WRITE transaction; this is the default.

ISOLATION LEVEL SERIALIZABLE

Similar to a READ ONLY transaction in that transaction-level read consistency is enforced instead of the default statement-level read consistency. Serializable transactions do allow changes to data. For more information about isolation levels, see Chapter 3.

ISOLATION LEVEL READ COMMITTED

If the transaction requires rows that are locked by other transactions, will wait.

USE ROLLBACK SEGMENT rbseg_name

Tells the RDBMS to use the specifically named rollback segment rbseg_name. Useful when only one rollback segment is large and a program knows that it needs to use the large rollback segment, such as during a month-end close operation.

name

Name for the transaction, which is available for the duration of the transaction.