8.4. Exam Essentials

Know the purpose of the Undo Advisor. Optimize the UNDO_RETENTION parameter as well as the size of the undo tablespace by using Undo Advisor. Use the graph on the Undo Advisor screen to perform what-if analyses given the undo retention requirements.

Be able to monitor locking and resolve lock conflicts. Identify the reasons for database lock conflicts, and explain how to resolve them. Show an example of a more serious type of lock conflict, a deadlock.

List the features supported by undo data in an undo tablespace. Enumerate the four primary uses for undo data: rollback, read consistency, database recovery, and flashback operations. Show how the rollback requirements for users that perform long transactions can interfere with read consistency required for query users. Be able to identify and use the method to preserve expired undo at the expense of transactions.

Summarize the steps for monitoring, configuring, and administering the undo tablespace. Set the initialization parameters required to use an undo tablespace. Be able to review the status of the undo tablespace using EM Database Control, and use the Undo Advisor to resize the undo tablespace when conditions warrant it. Alter the initialization parameter UNDO_RETENTION to configure how long undo information needs to be retained for long-running queries.

List the types of lock modes available when locking a table. Identify the locks available, from least restrictive to most restrictive. Be able to request a lock with either a LOCK or SELECT statement and return immediately if the lock is not available.