René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

set transaction [Oracle

set transaction read only;
set transaction read write;
set transaction isolation level serializable;
set transaction isolation level read committed;
set transaction use rollback segment some_rollback_segment;
set transaction name some_name;

set transaction read only name some_name;
set transaction read write name some_name;
set transaction isolation level serialiazable name some_name;
set transaction isolation level read commited name some_name;
set transaction use rollback segment some_rollback_segment name some_name;
A set transaction statement can only be executed as first statement in a transaction.

read only

A read only transaction can not change data, hence the DML statements update, insert and delete are not possible. A select statement can not be a select .. for update statement.
The other SQL statements that are allowed in read only transactions are: lock table, set role alter session and alter system.
Also, set transaction read only has no effect for sys.
A read only transaction doesn't see any changed data whose commit was issued later than the read only transaction has begun. This is called transaction-level read consistency.

read write

This is the default in Oracle. A (possibly long running) statement only sees data that was already commited when the statement has started as opposed to when the transaction has started. This is called statement-level read consistency.

read commited

This is the default in Oracle.

serializable

This sets the isolation level to serializable.

use rollback segment

This specifies the rollback segment to be used for this transaction. A transaction can not be read only when this clause is used.

name

Relevant in distributed database environments.

Thanks

Thanks to Urs Moser who spotted two typos on this page.