Managing undo in Oracle 10g
After completing this topic, you should be able to calculate undo tablespace sizing and resize an undo tablespace.
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.
|1. Click the Undo Management link|
|2. Click the Undo Advisor button|
| 3. Type |
|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.
|1. Click the Tablespaces link|
|2. Select the UNDOTBS1 option|
|3. Click the Edit button|
|4. Click the Edit button|
| 5. Type |
|6. Click the Continue button|
|7. Click Apply|