CHAPTER 6

DML and Concurrency

Exam Objectives

In this chapter, you will learn to

• 061.9.1    Describe Each Data Manipulation Language (DML) Statement

• 061.9.2    Insert Rows into a Table

• 061.9.3    Update Rows in a Table

• 061.9.4    Delete Rows from a Table

• 061.9.5    Control Transactions

• 062.9.1    Explain DML and Undo Data Generation

• 062.9.2    Monitor and Administer Undo Data

• 062.9.3    Describe the Difference between Undo Data and Redo Data

• 062.9.4    Configure Undo Retention

• 062.10.1    Describe the Locking Mechanism and Data Concurrency Management

• 062.10.2    Monitor and Resolve Locking Conflicts

Data in a relational database is managed with the Data Manipulation Language (DML) commands of SQL. These are INSERT, UPDATE, DELETE, and, with more recent versions of SQL, MERGE. This chapter discusses what happens in memory and on disk when you execute INSERT, UPDATE, or DELETE statements—the manner in which changed data is written

to blocks of table and index segments and the old version of the data is written out to blocks of an undo segment. The theory behind this, summarized as the ACID test, which every relational database must pass, is explored, and you will see the practicalities of how undo data is managed.

The transaction control statements COMMIT and ROLLBACK, which are closely associated with DML commands, are also explained. The chapter ends with a detailed examination of concurrent data access and table and row locking.

Describe Each Data Manipulation Language Statement

Strictly speaking, there are five DML commands:

•  SELECT

•  INSERT

•  UPDATE

•  DELETE

•  MERGE

In practice, most database professionals never include SELECT as part of DML. It is considered a separate language in its own right, which is not unreasonable when you consider that the next five chapters are dedicated to describing it. The MERGE command is often dropped as well, not because it isn’t clearly a data manipulation command, but because it doesn’t do anything that cannot be done with other commands. You can think of MERGE as a shortcut for executing either an INSERT or an UPDATE or a DELETE, depending on some condition. A command often considered with DML is TRUNCATE. This is actually a Data Definition Language (DDL) command, but because the effect for end users is the same as for a DELETE (though its implementation is totally different), it does fit with DML.

INSERT

Oracle stores data in the form of rows in tables. Tables are populated with rows (just as a country is populated with people) in several ways, but the most common method is with the INSERT statement. SQL is a set-oriented language, so any one command can affect one row or a set of rows. It follows that one INSERT statement can insert an individual row into one table or many rows into many tables. The basic versions of the statement do insert just one row, but more complex variations can, with one command, insert multiple rows into multiple tables.

The simplest form of the INSERT statement inserts one row into one table, using values provided inline as part of the command. The syntax is as follows:

Here’s an example:

The first of the preceding commands provides values for both columns of the REGIONS table. If the table had a third column, the statement would fail because it relies upon positional notation. The statement does not say which value should be inserted into which column; it relies on the position of the values, namely, their ordering in the command. When the database receives a statement using positional notation, it will match the order of the values to the order in which the columns of the table are defined. The statement would also fail if the column order was wrong; the database would attempt the insertion but would fail because of data type mismatches.

The second command nominates the columns to be populated and the values with which to populate them. Note that the order in which columns are mentioned now becomes irrelevant—as long as the order of the columns is the same as the order of the values.

The third example lists one column and therefore only one value. All other columns will be left null. This statement will fail if the REGION_NAME column is not nullable. The fourth example will produce the same result, but because there is no column list, some value (even a NULL) must be provided for each column.

To insert many rows with one INSERT command, the values for the rows must come from a query. The syntax is as follows:

Note that this syntax does not use the VALUES keyword. If the column list is omitted, then the subquery must provide values for every column in the table. To copy every row from one table to another, if the tables have the same column structure, a command such as this is all that is needed:

This presumes that the table REGIONS_COPY does exist. The SELECT subquery reads every row from the source table, which is REGIONS, and the INSERT inserts them into the target table, which is REGIONS_COPY.

To conclude the description of the INSERT command, it should be mentioned that it is possible to insert rows into several tables with one statement. This is not part of the Oracle Certified Professional (OCP) examination, but for completeness, here is an example:

To read this statement, start at the bottom. The subquery retrieves all employees recruited in the last 30 days. Then go to the top. The ALL keyword means that every row selected will be considered for insertion into all the tables following, not just into the first table for which the condition applies. The first condition is 1=1, which is always true, so every source row will create a row in EMP_NO_NAME. This is a copy of the EMPLOYEES table with the personal identifiers removed. The second condition is DEPARTMENT_ID <> 80, which will generate a row in EMP_NON_SALES for every employee who is not in the sales department; there is no need for this table to have the COMMISSION_PCT column. The third condition generates a row in EMP_SALES for all the salespeople; there is no need for the DEPARTMENT_ID column because they will all be in department 80.

This is a simple example of a multitable insert, but it should be apparent that with one statement, and therefore only one pass through the source data, it is possible to populate many target tables. This can take an enormous amount of strain off the database.

Exercise 6-1: Use the INSERT Command    In this exercise, use various techniques to insert rows into a table.

1.  Connect to the HR schema with either SQL Developer or SQL*Plus.

2.  Query the PRODUCTS, ORDERS, and ORDER_ITEMS tables created in Exercise 5-5 to confirm what data is currently stored.

3.  Insert two rows into the PRODUCTS table, providing the values inline.

4.  Insert two rows into the ORDERS table, explicitly providing the column names.

5.  Insert three rows into the ORDER_ITEMS table, using substitution variables.

When prompted, provide these values: {1, 1, 2,5}, {2,1,1,3}, and {1,2,2,4}.

6.  Insert a row into the PRODUCTS table, calculating the PRODUCT_ID to be 100 higher than the current high value. This will need a scalar subquery.

7.  Confirm the insertion of the rows.

8.  Commit the insertions.

The following illustration shows the results of the exercise, using SQL*Plus:

UPDATE

The UPDATE command is used to change rows that already exist—rows that have been created by an INSERT command or possibly by a tool such as Data Pump. As with any other SQL command, an UPDATE can affect one row or a set of rows. The size of the set affected by an UPDATE is determined by a WHERE clause, in exactly the same way that the set of rows retrieved by a SELECT statement is defined by a WHERE clause. The syntax is identical. All the rows updated will be in one table; it is not possible for a single UPDATE command to affect rows in multiple tables.

When updating a row or a set of rows, the UPDATE command specifies which columns of the rows to update. It is not necessary (or indeed common) to update every column of the row. If the column being updated already has a value, then this value is replaced with the new value specified by the UPDATE command. If the column was not previously populated—which is to say, its value was NULL—then it will be populated after the UPDATE with the new value.

A typical use of UPDATE is to retrieve one row and update one or more columns of the row. The retrieval will be done using a WHERE clause that selects a row by its primary key, which is the unique identifier that will ensure that only one row is retrieved. Then the columns that are updated will be any columns other than the primary key column. It is unusual to change the value of the primary key. The lifetime of a row begins when it is inserted and then may continue through several updates, until it is deleted. Throughout this lifetime, it will not usually change its primary key.

To update a set of rows, use a less restrictive WHERE clause than the primary key. To update every row in a table, do not use any WHERE clause at all. This set behavior can be disconcerting when it happens by accident. If you select the rows to be updated with any column other than the primary key, you may update several rows, not just one. If you omit the WHERE clause completely, you will update the whole table—perhaps millions of rows updated with just one statement—when you meant to change just one.

An UPDATE command must honor any constraints defined for the table, just as the original INSERT would have. For example, it will not be possible to update a column that has been marked as mandatory to a NULL value or to update a primary key column so that it will no longer be unique. The basic syntax is the following:

The more complex form of the command uses subqueries for one or more of the column values and for the WHERE condition. Figure 6-1 shows updates of varying complexity, executed from SQL*Plus.

Figure 6-1    Examples of using the UPDATE statement

The first example is the simplest. One column of one row is set to a literal value. Because the row is chosen with a WHERE clause that uses the equality predicate on the table’s primary key, there is an absolute guarantee that, at most, only one row will be affected. No row will be changed if the WHERE clause fails to find any rows at all.

The second example shows use of arithmetic and an existing column to set the new value, and the row selection is not done on the primary key column. If the selection is not done on the primary key or if a nonequality predicate (such as BETWEEN) is used, then the number of rows updated may be more than one. If the WHERE clause is omitted entirely, the update will be applied to every row in the table.

The third example in Figure 6-1 introduces the use of a subquery to define the set of rows to be updated. A minor additional complication is the use of a replacement variable to prompt the user for a value to use in the WHERE clause of the subquery. In this example, the subquery (lines 3 and 4) will select every employee who is in a department whose name includes the string ‘IT’ and will increment their current salary by 10 percent (unlikely to happen in practice).

It is also possible to use subqueries to determine the value to which a column will be set, as in the fourth example. In this case, one employee (identified by primary key in line 5) is transferred to department 80 (the sales department), and then the subquery in lines 3 and 4 sets his commission rate to whatever the lowest commission rate in the department happens to be.

The syntax of an update that uses subqueries is as follows:

There is a rigid restriction on the subqueries using update columns in the SET clause. The subquery must return a scalar value. A scalar value is a single value of whatever data type is needed; the query must return one row, with one column. If the query returns several values, the UPDATE will fail. Consider these two examples:

The first example, using an equality predicate on the primary key, will always succeed. Even if the subquery does not retrieve a row (as would be the case if there were no employee with EMPLOYEE_ID equal to 206), the query will still return a scalar value: a null. In that case, all the rows in EMPLOYEES would have their SALARY set to NULL, which might not be desired but is not an error as far as SQL is concerned. The second example uses an equality predicate on LAST_NAME, which is not guaranteed to be unique. The statement will succeed if there is only one employee with that name, but if there were more than one, it would fail with the error “ORA-01427: single-row subquery returns more than one row.” For code that will work reliably, no matter what the state of the data, it is vital to ensure that the subqueries used for setting column values are scalar.

The subqueries in the WHERE clause must also be scalar if it is using the equality predicate (as in the preceding examples) or the greater/less than predicates. If it is using the IN predicate, then the query can return multiple rows, as in this example that uses IN:

This will apply the update to all employees in a department whose name includes the string ‘IT’. There are several of these. But even though the query can return several rows, it must still return only one column.

Exercise 6-2: Use the UPDATE Command    In this exercise, use various techniques to update rows in a table. It is assumed that the HR.PRODUCTS table is as shown in the illustration at the end of Exercise 6-1. If not, adjust the values as necessary.

1.  Connect to the HR schema using SQL Developer or SQL*Plus.

2.  Update a single row, identified by primary key.

This statement should return the message “1 row updated.”

3.  Update a set of rows, using a subquery to select the rows and to provide values.

This statement should return the message “1 row updated.”

4.  Confirm the state of the rows.

5.  Commit the changes made.

DELETE

Previously inserted rows can be removed from a table with the DELETE command. The command will remove one row or a set of rows from the table, depending on a WHERE clause. If there is no WHERE clause, every row in the table will be removed (which can be a little disconcerting if you left out the WHERE clause by mistake).

A deletion is all or nothing. It is not possible to nominate columns. When rows are inserted, you can choose which columns to populate. When rows are updated, you can choose which columns to update. But a deletion applies to the whole row—the only choice is which rows in which table. This makes the DELETE command syntactically simpler than the other DML commands. The syntax is as follows:

This is the simplest of the DML commands, particularly if the condition is omitted. In that case, every row in the table will be removed with no prompt. The only complication is in the condition. This can be a simple match of a column to a literal.

The first statement identifies a row by primary key. Only one row will be removed—or no row at all, if the value given does not find a match. The second statement uses a nonequality predicate that could result in the deletion of many rows, namely, every employee whose surname begins with an uppercase S. The third statement uses an equality predicate but not on the primary key. It prompts for a department number with a substitution variable, and all employees in that department will go. The final statement removes all employees who are not currently assigned to a department.

The condition can also be a subquery.

This example uses a subquery for row selection that navigates the HR geographical tree (with more subqueries) to delete every employee who works for any department that is based in Europe. The same rule for the number of values returned by the subquery applies as for an UPDATE command: If the row selection is based on an equality predicate (as in the preceding example), the subquery must be scalar, but if it uses IN, the subquery can return several rows.

If the DELETE command finds no rows to delete, this is not an error. The command will return the message “0 rows deleted” rather than an error message because the statement did complete successfully—it just didn’t find anything to do.

Exercise 6-3: Use the DELETE Command    In this exercise, use various techniques to delete rows in a table. It is assumed that the HR.PRODUCTS table has been modified during the previous two exercises. If not, adjust the values as necessary.

1.  Connect to the HR schema using SQL Developer or SQL*Plus.

2.  Remove one row, using the equality predicate on the primary key.

This should return the message “1 row deleted.”

3.  Attempt to remove every row in the table by omitting a WHERE clause.

This will fail due to a constraint violation because there are child records in the ORDER_ITEMS table that reference PRODUCT_ID values in the PRODUCTS table via the foreign key constraint FK_PRODUCT_ID.

4.  Commit the deletion.

To remove rows from a table, there are two options: the DELETE command and the TRUNCATE command. DELETE is less drastic, in that a deletion can be rolled back, whereas a truncation cannot. DELETE is also more controllable in that it is possible to choose which rows to delete, whereas a truncation always affects the whole table. DELETE is, however, a lot slower and can place a lot of strain on the database. TRUNCATE is virtually instantaneous and effortless.

TRUNCATE

The TRUNCATE command is not a DML command; it is a DDL command. The difference is enormous. When DML commands affect data, they insert, update, and delete rows as part of transactions. Transactions are defined later in this chapter, in the section “Control Transactions.” For now, let it be said that a transaction can be controlled, in the sense that the user has the choice of whether to make the work done in a transaction permanent or whether to reverse it. This is useful but forces the database to do additional work behind the scenes that the user is not aware of. DDL commands are not user transactions (though within the database, they are, in fact, implemented as transactions—but developers cannot control them), and there is no choice about whether to make them permanent or to reverse them. Once executed, they are done. However, in comparison to DML, they are very fast.

From the user’s point of view, a truncation of a table is equivalent to executing a DELETE of every row; it’s a DELETE command without a WHERE clause. But whereas a deletion may take some time (possibly hours, if there are many rows in the table), a truncation will go through instantly. It makes no difference whether the table contains one row or billions; a TRUNCATE will be virtually instantaneous. The table will still exist, but it will be empty.

One part of the definition of a table as stored in the data dictionary is the table’s physical location. When first created, a table is allocated a single area of space of a fixed size in the database’s datafiles. This is known as an extent and will be empty. Then, as rows are inserted, the extent fills up. Once it is full, more extents will be allocated to the table automatically. A table therefore consists of one or more extents, which hold the rows. As well as tracking the extent allocation, the data dictionary tracks how much of the space allocated to the table has been used. This is done with the high water mark. The high water mark is the last position in the last extent that has been used; all space below the high water mark has been used for rows at one time or another, and none of the space above the high water mark has been used yet.

Note that it is possible for there to be plenty of space below the high water mark that is not being used at the moment; this is because of rows having been removed with a DELETE command. Inserting rows into a table pushes up the high water mark. Deleting them leaves the high water mark where it is; the space they occupied remains assigned to the table but is freed up for inserting more rows.

Truncating a table resets the high water mark. Within the data dictionary, the recorded position of the high water mark is moved to the beginning of the table’s first extent. Because Oracle assumes that there can be no rows above the high water mark, this has the effect of removing every row from the table. The table is emptied and remains empty until subsequent insertions begin to push the high water mark back up again. In this manner, one DDL command, which does little more than make an update in the data dictionary, can annihilate billions of rows in a table.

The syntax to truncate a table couldn’t be simpler.

MERGE

There are many occasions where you want to take a set of data (the source) and integrate it into an existing table (the target). If a row in the source data already exists in the target table, you may want to update the target row, you may want to replace it completely, or you may want to leave the target row unchanged. If a row in the source does not exist in the target, you will want to insert it. The MERGE command lets you do this. A MERGE passes through the source data for each row attempting to locate a matching row in the target. If no match is found, a row can be inserted; if a match is found, the matching row can be updated. The release 10g enhancement means that the target row can even be deleted after being matched and updated. The end result is a target table into which the data in the source has been merged.

A MERGE operation does nothing that could not be done with INSERT, UPDATE, and DELETE statements—but with one pass through the source data, it can do all three. Alternative code without a MERGE would require three passes through the data, one for each command.

The source data for a MERGE statement can be a table or any subquery. The condition used for finding matching rows in the target is similar to a WHERE clause. The clauses that update or insert rows are as complex as an UPDATE or an INSERT command. It follows that MERGE is the most complicated of the DML commands, which is not unreasonable because it is (arguably) the most powerful. Using MERGE is not on the OCP syllabus, but for completeness, here is a simple example:

The preceding statement uses the contents of a table NEW_EMPLOYEES to update or insert rows in EMPLOYEES. The situation could be that EMPLOYEES is a table of all staff, and NEW_EMPLOYEES is a table with rows for new staff and for salary changes for existing staff. The command will pass through NEW_EMPLOYEES and, for each row, attempt to find a row in EMPLOYEES with the same EMPLOYEE_ID. If there is a row found, its SALARY column will be updated with the value of the row in NEW_EMPLOYEES. If there is not such a row, one will be inserted. Variations on the syntax allow the use of a subquery to select the source rows, and it is even possible to delete matching rows.

DML Statement Failures

Commands can fail for many reasons, including the following:

•  Syntax errors

•  References to nonexistent objects or columns

•  Access permissions

•  Constraint violations

•  Space issues

Figure 6-2 shows several attempted executions of a statement with SQL*Plus.

Figure 6-2    Some examples of statement failure

In Figure 6-2, a user connects as SUE (password, SUE—not an example of good security) and queries the EMPLOYEES table. The statement fails because of a simple syntax error, correctly identified by SQL*Plus. Note that SQL*Plus never attempts to correct such mistakes, even when it knows exactly what you meant to type. Some third-party tools may be more helpful, offering automatic error correction.

The second attempt to run the statement fails with an error stating that the object does not exist. This is because it does not exist in the current user’s schema; it exists in the HR schema. Having corrected that, the third run of the statement succeeds—but only just. The value passed in the WHERE clause is a string, “07-JUN-2002,” but the column HIRE_DATE is not defined in the table as a string; it is defined as a date. To execute the statement, the database had to work out what the user really meant and cast the string as a date. In the last example, the type casting fails.

If a statement is syntactically correct and has no errors with the objects to which it refers, it can still fail because of access permissions. If the user attempting to execute the statement does not have the relevant permissions on the tables to which it refers, the database will return an error identical to that which would be returned if the object did not exist. As far as the user is concerned, it does not exist.

Errors caused by access permissions are a case where SELECT and DML statements may return different results. It is possible for a user to have permission to see the rows in a table but not to insert, update, or delete them. Such an arrangement is not uncommon; it often makes business sense. Perhaps more confusingly, permissions can be set up in such a manner that it is possible to insert rows that you are not allowed to see. And, perhaps worst of all, it is possible to delete rows that you can neither see nor update. However, such arrangements are not common.

A constraint violation can cause a DML statement to fail. For example, an INSERT command can insert several rows into a table, and for every row the database will check whether a row already exists with the same primary key. This occurs as each row is inserted. It could be that the first few rows (or the first few million rows) go in without a problem, and then the statement hits a row with a duplicate value. At this point it will return an error, and the statement will fail. This failure will trigger a reversal of all the insertions that had already succeeded. This is part of the SQL standard; a statement must succeed in total or not at all. The reversal of the work is a rollback. The mechanisms of a rollback are described in the next section of this chapter, “Control Transactions.”

If a statement fails because of space problems, the effect is similar. A part of the statement may have succeeded before the database ran out of space. The part that did succeed will be automatically rolled back. Rollback of a statement is a serious matter. It forces the database to do a lot of extra work and will usually take at least as long as the statement has taken already (sometimes much longer).

Control Transactions

The concepts behind a transaction are part of the relational database paradigm. A transaction consists of one or more DML statements, followed by either a ROLLBACK or a COMMIT command. It is possible to use the SAVEPOINT command to give a degree of control within the transaction. Before going into the syntax, it is necessary to review the concept of a transaction. A related topic is read consistency; this is automatically implemented by the Oracle server, but to a certain extent, programmers can manage it by the way they use the SELECT statement.

Database Transactions

Oracle’s mechanism for assuring transactional integrity is the combination of undo segments and redo log files. This mechanism is undoubtedly the best of any database yet developed and conforms perfectly with the international standards for data processing. Other database vendors comply with the same standards with their own mechanisms but with varying levels of effectiveness. In brief, any relational database must be able to pass the ACID test; it must guarantee atomicity, consistency, isolation, and durability.

A is for Atomicity

The principle of atomicity states that either all parts of a transaction must complete or none of them complete. For example, if your business analysts have said that every time you change an employee’s salary, you must also change his grade, then the atomic transaction will consist of two updates. The database must guarantee that both go through or neither do. If only one of the updates were to succeed, you would have an employee on a salary that was incompatible with his grade, which is a data corruption in business terms. If anything (anything at all!) goes wrong before the transaction is complete, the database itself must guarantee that any parts that did go through are reversed; this must happen automatically. But although an atomic transaction sounds small (like an atom), it could be enormous. To take another example, it is logically impossible in accountancy terms for the nominal ledger of an accounting suite to be half in August and half in September. The end-of-month rollover is therefore (in business terms) one atomic transaction, which may affect millions of rows in thousands of tables, as well as take hours to complete (or to roll back, if anything goes wrong). The rollback of an incomplete transaction is the reversal process and may be manual (as when you issue the ROLLBACK command), but it must be automatic and unstoppable in the case of an error.

C is for Consistency

The principle of consistency states that the results of a query must be consistent with the state of the database at the time the query started. Imagine a simple query that averages the value of a column of a table. If the table is large, it will take many minutes to pass through the table. If other users are updating the column while the query is in progress, should the query include the new or old values? Should it include rows that were inserted or deleted after the query started? The principle of consistency requires that the database ensure that changed values are not seen by the query; it will give you an average of the column as it was when the query started, no matter how long the query takes or what other activity is occurring on the tables concerned.

Through the use of undo segments, Oracle guarantees that if a query succeeds, the result will be consistent. However, if your undo segments are incorrectly configured, the query may not succeed. The famous Oracle error “ORA-1555: snapshot too old” is raised. This used to be a difficult problem to fix with earlier releases of the database, but from release 9i onward, you should always be able to avoid it.

I is for Isolation

The principle of isolation states that an incomplete (that is, uncommitted) transaction must be invisible to the rest of the world. While the transaction is in progress, only the one session that is executing the transaction is allowed to see the changes. All other sessions must see the unchanged data, not the new values. The logic behind this is, first, that the full transaction might not go through (remember the principle of atomicity?), and therefore no other users should be allowed to see changes that might be reversed. And, second, during the progress of a transaction, the data is (in business terms) inconsistent; there is a short time when the employee has had their salary changed, but not their grade. Transaction isolation requires that the database must conceal transactions in progress from other users. They will see the pre-update version of the data until the transaction completes, when they will see all the changes as a consistent set.

Oracle guarantees transaction isolation, consistency, and atomicity through the use of undo segments.

D is for Durability

The principle of durability states that once a transaction completes, it must be impossible for the database to lose it. During the time that the transaction is in progress, the principle of isolation requires that no one (other than the session concerned) can see the changes it has made so far. But the instant the transaction completes, it must be broadcast to the world, and the database must guarantee that the change is never lost because a relational database is not allowed to lose data. Oracle fulfills this requirement through the use of log files. Log files come in two forms—online redo log files and archive redo log files—that store a record of every change applied to the database. Of course, data can be lost through user error, such as using inappropriate DML and dropping objects. But as far as Oracle and the database administrator (DBA) are concerned, such events are transactions like any other. According to the principle of durability, they are absolutely nonreversible.

Executing SQL Statements

The entire SQL language consists of only a dozen or so commands. The ones you are concerned with here are SELECT, INSERT, UPDATE, and DELETE.

Executing a SELECT Statement

The SELECT command retrieves data. The execution of a select statement is a staged process. The server process executing the statement will first check whether the blocks containing the data required are already in memory in the database buffer cache. If they are, then execution can proceed immediately. If they are not, the server process must locate them on disk and copy them into the database buffer cache.

Once the data blocks required for the query are in the database buffer cache, any further processing (such as sorting or aggregation) is carried out in the Program Global Area (PGA) of the session. When the execution is complete, the result set is returned to the user process.

How does this relate to the ACID test just described? For consistency, if the query encounters a block that has been changed since the time the query started, the server process will go to the undo segment that protected the change, locate the old version of the data, and, for the purposes of the current query only, roll back the change. Thus, any changes initiated after the query commenced will not be seen. A similar mechanism guarantees transaction isolation, though this is based also on whether the change has been committed, not only on whether the data has been changed. Clearly, if the data needed to do this rollback is no longer in the undo segments, this mechanism will not work. That is when you get the “snapshot too old” error.

Figure 6-3 shows a representation of the way a SELECT statement is processed.

Figure 6-3    The stages of execution of a SELECT

In the figure, step 1 is the transmission of the SELECT statement from the user process to the server process. The server will search the database buffer cache to determine whether the necessary blocks are already in memory and, if they are, proceed to step 4. If they are not, step 2 is to locate the blocks in the datafiles, and step 3 is to copy them into the database buffer cache. Step 4 transfers the data to the server process, where there may be some further processing before step 5 returns the result of the query to the user process.

Executing an UPDATE Statement

For any DML operation, it is necessary to work on both data blocks and undo blocks and also to generate redo: the A, C, and I of the ACID test require generation of undo; the D requires generation of redo.

The first step in executing DML is the same as executing SELECT; the required blocks must be found in the database buffer cache or copied into the database buffer cache from the datafiles. The only change is that an empty (or expired) block of an undo segment is needed too. From then on, things are a bit more complicated.

First, locks must be placed on any rows and associated index keys that are going to be affected by the operation. This is covered later in this chapter.

Then the redo is generated; the server process writes to the log buffer the change vectors that are going to be applied to the data blocks. This generation of redo is applied both to table block changes and to undo block changes. If a column of a row is to be updated, then the rowid and the new value of the column are written to the log buffer (which is the change that will be applied to the table block) and also the old value (which is the change that will be applied to the undo block). If the column is part of an index key, then the changes to be applied to the index are also written to the log buffer, together with a change to be applied to an undo block to protect the index change.

Having generated the redo, the update is carried out in the database buffer cache; the block of table data is updated with the new version of the changed column, and the old version of the changed column is written to the block of the undo segment. From this point until the update is committed, all queries from other sessions addressing the changed row will be redirected to the undo data. Only the session that is doing the update will see the actual current version of the row in the table block. The same principle applies to any associated index changes.

Executing INSERT and DELETE Statements

Conceptually, INSERT and DELETE are managed in the same fashion as an UPDATE. The first step is to locate the relevant blocks in the database buffer cache or to copy them into it if they are not there.

Redo generation is the same. All change vectors to be applied to data and undo blocks are first written out to the log buffer. For an INSERT, the change vector to be applied to the table block (and possibly index blocks) consists of the bytes that make up the new row (and possibly the new index keys). The vector to be applied to the undo block is the rowid of the new row. For a DELETE, the change vector to be written to the undo block is the entire row.

A crucial difference between INSERT and DELETE is in the amount of undo generated. When a row is inserted, the only undo generated is writing out the new rowid to the undo block. This is because to roll back an INSERT, the only information Oracle requires is the rowid so that this statement can be constructed:

Executing this statement will reverse the original change.

For a DELETE, the whole row (which might be several kilobytes) must be written to the undo block so that the deletion can be rolled back if need be by constructing a statement that will insert the complete row back into the table.

The Start and End of a Transaction

A session begins a transaction the moment it issues any DML. The transaction continues through any number of further DML commands until the session issues either a COMMIT or a ROLLBACK statement. Only committed changes will be made permanent and become visible to other sessions. It is impossible to nest transactions. The SQL standard does not allow a user to start one transaction and then start another before terminating the first. This can be done with PL/SQL (Oracle’s proprietary third-generation language) but not with industry-standard SQL.

The explicit transaction control statements are COMMIT, ROLLBACK, and SAVEPOINT. There are also circumstances other than a user-issued COMMIT or ROLLBACK that will implicitly terminate a transaction.

•  Issuing a DDL or Data Control Language (DCL) statement

•  Exiting from the user tool (SQL*Plus or SQL Developer or anything else)

•  If the client session dies

•  If the system crashes

If a user issues a DDL (CREATE, ALTER, or DROP) or DCL (GRANT or REVOKE) command, the transaction in progress (if any) will be committed; it will be made permanent and become visible to all other users. This is because the DDL and DCL commands are themselves transactions. As it is not possible to nest transactions in SQL, if the user already has a transaction running, the statements the user has run will be committed implicitly before the statement that makes up the DDL or DCL command executes.

If you start a transaction by issuing a DML command and then exit from the tool you are using without explicitly issuing either a COMMIT or a ROLLBACK, the transaction will terminate—but whether it terminates with a COMMIT or a ROLLBACK is entirely dependent on how the tool is written. Many tools will have different behavior, depending on how the tool is exited. (For instance, in the Microsoft Windows environment, it is common to be able to terminate a program either by selecting File | Exit from a menu on the top left of the window or by clicking an X in the top-right corner. The programmers who wrote the tool may well have coded different logic into these functions.) In either case, it will be a controlled exit, so the programmers should issue either a COMMIT or a ROLLBACK, but the choice is up to them.

If a client’s session fails for some reason, the database will always roll back the transaction. Such failure could be for a number of reasons. The user process can die or be killed at the operating system level, the network connection to the database server may go down, or the machine where the client tool is running can crash. In any of these cases, there is no orderly issue of a COMMIT or ROLLBACK statement, and it is up to the database to detect what has happened. The behavior is that the session is killed and an active transaction is rolled back. The behavior is the same if the failure is on the server side. If the database server crashes for any reason, when it next starts up, all transactions from any sessions that were in progress will be rolled back.

Transaction Control: COMMIT, ROLLBACK, SAVEPOINT, SELECT FOR UPDATE

Oracle’s implementation of the relational database paradigm begins a transaction implicitly with the first DML statement. The transaction continues until a COMMIT or ROLLBACK statement. The SAVEPOINT command is not part of the SQL standard and is really just an easy way for programmers to back out some statements in reverse order. It need not be considered separately because it does not terminate a transaction.

COMMIT

Commit processing is where many people (and even some experienced DBAs) show an incomplete, or indeed completely inaccurate, understanding of the Oracle architecture. When you say COMMIT, all that happens physically is that LGWR flushes the log buffer to disk. DBWn does absolutely nothing. This is one of the most important performance features of an Oracle database.

To make a transaction durable, all that is necessary is that the changes that make up the transaction are on disk; there is no need whatsoever for the changed table data to be on disk in the datafiles. If the changes are on disk in the form of multiplexed redo log files, then in the event of damage to the database, the transaction can be reinstantiated by restoring the datafiles from a backup taken before the damage occurred and applying the changes from the logs. This process is covered in detail in later chapters—for now, just hang on to the fact that a COMMIT involves nothing more than flushing the log buffer to disk and flagging the transaction as complete. This is why a transaction involving millions of updates in thousands of tables over many minutes or hours can be committed in a fraction of a second. Because LGWR writes in nearly real time, virtually all the transaction’s changes are on disk already. When you say COMMIT, LGWR actually does write in real time; your session will hang until the write is complete. This delay will be the length of time it takes to flush the last bit of redo from the log buffer to disk, which will take milliseconds. Your session is then free to continue, and from then on all other sessions will no longer be redirected to the undo blocks when they address the changed table, unless the principle of consistency requires it.

The change vectors written to the redo log are all the change vectors: those applied to data blocks (tables and indexes) and those applied to undo segments.

Where there is often confusion is that the stream of redo written out to the log files by LGWR will contain changes for both committed and uncommitted transactions. Furthermore, at any given moment, DBWn may or may not have written out changed blocks of data segments or undo segments to the datafiles for both committed and uncommitted transactions. So in principle, your database on disk is corrupted. The datafiles may well be storing uncommitted work and be missing committed changes. But in the event of a crash, the stream of redo on disk always has enough information to reinstantiate any committed transactions that are not in the datafiles (by use of the changes applied to data blocks) and to reinstantiate the undo segments (by use of the changes applied to undo blocks) needed to roll back any uncommitted transactions that are in the datafiles.

ROLLBACK

While a transaction is in progress, Oracle keeps an image of the data as it was before the transaction. This image is presented to other sessions that query the data while the transaction is in progress. It is also used to roll back the transaction automatically if anything goes wrong or deliberately if the session requests it. The syntax to request a rollback is as follows:

The optional use of savepoints is detailed in the section following.

The state of the data before the rollback is that the data has been changed, but the information needed to reverse the changes is available. This information is presented to all other sessions in order to implement the principle of isolation. The rollback will discard all the changes by restoring the prechange image of the data; any rows the transaction inserted will be deleted, any rows the transaction deleted will be inserted back into the table, and any rows that were updated will be returned to their original state. Other sessions will not be aware that anything has happened at all; they never saw the changes. The session that did the transaction will now see the data as it was before the transaction started.

SAVEPOINT

Savepoints allow a marker to be set in a transaction that can be used to control the effect of the ROLLBACK command. Rather than rolling back the whole transaction and terminating it, it becomes possible to reverse all changes made after a particular point but leave changes made before that point intact. The transaction itself remains in progress: still uncommitted, still able to be rolled back, and still invisible to other sessions.

The syntax is as follows:

This creates a named point in the transaction that can be used in a subsequent ROLLBACK command. Table 6-1 illustrates the number of rows in a table at various stages in a transaction. The table is a simple table called TAB, with one column.

Table 6-1    Read Consistency and Savepoints

The example in the table shows two transactions: the first terminated with a COMMIT, the second with a ROLLBACK. It can be seen that the use of savepoints is visible only within the transaction; other sessions see nothing that is not committed.

SELECT FOR UPDATE

One last transaction control statement is SELECT FOR UPDATE. Oracle, by default, provides the highest possible level of concurrency. Readers do not block writers, and writers do not block readers. Or, in plain language, there is no problem with one session querying data that another session is updating or one session updating data that another session is querying. However, there are times when you may want to change this behavior and prevent changes to data that is being queried.

It is not unusual for an application to retrieve a set of rows with a SELECT command, present them to a user for perusal, and prompt them for any changes. Because Oracle is a multiuser database, it is not impossible that another session has also retrieved the same rows. If both sessions attempt to make changes, there can be some rather odd effects. The following table depicts such a situation.

This is what the first user will see from a SQL*Plus prompt:

This is a bit disconcerting. One way around this problem is to lock the rows in which one is interested.

The FOR UPDATE clause will place a lock on all the rows retrieved. No changes can be made to them by any session other than that which issued the command, and therefore the subsequent updates will succeed; it is not possible for the rows to have been changed. This means that one session will have a consistent view of the data (it won’t change), but the price to be paid is that other sessions will hang if they try to update any of the locked rows (they can, of course, query them).

The locks placed by a FOR UPDATE clause will be held until the session issuing the command issues a COMMIT or ROLLBACK. This must be done to release the locks, even if no DML commands have been executed.

The So-Called Autocommit

To conclude this discussion of commit processing, it is necessary to remove any confusion about what is often called autocommit or sometimes implicit commit. You will often hear it said that in some situations Oracle will autocommit. One of these situations is when doing DDL, which is described in the preceding section; another is when you exit from a user process such as SQL*Plus.

Quite simply, there is no such thing as an automatic commit. When you execute a DDL statement, there is a perfectly normal COMMIT included in the source code that implements the DDL command. But what about when you exit from your user process? If you are using SQL*Plus on a Windows terminal and you issue a DML statement followed by an EXIT, your transaction will be committed. This is because built into the SQL*Plus EXIT command there is a COMMIT statement. But what if you click in the top-right corner of the SQL*Plus window? The window will close, and if you log in again, you will see that the transaction has been rolled back. This is because the programmers who wrote SQL*Plus for Microsoft Windows included a ROLLBACK statement in the code that is executed when you close the window. The behavior of SQL*Plus on other platforms may well be different; the only way to be sure is to test it. So, whether you get an “autocommit” when you exit from a program in various ways is entirely dependent on how your programmers wrote your user process. The Oracle server will simply do what it is told to do.

The SQL*Plus command SET AUTOCOMMIT ON will cause SQL*Plus to modify its behavior; it will append a COMMIT to every DML statement issued. So, all statements are committed immediately as soon as they are executed and cannot be rolled back. But this is happening purely on the user process side; there is still no autocommit in the database, and the changes made by a long-running statement will be isolated from other sessions until the statement completes. Of course, a disorderly exit from SQL*Plus in these circumstances, such as killing it with an operating system utility while the statement is running, will be detected by PMON, and the active transaction will always be rolled back.

Exercise 6-4: Explain DML and Undo Data Generation    In this exercise, you will demonstrate transaction isolation and control. Use two SQL*Plus sessions (or SQL Developer if you prefer), each connected as user SYSTEM. Run the commands in the steps that follow in the two sessions in the correct order.

The results are the same in both sessions.

The results differ because transaction isolation conceals the changes.

The results are the same in both sessions.

Oh dear! The DDL statement committed the DELETE, so it can’t be rolled back.

Explain DML and Undo Data Generation

Undo data is the information needed to reverse the effects of DML statements. It is often referred to as rollback data, but try to avoid that term. In earlier releases of Oracle, the terms rollback data and undo data were used interchangeably, but from 9i onward they are different. Their function is the same, but their management is not. The old mechanism of rollback segments is long outdated, and all databases should use automatic undo management, which uses undo segments to store undo data.

Rolling back a transaction means to use data from the undo segments to construct an image of the data as it was before the transaction occurred. This is usually done automatically to satisfy the requirements of the ACID test, but the Flashback Query capability (detailed in the section “Flashback Query and Undo Retention”) leverages the power of the undo mechanism by giving you the option of querying the database as it was at some time in the past. And, of course, any user can use the ROLLBACK command interactively to back out any DML statements that were issued and not committed.

The ACID test requires, first, that the database should keep preupdate versions of data in order that incomplete transactions can be reversed—either automatically in the case of an error or on demand through the use of the ROLLBACK command. This type of rollback is permanent and published to all users. Second, for consistency, the database must be able to present a query with a version of the database as it was at the time the query started. The server process running the query will go to the undo segments and construct what is called a read-consistent image of the blocks being queried, if they were changed after the query started. This type of rollback is temporary and visible only to the session running the query. Third, undo segments are also used for transaction isolation. This is perhaps the most complex use of undo data. The principle of isolation requires that no transaction can be in any way dependent upon another incomplete transaction. In effect, even though a multiuser database will have many transactions in progress at once, the end result must be as though the transactions were executing one after another. The use of undo data combined with row and table locks guarantees transaction isolation, which means the impossibility of incompatible transactions. Even though several transactions may be running concurrently, isolation requires that the end result must be as if the transactions were serialized.

Exercise 6-5: Monitor and Administer Undo Data    In this exercise, you will investigate the undo configuration and usage in your database. Use either SQL*Plus or SQL Developer.

1.  Connect to the database as user SYSTEM.

2.  Determine whether the database is using undo segments or rollback segments with this query:

This should return the value AUTO. If it does not, issue this command and then restart the instance:

3.  Determine what undo tablespaces have been created and which one is being used with these two queries:

4.  Determine what undo segments are in use in the database and how big they are.

Note that the identifying number for a segment has a different column name in the two views.

5.  Find out how much undo data was being generated in your database in the recent past.

Monitor and Administer Undo Data

A major feature of undo segments is that they are managed automatically, but you must set the limits within which Oracle will do its management. After considering the nature and volume of activity in your database, you set certain instance parameters and adjust the size of your undo tablespace in order to achieve your objectives.

Error Conditions Related to Undo

The principles are simple. First, there should always be sufficient undo space available to allow all transactions to continue. Second, there should always be sufficient undo data available for all queries to succeed. The first principle requires that your undo tablespace must be large enough to accommodate the worst case for undo demand. It should have enough space allocated for the peak usage of active undo data generated by your transaction workload. Note that this might not be during the highest number of concurrent transactions; it could be that during normal running you have many small transactions, but the total undo they generate might be less than that generated by a single end-of-month batch job. The second principle requires that there be additional space in the undo tablespace to store unexpired undo data that might be needed for read consistency.

If a transaction runs out of undo space, it will fail with the error “ORA-30036: unable to extend segment in undo tablespace.” The statement that hit the problem is rolled back, but the rest of the transaction remains intact and uncommitted. The algorithm that assigns space within the undo tablespace to undo segments means that this error condition will arise only if the undo tablespace is absolutely full of active undo data.

If a query encounters a block that has been changed since the query started, it will go to the undo segment to find the pre-update version of the data. If, when it goes to the undo segment, that bit of undo data has been overwritten, the query will fail with the famous Oracle error “ORA-1555: snapshot too old.”

If the undo tablespace is undersized for the transaction volume and the length of queries, Oracle has a choice: Either let transactions succeed and risk queries failing with ORA-1555 or let queries succeed and risk transactions failing with ORA-30036. The default behavior is to let the transactions succeed to allow them to overwrite unexpired undo.

Parameters for Undo Management

The following four parameters control undo:

•  UNDO_MANAGEMENT

•  UNDO_TABLESPACE

•  UNDO_RETENTION (discussed in the section “Configure Undo Retention”)

•  TEMP_UNDO_ENABLED (discussed in the section “Temporary Undo”)

UNDO_MANAGEMENT defaults to AUTO. It is possible to set this to MANUAL, meaning that Oracle will not use undo segments at all. This is for backward compatibility, and if you use this, you will have to do a vast amount of work creating and tuning rollback segments. Don’t do it. Oracle Corporation strongly advises setting this parameter to AUTO to enable use of undo segments. This parameter is static, meaning that if it is changed, the change will not come into effect until the instance is restarted. The other parameters are dynamic—they can be changed while the instance is running.

If you are using UNDO_MANAGEMENT=AUTO (as you should), you should also specify UNDO_TABLESPACE. This parameter nominates a tablespace, which must have been created as an undo tablespace, as the active undo tablespace. All the undo segments within it will be brought online (that is, made available for use) automatically.

Sizing and Monitoring the Undo Tablespace

The undo tablespace should be large enough to store the worst case of all the undo generated by concurrent transactions, which will be active undo plus enough unexpired undo to satisfy the longest-running query. In an advanced environment, you may also have to add space to allow for flashback queries. The algorithm is simple: Calculate the rate at which undo is being generated at your peak workload and multiply by the length of your longest query. There is a view, V$UNDOSTAT, that will tell you all you need to know.

Making your undo datafiles autoextensible will ensure that transactions will never run out of space, but Oracle will not extend them merely to meet the UNDO_RETENTION target; it is therefore still possible for a query to fail with “snapshot too old.” However, you should not rely on the autoextend capability; your tablespace should be the correct size to begin with.

Figure 6-4 shows the undo configuration and usage using SQL*Plus. The same information is available in graphical format in Database Express. On the database home page, select the Undo Management link on the Storage drop-down menu.

Figure 6-4    Undo configuration and activity

The first statement in the figure sets the session’s date display format to show hours, minutes, and seconds. Then the SQL*Plus SHOW command shows the settings for the four instance parameters that include the string “undo” in their name. All four are set to their default: Temporary undo is disabled, undo management is automatic (using undo segments, not antiquated rollback segments), the retention target is 900 seconds, and the undo tablespace is UNDOTBS1, which is the tablespace created when using DBCA to create a database. A query against V$UNDOSTAT shows undo activity captured in 10-minute intervals. During the half-hour shown, undo generation peaked at 281,117 blocks in one 10-minute interval and generated 908 transactions. The longest query was 1,393 seconds. Finally, querying DBA_DATA_FILES shows that the undo tablespace datafile is 540,800 blocks, which is about 4GB, assuming that the database was created using the default block size of 8KB.

Doing some simple arithmetic shows that the undo generation rate peaks at about 500 blocks a second, so if the longest query is about 1,000 seconds (which is pretty close to the undo_retention setting), you would need an undo tablespace of about half a million blocks to ensure that undo can always be kept for as long as the longest query. This database would appear to have undo configuration that is well matched to the workload.

The V$UNDOSTAT view is often referred to as the undo advisor because it lets you predict how long undo data can be kept for a given workload. Database Express represents this nicely, as shown in Figure 6-5.

Figure 6-5    Undo configuration in Database Express

The curve in the undo advisor window shows that if the undo tablespace were 5GB, it would be able to store undo data for between about 1,500 and 4,000 seconds (depending on activity). Larger or smaller sizes would increase or decrease the time for which undo would be kept.

Temporary Undo

Temporary undo segments are used for storing undo data generated by DML against global temporary tables. A global temporary table is a table whose definition may be visible to all sessions (global) but whose rows are private to the session that inserted them. The duration of the rows is either until COMMIT or until the session terminates (temporary).

Global temporary tables are often useful to developers. They offer a storage location for data that may be useful for interim result sets, without any need to worry about clearing the data when it is no longer needed or ensuring that sessions will not interfere with (or even see) data created by another session. From the database’s point of view, global temporary tables are easy to manage. They exist as segments created (and dropped) automatically in a temporary tablespace.

The performance on temporary tables is usually superior to the performance of permanent tables. This is for two reasons.

•  The I/O is direct. The session reads and writes its temporary segment without going through the buffer cache and without involving the database writer.

•  No redo is generated on global temporary tables. There would be no reason. The purpose of redo is to make changes persistent, and global temporary tables do not store persistent data.

Undo data is generated by DML against temporary tables. This is necessary because the usual transactional rules apply. And it follows that redo is generated because even though the table may be a temporary segment, the undo segment is not. In earlier releases, this caused these two problems:

•  A performance hit because of undo segment input/output (I/O) going via the buffer cache and generating associated redo

•  The impossibility of transactions against temporary tables in a read-only database; the table might not exist in a read/write tablespace, but the undo segment does

Release 12c has a facility that permits the creation of temporary undo segments in a temporary tablespace. This may significantly improve the performance of transactions against temporary tables without compromising the transactional integrity, and it also means that transactions can be run against read-only databases, such as a Data Guard physical standby database.

Creating and Managing Undo Tablespaces

As far as datafile management is concerned, an undo tablespace is the same as any other tablespace; files can be added, resized, taken online and offline, and moved or renamed. But it is not possible to specify any options regarding storage. You cannot specify automatic segment space management, and you cannot specify a uniform extent size. To create an undo tablespace, use the keyword UNDO.

By default, the tablespace will not guarantee undo retention. This characteristic can be specified at tablespace creation time or set later.

It is not possible to create segments in an undo tablespace, other than the undo segments that will be created automatically. Initially, there will be a pool of ten undo segments created in an undo tablespace. More will be created if there are more than ten concurrent transactions. Oracle will monitor the concurrent transaction rate and adjust the number of segments as necessary.

No matter how many undo tablespaces there may be in a database, generally speaking, only one will be in use at a time. The undo segments in this tablespace will have a status of online (meaning that they are available for use); the segments in any other undo tablespaces will have a status of offline, indicating that they will not be used. If the undo tablespace is changed, all the undo segments in the old undo tablespace will be taken offline and those in the new undo tablespace will be brought online. There are two exceptions to this:

•  In a Real Application Cluster (RAC) database, every instance opening the database must have its own undo tablespace. This can be controlled by setting the UNDO_TABLESPACE parameter to a different value for each instance. Each instance will bring its own undo segments online.

•  If the undo tablespace is changed by changing the UNDO_TABLESPACE parameter, any segments in the previously nominated tablespace that were supporting a transaction at the time of the change will remain online until the transaction finishes.

Exercise 6-6: Work with Undo Tablespaces    In this exercise, you will create an undo tablespace and bring it into use. Here are the steps to follow:

1.  Connect to your instance as user SYSTEM with SQL*Plus.

2.  Create an undo tablespace.

For the datafile’s path and name, use anything suitable for your environment.

3.  Run the following query, which will return one row for each tablespace in your database.

Note that your new tablespace has contents UNDO, meaning that it can be used only for undo segments and that retention is NOGUARANTEE.

4.  Run the following query, which will return one row for each rollback or undo segment in your database:

Note that ten undo segments have been created automatically in your new undo tablespace, but they are all offline.

5.  Adjust your instance to use the new undo tablespace. Use a SCOPE clause to ensure that the change will not be permanent.

6.  Rerun the query from step 4. You will see that the undo segments in the new tablespace have been brought online and those in the previously active undo tablespace are offline.

7.  Tidy up by setting the undo_tablespace parameter back to its original value and then dropping the new undo tablespace. Remember to use the INCLUDING CONTENTS AND DATAFILES clause.

Describe the Difference Between Undo Data and Redo Data

This topic gets a special mention because many mistakes are made by many people when describing undo and redo. Here is a set of contrasts:

•  Undo is transient, whereas redo is permanent. Undo data persists for at least the duration of the transaction that generates it, but possibly for no longer. Eventually, it will always be overwritten. Redo persists indefinitely, first in the online log files and then in the archive log files.

•  Undo operates at the logical level, whereas redo operates at the physical level. Undo data is row oriented; changes are grouped according to the transaction that made them, and within an undo segment all the entries are related to rows of the same transaction. Redo is physically oriented. Change vectors are related to physical locations in blocks by the row ID pointer. There is no relationship between consecutive change vectors in the log buffer or the log files.

•  Undo can reverse changes, whereas redo can repeat changes. Undo data provides the ability to reverse committed transactions. Redo provides the ability to replay work that has been lost.

•  Undo resides in tablespaces, whereas redo resides in files. Undo data is a segment structure within the database. Redo is written out to operating system files.

•  Undo are redo are not opposites. They have different functions. Undo is about transactional integrity, whereas redo is about preventing data loss.

Configure Undo Retention

Automatic undo management will always keep undo data for as long as possible. This is usually all that is required. However, there are two cases where DBA action may be necessary: if it is necessary to ensure that queries will always succeed, even if this means that DML may fail, and if flashback queries are being used extensively.

Configuring Undo Retention to Support Long-Running Queries

UNDO_RETENTION, set in seconds, is usually optional. It specifies a target for keeping inactive undo data and determines when it becomes classified as expired rather than unexpired. If, for example, your longest-running query is 30 minutes, you would set this parameter to 1800. Oracle will then attempt to keep all undo data for at least 1,800 seconds after COMMIT, and your query should therefore never fail with ORA-1555. If, however, you do not set this parameter or set it to zero, Oracle will still keep data for as long as it can anyway. The algorithm controlling which expired undo data is overwritten first will always choose to overwrite the oldest bit of data; therefore, UNDO_RETENTION is always at the maximum allowed by the size of the tablespace.

Where the UNDO_RETENTION parameter is not optional is if you have configured guaranteed undo retention. The default mode of operation for undo is that Oracle will favor transactions over queries. If the sizing of the undo tablespace is such that a choice has to be made between the possibility of a query failing with ORA-1555 and the certainty of a transaction failing with ORA-30036, Oracle will choose to let the transaction continue by overwriting undo data that a query might need. In other words, the undo retention is only a target that Oracle will try to achieve. But there may be circumstances when successful queries are considered more important than successful transactions. An example might be the end-of-month billing run for a utilities company, when it might be acceptable to risk transactions being blocked for a short time while the reports are generating. Another case is if you are making use of flashback queries, which rely on undo data.

Guaranteed undo retention, meaning that undo data will never be overwritten until the time specified by the undo retention has passed, is enabled at the tablespace level. This attribute can be specified at tablespace creation time, or an undo tablespace can be altered later to enable it. Once you activate an undo tablespace for which retention guarantee has been specified, all queries will complete successfully, provided they finish within the undo retention time; you will never have “snapshot too old” errors again. The downside is that transactions may fail for lack of undo space.

If the UNDO_RETENTION tablespace parameter has been set and the datafile (or datafiles) making up the undo tablespace is set to autoextend, then Oracle will increase the size of the datafile automatically if necessary to keep to the undo retention target. This combination of guaranteed undo retention and autoextending datafiles means that both queries and transactions will always succeed—assuming you have enough disk space. If you don’t, the automatic extension will fail.

Flashback Query and Undo Retention

Flashback Query can place additional demands on the undo system. Flashback Query is a facility that allows users to see the database as it was at a time in the past. There are several methods of making flashback queries, but the simplest is a straightforward SELECT statement with an AS OF clause. Here is an example:

This statement will return all the rows in the SCOTT.EMP table that were there 10 minutes ago. Rows that have been deleted will be seen, rows that have been inserted will not be seen, and rows that have been updated will be seen with their old values. This is the case whether or not the DML statements have been committed. To execute flashback queries, undo data is used to roll back all the changes. The rows that have been deleted are extracted from the undo segments and inserted back into the result set; rows that have been inserted are deleted from the result set. A query such as the preceding one that attempts to go back 10 minutes will probably succeed. A query that tries to go back a week would almost certainly fail because the undo data needed to reconstruct a version of the table as it was a week ago will have been overwritten.

Flashback Query can be a valuable tool. For example, if because of some mistake a deletion has occurred (and has been committed) at some time in the last hour, this command will reverse it by inserting all deleted rows back into the table:

If Flashback Query is likely to be used, then you must configure the undo system to handle it by setting the UNDO_RETENTION parameter to an appropriate value. If you want the ability to flash back a day, it must be set to 86,400 seconds. The undo tablespace must be appropriately sized. Then to be certain of success, either enable automatic extension for the undo tablespace’s datafiles or enable the retention guarantee for the tablespace.

Exercise 6-7: Work with Transactions and Flashback Query    In this exercise, you’ll demonstrate the manner in which undo data is used to provide transaction isolation and rollback, as well as to implement Flashback Query. Use the REGIONS table in the HR demonstration schema. Here are the steps to follow:

1.  Connect to the HR schema with two sessions concurrently. These can be two SQL*Plus sessions, two SQL Developer sessions, or one of each. The table that follows lists the steps to follow in each session:

2.  Demonstrate the use of Flashback Query using one session connected as user HR.

A.  Adjust your time display format to include seconds.

B.  Query and record the current time.

C.  Delete the row inserted previously and then commit the deletion.

D.  Query the table as it was before the row was deleted.

The deleted row for region 100 will be listed, having been retrieved from an undo segment.

Describe the Locking Mechanism and Data Concurrency Management

There are many types of locks. Most are internal to the server, such as locks necessary for serializing execution of certain critical sections of code or for protecting certain memory structures. The Oracle Certified Associate (OCA) exam does not cover these, although they are often important for tuning. The topics covered are locks that are used at the application layer, such as locks taken and released as SQL statements execute. These locks are applied either to rows or to entire tables, automatically or manually (if developers really need to do this).

Serialization of concurrent access is accomplished by record- and table-locking mechanisms. Locking in an Oracle database is completely automatic. Generally speaking, problems arise only if software tries to interfere with the automatic locking mechanism with poorly written code or if the business analysis is faulty and thus results in a business model where sessions will collide.

Shared and Exclusive Locks

The standard level of locking in an Oracle database guarantees the highest possible level of concurrency. This means that if a session is updating one row, the one row is locked and nothing else. Furthermore, the row is locked only to prevent other sessions from updating it; other sessions can read it at any time. The lock is held until the transaction completes, either with a COMMIT or with a ROLLBACK. This is an exclusive lock; the first session to request the lock on the row gets it, and any other sessions requesting write access must wait. Read access is permitted—although if the row has been updated by the locking session, as will usually be the case, then any reads will involve the use of undo data to make sure that reading sessions do not see any uncommitted changes.

Only one session can take an exclusive lock on a row, or a whole table, at a time—but shared locks can be taken on the same object by many sessions. It would not make any sense to take a shared lock on one row because the only purpose of a row lock is to gain the exclusive access needed to modify the row. Shared locks are taken on whole tables, and many sessions can have a shared lock on the same table. The purpose of taking a shared lock on a table is to prevent another session from acquiring an exclusive lock on the table. You cannot get an exclusive lock if anyone else already has a shared lock. Exclusive locks on tables are required to execute DDL statements. You cannot issue a statement that will modify an object (for instance, dropping a column of a table) if any other session already has a shared lock on the table.

To execute DML on rows, a session must acquire exclusive locks on the rows to be changed, as well as shared locks on the tables containing the rows. If another session already has exclusive locks on the rows, the session will hang until the locks are released by a COMMIT or a ROLLBACK. If another session already has a shared lock on the table and exclusive locks on other rows, that is not a problem. An exclusive lock on the table could be a problem, but the default locking mechanism does not lock whole tables unless this is necessary for DDL statements.

All DML statements require at least two locks: an exclusive lock on each row affected and a shared lock on the table containing the row. The exclusive lock prevents another session from interfering with the row, and the shared lock prevents another session from changing the table definition with a DDL statement. These locks are requested automatically. If a DML statement cannot acquire the exclusive row locks it needs, it will hang until it gets them.

Executing DDL commands requires an exclusive lock on the object concerned. This cannot be obtained until all DML transactions against the table have finished, thereby releasing both their exclusive row locks and their shared table locks. The exclusive lock required by any DDL statement is requested automatically, but if it cannot be obtained—typically because another session already has the shared lock granted for DML—then the statement will terminate with an error immediately.

The Enqueue Mechanism

Requests for locks are queued. If a session requests a lock and cannot get it because another session already has the row or object locked, the session will wait. It may be that several sessions are waiting for access to the same row or object—in that case, Oracle will keep track of the order in which the sessions requested the lock. When the session with the lock releases it, the next session will be granted the lock, and so on. This is known as the enqueue mechanism.

If you do not want a session to queue up if it cannot get a lock, the only way to avoid this is to use the WAIT or NOWAIT clause of the SELECT…FOR UPDATE command. A normal SELECT will always succeed because SELECT does not require any locks, but a DML statement will hang. The SELECT…FOR UPDATE command will select rows and lock them in exclusive mode. If any of the rows are locked already, the SELECT…FOR UPDATE statement will be queued, and the session will hang until the locks are released, just like a DML statement would. To avoid sessions hanging, use either SELECT…FOR UPDATE NOWAIT or SELECT…FOR UPDATE WAIT <n>, where <n> is a number of seconds. Having obtained the locks with either of the SELECT…FOR UPDATE options, you can then issue the DML commands with no possibility of the session hanging.

Automatic and Manual Locking

Whenever any DML statement is executed, as part of the execution, the session will automatically take a shared lock on the table and exclusive locks on the affected rows. This automatic locking is perfect for virtually all operations. It offers the highest possible level of concurrency (minimizing the possibilities for contention) and requires no programmer input whatsoever. These locks are released, also automatically, when the transaction is completed with either COMMIT or ROLLBACK.

Whenever any DDL statement is executed, the session will automatically take an exclusive lock on the entire object. This lock persists for the duration of the DDL statement and is released automatically when the statement completes. Internally, what is happening is that the DDL is, in fact, DML statements against rows in tables in the data dictionary. If one could see the source code of, for example, the DROP TABLE command, this would be clear. It will (among other things) be deleting a number of rows from the SYS.COL$ table, which has one row for every column in the database, and one row from the SYS.TAB$ table, which has one row for every table, followed by COMMIT. Most DDL statements are quick to execute, so the table locks they require will usually not be noticed by users.

Manually locking objects can be done. The syntax is as follows:

Five modes are possible, each of which may or may not be compatible with another lock request of a certain mode from another session:

•  ROW SHARE

•  ROW EXCLUSIVE

•  SHARE

•  SHARE ROW EXCLUSIVE

•  EXCLUSIVE

The following table shows the lock compatibilities. If one session has taken the lock type listed across the top of the table, another session will (Y) or will not (N) be able to take the type of lock listed in the first column.

For example, if a session has taken a ROW SHARE lock on a table, other sessions can take any type of lock except EXCLUSIVE. On the other hand, if a session has an EXCLUSIVE lock on the object, no other session can take any lock at all. A ROW SHARE lock permits DML by other sessions but will prevent any other session from taking an EXCLUSIVE lock on the table. An EXCLUSIVE lock is needed (and requested automatically) in order to drop the table. ROW SHARE will therefore ensure that the table is not dropped by another session. In practice, the only type of lock that is ever likely to be taken manually is the EXCLUSIVE lock. This will prevent any other sessions from performing any DML against the table.

Whenever a session does any DML against a table, it will (automatically) take a table lock in ROW EXCLUSIVE mode. Because this is not incompatible with other sessions taking a ROW EXCLUSIVE mode lock, many sessions can perform DML concurrently—as long as they do not attempt to update the same rows.

Monitor and Resolve Locking Conflicts

When a session requests a lock on a row or object and cannot get it because another session has an exclusive lock on the row or object, it will hang. This is lock contention, and it can cause the database performance to deteriorate appallingly as sessions queue up waiting for locks. Some lock contention may be inevitable as a result of normal activity; the nature of the application may be such that different users will require access to the same data. But in many cases, lock contention is caused by program and system design.

The Oracle database provides utilities for detecting lock contention, and it is also possible to solve the problem in an emergency. A special case of lock contention is the deadlock, which is always resolved automatically by the database.

The Causes of Lock Contention

It may be that the nature of the business is such that users do require write access to the same rows at the same time. If this is a limiting factor in the performance of the system, the only solution is business process reengineering to develop a more efficient business model. However, although some locking is a necessary part of business data processing, some faults in application design can exacerbate the problem.

Long-running transactions will cause problems. An obvious case is where a user updates a row and then does not commit the change. Perhaps they even go off to lunch, leaving the transaction unfinished. You cannot stop this from happening if users have access to the database with tools such as SQL*Plus, but it should never occur with well-written software. The application should take care that a lock is imposed only just before an update occurs and then released (with a COMMIT or ROLLBACK) immediately afterward.

Poorly written batch processes can also cause problems if they are coded as long transactions. Consider the case of an accounting suite nominal ledger. It is a logical impossibility in accountancy terms for the ledger to be partly in one period and partly in another, so the end-of-month rollover to the next period is one business transaction. This transaction may involve updating millions of rows in thousands of tables and take hours to complete. If the rollover routine is coded as one transaction with a COMMIT at the end, millions of rows will be locked for hours—but in accountancy terms, this is what should happen. Good program design would avoid the problem by updating the rows in groups, with regular commits, but the programmers will also have to take care of simulating read consistency across transactions and handle the situation where the process fails partway through. If it were one transaction, this wouldn’t be a problem; the database would roll it back. If it involves many small transactions, they will have to manage a ledger that is half in one period and half in another. These considerations should not be a problem. Your programmers should bear in mind long transactions’ impact on the usability of the system and design their systems accordingly.

Third-party user process products may impose excessively high locking levels. For example, some application development tools always do a SELECT…FOR UPDATE to avoid the necessity of requerying the data and checking for changes. Some other products cannot do row-level locking. If a user wants to update one row, the tool locks a group of rows—perhaps dozens or even hundreds. If your application software is written with tools such as these, the Oracle database will simply do what it is told to do; it will impose numerous locks that are unnecessary in business terms. If you suspect that the software is applying more locks than necessary, investigate whether it has configuration options to change this behavior.

Lastly, make sure your programmers are aware of the capabilities of the database. A common problem is repeatable reads. Consider this example:

How can this be possible? The first query (the detail report) shows four rows, but then the second query (the summary report) shows five. The problem is that during the course of the first query, another session inserted and committed the fifth row. One way out of this would be to lock the table while running the reports, thus causing other sessions to hang. A more sophisticated way would be to use the SET TRANSACTION READ ONLY statement. This will guarantee (without imposing any locks) that the session does not see any DML on any tables, committed or not, until it terminates the read-only transaction with a COMMIT or ROLLBACK. The mechanism is based on the use of undo segments.

Detecting Lock Contention

Certain views will tell you what is going on with locking in the database, and Database Express offers a graphical interface for lock monitoring. Lock contention is a natural consequence of many users accessing the same data concurrently. The problem can be exacerbated by badly designed software, but in principle, lock contention is part of normal database activity. It is therefore not possible for DBAs to resolve it completely; they can identify only that it is a problem and suggest to system and application designers that they bear in mind the impact of lock contention when designing data structures and programs.

Lock contention can be seen in the V$SESSION view. This view has one row for each currently logged-on session. The unique identifier is the column session identifier (SID). If a session is blocked by another session, the SID of the blocking session is shown in the column BLOCKING_SESSION. Figure 6-6 shows a query that joins V$SESSION to itself, using the SID and BLOCKING_SESSION columns. The only sessions listed are those that are blocked; all other sessions will have BLOCKING_SESSION=NULL and will therefore not be included.

Figure 6-6    Finding and killing blocking sessions

In the figure, user MILLER is blocking two other sessions: JW and KING. User SCOTT is blocking user SYSTEM. To identify lock contention in Database Express, click the Performance tab, click the link for Performance Hub, and then click the Current ADDM Findings tab. This will show current detected issues, with details visible if you click the Impact bar. Figure 6-7 shows a situation where one session, SID=256, is blocking two other sessions (SID=15 and SID=251) with row lock enqueues. It is also possible to understand the queue: Session SID=15 is blocked by both sessions 251 and 256, so killing 256 (which is suggested as Recommendation 1) will not be enough to free it. However, killing session 256 will free up session 251. This will allow 251 to complete its work, which may then free up session 15. All the sessions are identified by a three-faceted session ID, consisting of the instance number (which is always 1 because this is not a clustered database), the SID, and the SERIAL#.

Figure 6-7    Lock contention as displayed in Database Express

Solving Lock Contention

In most circumstances, such blocks will be very short lived. The blocking sessions will commit their transactions, and the blocked sessions will then be able to work. In an emergency, however, it is possible for the DBA to solve the problem—by terminating the session (or sessions) holding too many locks for too long. When a session is terminated forcibly, any locks it holds will be released as its active transaction is rolled back. The blocked sessions will then become free and can continue.

To terminate a session, use the ALTER SYSTEM KILL SESSION command. It takes the SID and SERIAL# of the session to identify which session to kill. The SID is unique at any given moment, but as users log on and off, SIDs will be reused. The SID plus SERIAL# is guaranteed to be unique for the lifetime of the instance, and both must be specified when a session is killed.

Deadlocks: A Special Case

It is possible to construct a position where two sessions block each other in such a fashion that both will hang, each waiting for the other to release its lock. This is a deadlock. Deadlocks are caused by bad program design and are resolved automatically by the database. Information regarding deadlocks is written out to the alert log, with full details in a trace file—part of your daily monitoring will pick up the occurrence of deadlocks and inform your developers that they are happening.

If a deadlock occurs, both sessions will hang, but only for a brief moment. One of the sessions will detect the deadlock within seconds, and it will roll back the statement that caused the problem. This will free up the other session, returning the message “ORA-00060 Deadlock detected.” This message must be trapped by your programmers in their exceptions clauses, which should take appropriate action.

It must be emphasized that deadlocks are a program design fault. They occur because the code attempts to do something that is logically impossible. Well-written code will always request locks in a sequence that cannot cause deadlocks to occur or will test whether incompatible locks already exist before requesting them.

Exercise 6-8: Describe the Locking Mechanism and Data Concurrency Management    In the first part of this exercise, you will use SQL*Plus to cause a problem and then detect and resolve it. In the second part, you will learn how to handle deadlocks.

Here are the steps to follow to demonstrate lock contention:

1.  Using SQL*Plus, connect to your database with three sessions as user SYSTEM.

2.  In the first session, create a table.

3.  In the second session, update a row.

4.  In the third session, issue the same statement as in step 2. The session will hang.

5.  In the first session, run this query to identify which session is blocked:

Then run this query to retrieve the necessary details of the blocking session:

When prompted, enter the blocking session’s SID.

6.  In the first session, kill the blocking session.

When prompted, enter the blocking session’s SID and SERIAL#.

7.  Tidy up.

A.  In the second session, attempt to run any SQL statement. You will receive the message “ORA-00028: your session has been killed.”

B.  In the third session, the update will now have succeeded and completed. Terminate the transaction with COMMIT or ROLLBACK.

Here are the steps to follow to demonstrate deadlocks:

1.  In your first session, update a row.

2.  In your third session, update a second row.

3.  In your first session, attempt to update the second row.

This will hang because the row is already locked.

4.  Set up the deadlock by updating the first row in the third session.

This will hang, but within three seconds the first session will become free with the message “ORA-00060: deadlock requested while waiting for resource.”

5.  Retrieve information about the deadlock from the alert log and trace files.

A.  From an operating system prompt, open the alert log in the database’s trace directory. The location can be found with this query:

and the file will be called alert_SID.ora. The last entry in the file will be something like this (a Windows example):

B.  Open the trace file with an editor. Toward the top of the file will be this critical message:

Note that this message places the responsibility firmly on the developers. Look lower down in the file, and you will find the SIDs of the sessions involved and the statements they executed that caused the deadlock.

6.  Tidy up. In the first session, issue a ROLLBACK to roll back the update that did succeed. This will free up the third session, where you can now also issue a ROLLBACK. Drop the LOCKDEMO table.

Two-Minute Drill

Describe Each Data Manipulation Language Statement

•  INSERT enters rows into a table.

•  UPDATE adjusts the values in existing rows.

•  DELETE removes rows.

•  MERGE can combine the functions of INSERT, UPDATE, and DELETE.

•  Even though TRUNCATE is not DML, it does remove all rows in a table.

•  It is possible for an INSERT to enter rows into multiple tables.

•  Subqueries can be used to generate the rows to be inserted, updated, or deleted.

•  An INSERT, UPDATE, or DELETE is not permanent until it is committed.

•  TRUNCATE removes every row from a table.

•  A TRUNCATE is immediately permanent; it cannot be rolled back.

Control Transactions

•  A transaction is a logical unit of work, possibly comprising several DML statements.

•  Transactions are invisible to other sessions until committed.

•  Until committed, transactions can be rolled back.

•  A SAVEPOINT lets a session roll back part of a transaction.

Explain DML and Undo Data Generation

•  All DML commands generate undo and redo.

•  Redo protects all changes to segments—undo segments as well as data segments.

•  Server processes read from datafiles; DBWn writes to datafiles.

Monitor and Administer Undo Data

•  An instance will use undo segments in one nominated undo tablespace.

•  More undo tablespaces may exist, but only one will be used at a time.

•  The undo tablespace should be large enough to take account of the maximum rate of undo generation and the longest-running query.

•  Undo tablespace datafiles are datafiles like any others.

Describe the Difference Between Undo Data and Redo Data

•  Undo protects transactions, whereas redo protects block changes. They are not opposites; they are complementary.

Configure Undo Retention

•  Undo data will always be kept until the transaction that generated it completes with a COMMIT or a ROLLBACK. This is active undo.

•  Undo data will be retained for a period after it becomes inactive to satisfy any read consistency requirements of long-running queries; this is unexpired undo.

•  Expired undo is data no longer needed for read consistency and may be overwritten at any time as space in undo segments is reused.

•  Configure undo retention by setting a target with the undo_retention parameter. This is only a target, and if the undo tablespace has insufficient space, it will not be attained—unless you set the tablespace to RETENTION GUARANTEE, which risks transactions failing for lack of undo space.

Describe the Locking Mechanism and Data Concurrency Management

•  The default level of locking is row level.

•  Locks are required for all DML commands and are optional for SELECT.

•  A DML statement requires shared locks on the objects involved and exclusive locks on the rows involved.

•  A DDL lock requires an exclusive lock on the object it affects.

Monitor and Resolve Locking Conflicts

•  Blocking caused by row locks can be identified by querying the V$SESSION view or through Database Express.

•  Resolve lock contention either by terminating blocking transactions or by killing the blocking session.

•  Deadlocks are resolved automatically.

Self Test

1.  Which of the following commands can be rolled back? (Choose all that apply.)

A.  COMMIT

B.  DELETE

C.  INSERT

D.  MERGE

E.  TRUNCATE

F.  UPDATE

2.  If an UPDATE or DELETE command has a WHERE clause that gives it a scope of several rows, what will happen if there is an error partway through execution? The command is one of several in a multistatement transaction. (Choose the best answer.)

A.  The command will skip the row that caused the error and continue.

B.  The command will stop at the error, and the rows that have been updated or deleted will remain updated or deleted.

C.  Whatever work the command had done before hitting the error will be rolled back, but work done already by the transaction will remain.

D.  The whole transaction will be rolled back.

3.  Study the result of this SELECT statement:

If you issue this statement:

why will it fail? (Choose the best answer.)

A.  Because values are not provided for all the table’s columns. There should be NULLs for C3 and C4.

B.  Because the subquery returns multiple rows. It requires a WHERE clause to restrict the number of rows returned to one.

C.  Because the subquery is not scalar. It should use MAX or MIN to generate scalar values.

D.  Because the VALUES keyword is not used with a subquery.

E.  It will succeed, inserting two rows with NULLs for C3 and C4.

4.  You want to insert a row and then update it. What sequence of steps should you follow? (Choose the best answer.)

A.  INSERT, UPDATE, COMMIT

B.  INSERT, COMMIT, UPDATE, COMMIT

C.  INSERT, SELECT FOR UPDATE, UPDATE, COMMIT

D.  INSERT, COMMIT, SELECT FOR UPDATE, UPDATE, COMMIT

5.  Which of these commands will remove every row in a table? (Choose all correct answers.)

A.  A DELETE command with no WHERE clause

B.  A DROP TABLE command

C.  A TRUNCATE command

D.  An UPDATE command, setting every column to NULL and with no WHERE clause

6.  User JOHN updates some rows and asks user ROOPESH to log in and check the changes before he commits them. Which of the following statements is true? (Choose the best answer.)

A.  ROOPESH can see the changes but cannot alter them because JOHN will have locked the rows.

B.  ROOPESH will not be able to see the changes.

C.  JOHN must commit the changes so that ROOPESH can see them and, if necessary, roll them back.

D.  JOHN must commit the changes so that ROOPESH can see them, but only JOHN can roll them back.

7.  There are several steps involved in executing a DML statement. Place these in the correct order:

A.  Apply the change vectors to the database buffer cache.

B.  Copy blocks from datafiles into buffers.

C.  Search for the relevant blocks in the database buffer cache.

D.  Write the change vectors to the log buffer.

8.  When a COMMIT is issued, what will happen? (Choose the best answer.)

A.  All the change vectors that make up the transaction are written to disk.

B.  DBWn writes the change blocks to disk.

C.  LGWR writes the log buffer to disk.

D.  The undo data is deleted so that the changes can no longer be rolled back.

9.  What types of segments are protected by redo? (Choose all that apply.)

A.  Index segments

B.  Table segments

C.  Temporary segments

D.  Undo segments

10.  Which of these commands will terminate a transaction? (Choose all that apply.)

A.  CREATE

B.  GRANT

C.  SAVEPOINT

D.  SET AUTOCOMMIT ON

11.  Locks are needed to stop sessions from working on the same data at the same time. If one user updates a row without specifying any locking, what will be the effect on other sessions?

A.  Others will be able to read and write other rows but not the affected rows.

B.  Others will be able to read the affected rows but not write to them.

C.  Others will be able to read and write the affected rows, but a COMMIT will hang until the first session has been committed or rolled back.

D.  Others will not be able write any rows because, by default, the first session will have taken a lock on the entire table.

12.  Which of these commands will prevent other sessions from reading rows in the table? (Choose the best answer.)

A.  LOCK TABLE SCOTT.EMP IN EXCLUSIVE MODE;

B.  LOCK TABLE SCOTT.EMP IN ROW EXCLUSIVE MODE;

C.  SELECT * FROM SCOTT.EMP FOR UPDATE;

D.  DELETE FROM SCOTT.EMP;

E.  Oracle does not provide a lock that will prevent others from reading a table.

13.  If several sessions request an exclusive lock on the same row, what will happen? (Choose the best answer.)

A.  The first session will get the lock; after it releases the lock, there is a random selection of the next session to get the lock.

B.  The first session will get an exclusive lock, and the other sessions will get shared locks.

C.  The sessions will be given an exclusive lock in the sequence in which they requested it.

D.  Oracle will detect the conflict and roll back the statements that would otherwise hang.

14.  If a programmer does not request a type of lock when updating many rows in one table, what lock or locks will they be given? (Choose the best answer.)

A.  No locks at all. The default level of locking is NONE in order to maximize concurrency.

B.  An exclusive lock on the table. This is the fastest method when many rows are being updated.

C.  Shared locks on the table and on each row. This is the safest (although not the fastest) method.

D.  An exclusive lock on each row and a shared lock on the table. This maximizes concurrency safely.

15.  What happens if two sessions deadlock against each other? (Choose the best answer.)

A.  Oracle will roll back one session’s statement.

B.  Oracle will roll back both sessions’ statements.

C.  Both sessions will hang indefinitely.

D.  Oracle will terminate one session.

E.  Oracle will terminate both sessions.

16.  When a DML statement executes, what happens? (Choose the best answer.)

A.  Both the data and the undo blocks on disk are updated, and the changes are written out to the redo stream.

B.  The old version of the data is written to an undo segment, and the new version is written to the data segments and the redo log buffer.

C.  Both data and undo blocks are updated in the database buffer cache, and the updates also go to the log buffer.

D.  The redo log buffer is updated with information needed to redo the transaction, and the undo blocks are updated with information needed to reverse the transaction.

17.  If you suspect that undo generation is a performance issue, what can you do to reduce the amount of undo data generated? (Choose the best answer.)

A.  Convert from use of rollback segments to automatic undo management.

B.  Set the UNDO_MANAGEMENT parameter to NONE.

C.  Reduce the size of the undo segments.

D.  There is nothing you can do because all DML statements must generate undo.

18.  First, user JOHN initiates a query. Second, user ROOPESH updates a row that will be included in the query. Third, JOHN’s query completes. Fourth, ROOPESH commits his change. Fifth, JOHN runs his query again. Which of the following statements are correct? (Choose all that apply.)

A.  The principle of consistency means that both of JOHN’s queries will return the same result set.

B.  When ROOPESH commits, the undo data is flushed to disk.

C.  When ROOPESH commits, the undo becomes inactive.

D.  JOHN’s first query will use undo data.

E.  JOHN’s second query will use undo data.

F.  The two queries will be inconsistent with each other.

19.  If an undo segment fills up, what will happen? (Choose the best answer.)

A.  Another undo segment will be created automatically.

B.  The undo segment will increase in size.

C.  The undo tablespace will extend if its datafiles are set to autoextend.

D.  Transactions will continue in a different undo segment.

20.  Which of the following statements are correct about undo? (Choose all that apply.)

A.  One undo segment can protect many transactions.

B.  One transaction can use many undo segments.

C.  One database can have many undo tablespaces.

D.  One instance can have many undo tablespaces.

E.  One undo segment can be cut across many datafiles.

F.  Undo segments and rollback segments cannot coexist.

21.  Your undo tablespace has 10 undo segments, but during a sudden burst of activity you have 20 concurrent transactions. What will happen? (Choose the best answer.)

A.  Oracle will create another ten undo segments.

B.  The transactions will be automatically balanced across the ten undo segments.

C.  Ten transactions will be blocked until the first ten commit.

D.  What happens will depend on your UNDO_RETENTION setting.

22.  Your users are reporting “ORA-1555: Snapshot too old” errors. What might be the cause of this? (Choose the best answer.)

A.  You are not generating snapshots frequently enough.

B.  The undo data is too old.

C.  There is not enough undo data.

D.  Your undo tablespace is retaining data for too long.

23.  Examine this query and result set:

The block size of the undo tablespace is 4KB. Which of the following would be the optimal size for the undo tablespace? (Choose the best answer.)

A.  1GB

B.  2GB

C.  3GB

D.  4GB

24.  When do changes get written to the log buffer? (Choose all that apply.)

A.  When a table block is updated

B.  When an index block is updated

C.  When an undo block is updated

D.  During rollback operations

E.  On COMMIT

F.  When queries are run that access blocks with uncommitted changes

25.  Even though you are using automatic undo segments, users are still getting “snapshot too old” errors. What could you do? (Choose all that apply.)

A.  Increase the UNDO_RETENTION parameter.

B.  Set the RETENTION_GUARANTEE parameter.

C.  Tune the queries to make them run faster.

D.  Increase the size of the undo tablespace.

E.  Enable RETENTION GUARANTEE.

F.  Increase the size of your undo segments.

Self Test Answers

1.      B, C, D, and F. These are the DML commands; they can all be rolled back.
    A and E are incorrect. COMMIT terminates a transaction, which can then never be rolled back. TRUNCATE is a DDL command and includes a built-in COMMIT.

2.      C. This is the expected behavior: The statement is rolled back, and the rest of the transaction remains uncommitted.
    A, B, and D are incorrect. A is incorrect because while this behavior is, in fact, configurable, it is not enabled by default. B is incorrect because while this is, in fact, possible in the event of space errors, it is not enabled by default. D is incorrect because only the one statement will be rolled back, not the whole transaction.

3.      D. The syntax is incorrect; use either the VALUES keyword or a subquery, but not both. Remove the VALUES keyword, and it will run. C3 and C4 would be populated with NULLs.
    A, B, C, and E are incorrect. A is incorrect because there is no need to provide values for columns not listed. B and C are incorrect because an INSERT can insert a set of rows, so there is no need to restrict the number with a WHERE clause or by using MAX or MIN to return only one row. E is incorrect because the statement is not syntactically correct.

4.      A. This is the simplest (and therefore the best) way.
    B, C, and D are incorrect. All these will work, but they are all needlessly complicated. No programmer should use unnecessary statements.

5.      A and C. The TRUNCATE will be faster, but the DELETE will get there too.
    B and D are incorrect. B is incorrect because this will remove the table as well as the rows within it. D is incorrect because the rows will still be there—even though they are populated with NULLs.

6.      B. The principle of isolation means that only JOHN can see his uncommitted transaction.
    A, C, and D are incorrect. A is incorrect because transaction isolation means that no other session will be able to see the changes. C and D are incorrect because a committed transaction can never be rolled back.

7.      C, B, D, and A. This is the sequence. All others are incorrect.

8.      C. A COMMIT is implemented by placing a COMMIT record in the log buffer and LGWR flushing the log buffer to disk.
    A, B, and D are incorrect. A is incorrect because many of the change vectors (perhaps all of them) will be on disk already. B is incorrect because DBWn does not participate in commit processing. D is incorrect because the undo data may well persist for some time; a COMMIT is not relevant to this.

9.      A, B, and D. Changes to any of these will generate redo.
    C is incorrect. Changes to temporary segments do not generate redo.

10.      A and B. Both DDL and access control commands include a COMMIT.
    C and D are incorrect. C is incorrect because a savepoint is only a marker within a transaction. D is incorrect because this is a SQL*Plus command that acts locally on the user process; it has no effect on an active transaction.

11.      B. By default, a row exclusive lock will protect the row against write but not read.
    A, C, and D are incorrect. A is incorrect because reading the updated row (in its pre-update form) will always be possible. C is incorrect because it is DML that will be blocked, not COMMIT. D is incorrect because the default exclusive lock is on the affected row only.

12.      E. Readers are never blocked.
    A, B, C, and D are incorrect. A is incorrect because it will lock all rows against update, and B is incorrect because it will lock any updated rows. Neither will block readers. C and D are incorrect because they will take shared locks on the table and exclusive locks on the rows. Again, neither can prevent reads.

13.      C. This correctly describes the operation of the enqueue mechanism.
    A, B, and D are incorrect. A is incorrect because locks are granted sequentially, not randomly. B is incorrect because the shared locks apply to the object; row locks must be exclusive. D is incorrect because this is more like a description of how deadlocks are managed.

14.      D. This correctly describes the DML locking mechanism: a shared lock to protect the table definition and exclusive locks to protect the rows.
    A, B, and C are incorrect. A is incorrect because locks are always imposed. B is incorrect because exclusive table locks are applied only if the programmer requests them. C is incorrect because exclusive locks must always be taken on rows.

15.      A. One of the statements will be automatically rolled back, allowing the session to continue.
    B, C, D, and E are incorrect. B is incorrect because only one statement will be rolled back. The other will remain in effect, blocking its session. C is incorrect because this is exactly the effect that is avoided. D and E are incorrect because the deadlock-resolution mechanism does not terminate sessions, only statements.

16.      C. All DML occurs in the database buffer cache, and changes to both data blocks and undo blocks are protected by redo.
    A, B, and D are incorrect. A is incorrect because writing to disk is independent of executing the statement. B and D are incorrect because they are incomplete: Redo protects changes to both data blocks and undo blocks.

17.      D. All DML generates undo, so the only way to reduce undo generation would be to redesign the application.
    A, B, and C are incorrect. A is incorrect because although automatic undo is more efficient, it cannot reduce undo. B is incorrect because there is no parameter setting that can switch off undo. C is incorrect because the size of the segments will affect only how quickly they are reused, not how much undo is generated.

18.      C, D, and F. C is correct because undo becomes inactive on commit (although it does not necessarily expire). D is correct because the query will need undo data to construct a result consistent with the state of the data at the start of the query. F is correct because Oracle guarantees consistency within a query, not across queries.
    A, B, and E are incorrect. A is incorrect because Oracle guarantees consistency within a query, not across queries. B is incorrect because there is no correlation between a COMMIT and a write to the datafiles. E is incorrect because the second query is against a table that is not changed during the course of the query.

19.      B. Undo segments extend as a transaction generates more undo data.
    A, C, and D are incorrect. A is incorrect because another undo segment will be created only if there are more concurrent transactions than segments. C confuses the effect of a segment filling up with that of the tablespace filling up. D is impossible because one transaction can be protected by only one undo segment.

20.      A, C, E. A is correct, although Oracle will try to avoid this. C is correct, although only one will be made active at any moment by the instance. E is correct because when it comes to storage, an undo segment is like any other segment: The tablespace abstracts the physical storage from the logical storage.
    B, D, and F are incorrect. B is incorrect because one transaction is protected by one undo segment. D is incorrect because one instance can use only one undo tablespace. F is incorrect because undo and rollback segments can coexist, but a database can use only one or the other.

21.      A. Undo segments are spawned according to demand.
    B, C, and D are incorrect. B is incorrect because more segments will be created. C is incorrect because there is no limit imposed by the number of undo segments. D is incorrect because this parameter is not relevant to transactions, only to queries.

22.      C. An “ORA-1555: snapshot too old” error is a clear indication that undo data is not being kept for long enough to satisfy the query workload. There is not enough undo data available.
    A, B, and D are incorrect. A is incorrect because it doesn’t refer to undo at all—it refers to snapshots, which existed in earlier versions of the database but are now called materialized views. B and D are both incorrect because they describe the opposing situation, where undo data is being retained for longer than necessary. This is not a problem, but it may be a waste of space.

23.      C. To calculate this, take the largest figure for UNDBLKS, which is for a 10-minute period. Divide by 600 to get the rate of undo generation in blocks per second, and multiply by the block size to get the figure in bytes. Multiply by the largest figure for MAXQUERYLEN to find the space needed if the highest rate of undo generation coincided with the longest query, and then divide by a billion to get the answer in gigabytes:
237,014 / 600 * 4,096 * 1,740 = 2.6GB (approximately).
    A, B, and D are incorrect. These figures are derived from an incorrect understanding of the undo arithmetic (detailed for C).

24.      A, B, C, and D. Changes to all data blocks are protected by redo. A rollback operation changes blocks and therefore also generates redo.
    E and F are incorrect. COMMIT does not write changes because they have already been written. Queries never read or write redo, although they may well read undo.

25.      C, D, and E. Answer C is correct because making the queries complete faster will reduce the likelihood of “snapshot too old.” D is correct because it will allow more unexpired undo to be stored. E will solve the problem completely, although it may cause problems with transactions.
    A, B, and F are incorrect. A is incorrect because it won’t help by itself—it is just a target, unless combined with E. B is incorrect because there is no such parameter (although it is a clause that can be applied to an undo tablespace). F is incorrect because this cannot be done manually—Oracle will already be doing its best automatically.