Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 2 of 5
To establish the current transaction as a 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.
If you use a SET
TRANSACTION
statement, it must be the first statement in your transaction. However, a transaction need not have a SET
TRANSACTION
statement.
|
establishes the current transaction as a read-only transaction. This clause established transaction-level read consistency. See Also: Oracle8i Concepts. |
|
|
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. |
|
|
Restriction: Only the following statements are permitted in a read-only transaction: |
|
|
establishes the current transaction as a read-write transaction. This clause established statement-level read consistency, which is the default. |
|
|
Restriction: You cannot toggle between transaction-level and statement-level read consistency in the same transaction. |
|
|
specifies how transactions containing database modifications are handled. |
|
|
|
specifies serializable transaction isolation mode as defined in SQL92. If a serializable transaction contains data manipulation language (DML) that attempts to update any resource that may have been updated in a transaction uncommitted at the start of the serializable transaction, then the DML statement fails. |
|
|
Note: The |
|
|
is the default Oracle transaction behavior. If the transaction contains DML that requires row locks held by another transaction, then the DML statement waits until the row locks are released. |
|
assigns 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 |
The following statements could be run at midnight of the last day of every month to count how many ships and containers the company owns. This report would not be affected by any other user who might be adding or removing ships and/or containers.
COMMIT; SET TRANSACTION READ ONLY; SELECT COUNT(*) FROM ship; SELECT COUNT(*) FROM container; COMMIT;
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 OLTP_5
:
SET TRANSACTION USE ROLLBACK SEGMENT oltp_5;
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|