Managing undo in Oracle 10g

Learning objective

After completing this topic, you should be able to calculate undo tablespace sizing and resize an undo tablespace.

Exercise overview

In this exercise, you're required to generate an estimate of the undo tablespace size you need, and then to resize the undo tablespace datafile accordingly.

This involves the following tasks:

• generating an estimate of the required undo tablespace size
• changing the undo tablespace size

A new version of your application will include several reports based on very long-running queries. You need to configure your system to support these reports by setting a suitable undo retention time and then resizing the undo tablespace accordingly.

Task 1: Determining tablespace size

You have logged onto Enterprise Manager and opened the Administration tabbed page. Use the Undo Advisor to calculate the amount of undo space required to support an undo retention time of 1500 minutes on the basis of the last seven days activity on your system.

Steps list

Instructions
1. Click the Undo Management link
2. Click the Undo Advisor button
3. Type 1500 in the first New Undo Retention field
4. Click the Update Analysis and Graph button

Task 2: Resize the undo tablespace

The Undo Advisor has calculated an undo tablespace size of at least 205 MB to support an undo retention time of 1500 minutes. On this basis, resize the datafile in the undo tablespace to 210 MB. You have already opened the Administration tabbed page in Enterprise Manager.

Steps list

Instructions
1. Click the Tablespaces link
2. Select the UNDOTBS1 option
3. Click the Edit button
4. Click the Edit button
5. Type 210 in the first File Size field
6. Click the Continue button
7. Click Apply