This Oracle tutorial explains how to use the Oracle SET TRANSACTION announcement with syntax and examples.
In Oracle, the SET TRANSACTION declaration is used to set a transaction as read-only, set a transaction as read/write, set a transaction’s isolation level, assign a name to a transaction, or assign a rollback phase to a transaction.
The syntax for the SET TRANSACTION assertion in Oracle/PLSQL is:
SET TRANSACTION [ READ ONLY | READ WRITE ] [ ISOLATION LEVEL [ SERIALIZE | READ COMMITED ] [ USE ROLLBACK SEGMENT 'segment_name' ] [ NAME 'transaction_name' ];
Parameters or Arguments
READ ONLY Optional. If specified, it units the transaction as a read-only transaction. READ WRITE Optional. If specified, it units the transaction as a read/write transaction. ISOLATION LEVEL Optional. If specified, it has two options: ISOLATION LEVEL SERIALIZE – If a transaction tries to replace a resource that has been up to date by another transaction and uncommitted, the transaction will fail. ISOLATION LEVEL READ COMMITTED – If a transaction requires row locks held by way of another transaction, the transaction will wait till the row locks are released. USE ROLLBACK SEGMENT Optional. If specified, it assigns the transaction to a rollback phase identified by way of ‘segment_name’ which is the section name enclosed in quotes. NAME Assigns a name to the transaction identified by ‘transaction_name’ which is enclosed in quotes.
Let’s seem to be at an example that suggests how to use the SET TRANSACTION assertion in Oracle.
First, let’s seem at how to set a transaction as read-only using the SET TRANSACTION statement.
SET TRANSACTION READ ONLY NAME 'RO_example';
This SET TRANSACTION example would set the modern-day transaction to read-only and assign it the name of ‘RO_example’.
Next, let’s seem to be at how to set a transaction as read/write the use of the SET TRANSACTION statement.
SET TRANSACTION READ WRITE NAME 'RW_example';