Using transactions in Oracle 10g
Learning objective
After completing this topic, you should be able to identify the functions of different SQL statements and recognize the function of data locking.
1. Transactions in Oracle 10g
A transaction begins when an executable SQL statement runs.
Oracle assigns the transaction to an available undo segment in an undo tablespace.
This segment records the undo entries for the new transaction.
The entries in the undo tablespace are used when a ROLLBACK
command is issued.
These are used to return the data to its last committed state before the current transaction.
At certain points during your session, when you have confirmed that the changes you have made are correct, you execute a COMMIT
command to make the changes specified in the current transaction permanent.
This ends the current transaction and fixes the state of the database, preventing the transactions carried out since the previous commit - or since the start of the session - from being undone.
After the transaction ends, the next SQL statement you write begins a new transaction.
Even if you don't execute a COMMIT
command, the transaction can still end if
- you execute a data definition language (DDL) command
- you disconnect from the database
- the session terminates abnormally
- you execute a data definition language (DDL) command
- When you execute a data definition language (DDL) command - such as
CREATE
,ALTER
, orDROP
-
Oracle commits all data manipulation language (DML) statements - such asADD
andDELETE
- in the transaction. It then runs and commits the DDL statement. - you disconnect from the database
- If you disconnect from the database, the current transaction is committed automatically.
- the session terminates abnormally
- If your session terminates abnormally, Oracle uses the relevant undo segment to roll the database back to its state before the current transaction began.
You can divide a transaction into smaller parts using what are called savepoints.
You use the SAVEPOINT
command to create a savepoint. Each savepoint has a name so that you can specify the one to which you wish to roll back.
Rather than rolling back to the previous commit state, you can instead roll back to a particular savepoint.
Question
Match each transaction-control command to its function.
Options:
COMMIT
ROLLBACK
SAVEPOINT
Targets:
- Divides a transaction into smaller parts
- Makes the changes specified in the current transaction permanent
- Undoes changes you have made
Answer
COMMIT
makes the changes specified in the current transaction permanent. ROLLBACK
undoes changes you have made. SAVEPOINT
divides a transaction into smaller parts.
COMMIT
ends the current transaction. It fixes the state of the database so that the commands in the transaction just ended can't be undone.
ROLLBACK
ends the current transaction. The relevant undo data is retrieved, and the commands in the transaction just ended are undone.
Because you need to refer to a savepoint specifically, you need to assign it a name.
Oracle Database 10g's fine-grained locking mechanism provides data concurrency and data integrity in transactions.
This means that a single user can update a specific data row at a particular time, and no other user can update that row while the first user is working on it.
However, all other rows in the relevant table are available for modification.
Question
Identify the benefits of data locking.
Options:
- It enables multiple users to work on the same row of data at the same time
- It locks all users out of a database while you are working on it
- It makes the remainder of a table available to other users while you work on a particular row
- It prevents multiple users from changing a row at the same time
Answer
Data locking makes the remainder of a table available to other users while you work on a particular row, and it also prevents multiple users from changing a row at the same time.
Option 1 is incorrect. Oracle Database 10g's data-locking mechanism is designed to prevent this from occurring.
Option 2 is incorrect. The data-locking mechanism is designed to make as much of the database as possible available to multiple concurrent users.
Option 3 is correct. Oracle Database 10g's data-locking mechanism is fine-grained.
Option 4 is correct. This ensures data integrity.
2. COMMIT, ROLLBACK, and SAVEPOINT
Suppose that you have created a table that has yet to be populated with data.
When you query its contents, no results are generated.
SELECT * FROM local_temp
You add a row of data to the table.
INSERT INTO local_temp
VALUES (SYSDATE, 76, 58, 67)
Then you query the table again. This time, the command returns the contents of the new row.
SELECT * from local_temp
You decide to roll back the current transaction.
ROLLBACK
To do this, you type ROLLBACK
and then click Execute.
The transaction has been rolled back and the table is empty again.
This means that no results are generated when you query the table contents again.
SELECT * FROM local_temp
You add further data to the table, and then you decide to create a savepoint called before_insert
.
SAVEPOINT before_insert
You type SAVEPOINT before_insert
and click Execute.
You have created a savepoint called before_insert
.
Suppose now that you insert a row into the LOCAL_TEMP table, and then decide that you want to roll the code back to the before_insert
savepoint.
To roll back to the savepoint, you need to add a TO
clause to the rollback statement. This clause specifies the name of the savepoint.
ROLLBACK TO before_insert
You type ROLLBACK TO before_insert
and then you click Execute.
The statement rolls back to the before_insert
savepoint so that the latest insertion is rolled back.
But no commands that were executed before the savepoint in the current transaction are rolled back.
You decide to commit any changes you have made in the current transaction.
COMMIT
You type COMMIT
and then you click Execute.
The changes made in the current transaction are committed.
Question
You have initiated a transaction by adding a row to the locations table.
Type the command that undoes this change to the database.
Answer
You use the ROLLBACK
command.
3. Other statement categories
Besides ROLLBACK
, COMMIT
, and SAVEPOINT
, a number of other SQL*Plus commands can be used to work with transactions. They fall into three categories.
- Session control statements
- System control statement
- Embedded SQL statements
- Session control statements
- Session control statements enable you to implement dynamic management of your session. They are
ALTER SESSION
SET ROLE
TheALTER SESSION
command can
- modify National Language Support (NLS) parameter values
- set the server logging level
- set the schema emulation mode
You can useALTER SESSION
to change the ISO currency symbol to the ISO currency symbol for the territory America, for example.
Session control statements don't implicitly commit the transaction. For this reason, to commit the transaction you can add the committed keyword to the end of the statement.
The code to do this is:
ALTER SESSION SET nls_iso_currency = America committed
You use theSET ROLE
command to enable and disable roles in the current session.
In this example you enable all roles granted to you in the current session.
The code is as follows:
SET ROLE all - System control statement
- In SQL*Plus, there is a single system-control statement:
ALTER SYSTEM
.
You useALTER SYSTEM
to dynamically manage the properties of an instance.
In this example, you dynamically change the limit on sessions for your instance to 64 and the warning threshold for sessions on your instance to 54.
The code to do this is:
ALTER SYSTEM
SET license_max_sessions = 64
license_sessions_warning = 54 - Embedded SQL statements
- Embedded SQL statements are statements supported by Oracle precompilers that can be embedded DL, DML, and transaction-control statements in procedural code, such as a C++ application.
For example, in C++ you use anEXEC SQL
expression to execute a SQL statement.
Question
Match each of these statement categories with the corresponding feature.
Options:
- Embedded SQL statements
- Session control statements
- System control statements
Targets:
- These include
ALTER SESSION
andSET ROLE
- In SQL*Plus, this category includes only
ALTER SYSTEM
- These are statements that can run in certain procedural languages
Answer
Session control statements include ALTER SESSION
and SET ROLE
. The only SQL*Plus system control statement is ALTER SYSTEM
. Embedded SQL statements can run in certain procedural languages.
Embedded SQL statements must be supported by the relevant Oracle precompiler.
Session control statements manage sessions dynamically.
A system control statement manages the properties of an instance dynamically.
Summary
You can undo the SQL commands executed during a transaction before it is committed. When the transaction is committed, it ends and the current state of the database is fixed. From that point, the statements that made up the transaction can no longer be undone. You can divide up a transaction using savepoints. Oracle Database 10g's data-locking mechanism provides data-concurrency and data-integrity functionality.
You undo SQL statements using the ROLLBACK
command. You commit a transaction using the COMMIT
command. You create a savepoint using the SAVEPOINT
command, specifying a name for the savepoint. To roll the database back to a savepoint, you use the ROLLBACK
command, appending a TO
clause specifying the relevant savepoint name.
Besides COMMIT
, ROLLBACK
, and SAVEPOINT
, there are three other categories of transaction-related SQL commands: session control statements, system control statements, and embedded SQL statements.
1 comment:
تنظيف المنازل بالدمام
مكافحة الحشرات بالدمام
تنظيف الفلل بالدمام
Post a Comment