Chapter 8. Managing Consistency and Concurrency

ORACLE DATABASE 10 G: ADMINISTRATION I EXAM OBJECTIVES COVERED IN THIS CHAPTER:

• Undo Management

  • Monitor and administer undo.

  • Configure undo retention.

  • Guarantee undo retention.

  • Use the Undo Advisor.

  • Describe the relationship between undo and transactions.

  • Size the undo tablespace.

• Monitoring and Resolving Lock Conflicts

  • Detect and resolve lock conflicts.

  • Manage deadlocks.

  • Describe the relationship between transactions and locks.

  • Explain lock modes within the Oracle Database 10g.

If a database user wants to change their mind after running a DML (Data Manipulation Language) statement, Oracle Database10g (Oracle 10g) retains undo data needed to bring back the data changed by the user since the beginning of a transaction. This same undo data allows the user to view the data in a table as of a point of time in the past. The undo data provides yet another benefit: a query against a table that may be changing after the query started will not see the changes until a new query is issued against the table. Ensuring that the tablespace that holds undo data is sized correctly is an important job for you as a DBA, preventing those late-night phone calls from users who either cannot run their queries to completion or make their nightly updates to the ORDERS table!

Oracle's GUI administration tool, the EM Database Control, makes undo administration and sizing simpler than ever. The Undo Advisor collects statistics on an ongoing basis to help you size the undo tablespace so that DML statements within a transaction can complete successfully while at the same time allowing SELECT statements to complete successfully without receiving the all-too-familiar "Snapshot too old" error; this information is presented in the EM Database Control environment as an easy-to-read graph, making it simple for you to adjust the undo retention and other settings.

Oracle also provides a number of ways to both monitor and resolve locking conflicts in the database. Oracle's locking mechanism keeps locking at the lowest level possible, although users can lock resources at a higher level when necessary. Row-level locking was introduced in Oracle version 6.

The first part of this chapter covers all aspects of undo management: monitoring and administering undo data, ensuring that undo data is available for user transactions, and calculating the size of the undo tablespace. In the last half of this chapter, we'll review the types of locks available and how to manage locking and contention for the same resources by more than one user or transaction. As with undo administration, the EM Database Control environment makes lock management easier than in previous releases of Oracle.