Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Oracle at Work

To truly understand how all the disparate pieces of the Oracle database work together, in this section we’ll walk through an example of the steps taken by the Oracle database to respond to a user request. For this example, we’ll look at a user who is adding new information to the database (in other words, executing a transaction).

Oracle and Transactions

A transaction, in this case, is a work request from a client to insert, update, or delete data. The statements that change data are a subset of the SQL language called Data Manipulation Language (DML). Transactions must be handled in a way that guarantees their integrity. Although Chapter 7 delves into transactions more deeply, we must visit a few basic concepts relating to transactions now in order to understand the example in this section:

Transactions are logical and complete

In database terms, a transaction is a logical unit of work composed of one or more data changes. A transaction may consist of multiple INSERT, UPDATE, and/or DELETE statements affecting data in multiple tables. The entire set of changes must succeed or fail as a complete unit of work. A transaction starts with the first DML statement and ends with either a commit or a rollback.

Commit or rollback

Once a user enters the data for his transaction, he can either commit the transaction to make the changes permanent or roll back the transaction to undo the changes.

System Change Number (SCN)

A key factor in preserving database integrity is an ...