Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

SET TRANSACTION

Purpose

Use the SET TRANSACTION statement to establish the current transaction as read only or read write, establish its isolation level, or assign it to a specified rollback segment.

The operations performed by a SET TRANSACTION statement affect only your current transaction, not other users or other transactions. Your transaction ends whenever you issue a COMMIT or ROLLBACK statement. Oracle implicitly commits the current transaction before and after executing a data definition language (DDL) statement.

See Also:

COMMIT and ROLLBACK

Prerequisites

If you use a SET TRANSACTION statement, then it must be the first statement in your transaction. However, a transaction need not have a SET TRANSACTION statement.

Syntax

set_transaction::=

Text description of statements_1024.gif follows
Text description of set_transaction


Semantics

READ ONLY

The READ ONLY clause establishes the current transaction as a read-only transaction. This clause established transaction-level read consistency.

All subsequent queries in that transaction only see changes committed before the transaction began. Read-only transactions are useful for reports that run multiple queries against one or more tables while other users update these same tables.


Note:

This clause is not supported for the user SYS. That is, queries by SYS will return changes made during the transaction even if SYS has set the transaction to be READ ONLY.


Restriction on Read-only Transactions

Only the following statements are permitted in a read-only transaction:

READ WRITE

Specify READ WRITE to establish the current transaction as a read/write transaction. This clause establishes statement-level read consistency, which is the default.

Restriction on Read/Write Transactions

You cannot toggle between transaction-level and statement-level read consistency in the same transaction.

ISOLATION LEVEL Clause

Use the ISOLATION LEVEL clause to specify how transactions containing database modifications are handled.

USE ROLLBACK SEGMENT Clause

Specify USE ROLLBACK SEGMENT to assign the current transaction to the specified rollback segment. This clause also implicitly establishes the transaction as a read/write transaction.

This clause lets you to assign transactions of different types to rollback segments of different sizes. For example:

You cannot use the READ ONLY clause and the USE ROLLBACK SEGMENT clause in a single SET TRANSACTION statement or in different statements in the same transaction. Read-only transactions do not generate rollback information and therefore are not assigned rollback segments.

NAME Clause

Use the NAME clause to assign a name to the current transaction. This clause is especially useful in distributed database environments when you must identify and resolve in-doubt transactions. The text string is limited to 255 bytes.

If you specify a name for a distributed transaction, then when the transaction commits, the name becomes the commit comment, overriding any comment specified explicitly in the COMMIT statement.

Examples

Setting Transactions: Examples

The following statements could be run at midnight of the last day of every month to count the products and quantities on hand in the Toronto warehouse in the sample Order Entry (oe) schema. This report would not be affected by any other user who might be adding or removing inventory to a different warehouse.

COMMIT; 

SET TRANSACTION READ ONLY NAME 'Toronto'; 

SELECT product_id, quantity_on_hand FROM inventories
   WHERE warehouse_id = 5; 

COMMIT; 

The first COMMIT statement ensures that SET TRANSACTION is the first statement in the transaction. The last COMMIT statement does not actually make permanent any changes to the database. It simply ends the read-only transaction.

The following statement assigns your current transaction to the rollback segment rs_one:

SET TRANSACTION USE ROLLBACK SEGMENT rs_one;