8.3. Summary

In this chapter, we presented the undo tablespace and its importance for the two types of database users: those who want to query a table and receive consistent results, and those who want to make changes to a table and have the option to roll back the data to its state when the transaction started. The undo tablespace provides undo information, or the value of rows in a table before changes were made, for both classes of users. More specifically, undo data facilitates rollback operations, read consistency, certain database recovery operations, and several types of flashback features, some of which were introduced in Oracle9i and greatly expanded in Oracle 10g.

An undo tablespace can be configured with a handful of initialization parameters: UNDO_ MANAGEMENT to define the mode in which undo is managed, with values of either MANUAL or AUTO. The UNDO_TABLESPACE parameter identifies the current undo tablespace, which can be switched while the database is open to users; however, only one undo tablespace can be active at a time.

You can use the EM Database Control to both proactively monitor and resize the undo tablespace, before you get the phone call from the user whose transactions are failing or SELECT statements are not completing. For databases whose long-running queries have priority over successful DML transactions, you can specify that an undo tablespace retain expired undo information at the expense of failed transactions.

In the second part of the chapter, we showed you how to monitor resource locks within a transaction, both at the row level and the table level. Although Oracle usually manages locks at the minimum level to ensure that two sessions do not try to simultaneously update the same row in a table, you can explicitly lock a table at a number of levels. In addition, you can lock a subset of rows in a table to prevent updates or locks from other transactions with the FOR UPDATE clause in the SELECT statement.

Finally, we presented some reasons that lock conflicts occur and how to resolve them; a special kind of lock conflict, called a deadlock, occurs when two users are waiting on a resource locked by the other user. Deadlocks, unlike other types of lock conflicts, are resolved quickly and automatically by Oracle long before any manual lock resolution is attempted.