We also see these reasons for UNDO:. You can also run this query to see UNDO usage:. Oracle Training from Don Burleson The best on site " Oracle training classes " are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop! Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum.
If the undo tablespace is fixed size, the database tunes the retention period for the best possible undo retention for that tablespace size and the current system load. This tuned retention period can be significantly greater than the specified minimum retention period. Again, this tuned retention period can be greater than the specified minimum retention period.
This view contains one row for each minute statistics collection interval over the last 4 days. Undo Retention Tuning and Alert Thresholds For a fixed size undo tablespace, the database calculates the maximum undo retention period based on database statistics and on the size of the undo tablespace.
For more information on tablespace alert thresholds, see "Managing Tablespace Alerts". This parameter specifies the desired minimum undo retention period in seconds. You can size the undo tablespace appropriately either by using automatic extension of the undo tablespace or by using the Undo Advisor for a fixed sized tablespace.
Oracle Database supports automatic extension of the undo tablespace to facilitate capacity planning of the undo tablespace in the production environment. When the system is first running in the production environment, you may be unsure of the space requirements of the undo tablespace. In this case, you can enable automatic extension of the undo tablespace so that it automatically increases in size when more space is needed.
If you have decided on a fixed-size undo tablespace, the Undo Advisor can help you estimate needed capacity. Enterprise Manager is the preferred method of accessing the advisor. It is therefore important that the AWR have adequate workload statistics available so that the Undo Advisor can make accurate recommendations. For newly created databases, adequate statistics may not be available immediately. In such cases, an auto-extensible undo tablespace can be used. An adjustment to the collection interval and retention period for AWR statistics can affect the precision and the type of recommendations that the advisor produces.
See "Automatic Workload Repository" for more information. After the database has been up for a while, you can view the Longest Running Query field on the Undo Management page of Enterprise Manager. For example, if you expect to run Flashback Queries for up to 48 hours in the past, your flashback requirement is 48 hours.
You then take the maximum of these two undo retention values and use that value to look up the required undo tablespace size on the Undo Advisor graph. You can activate the Undo Advisor by creating an undo advisor task through the advisor framework. The following example creates an undo advisor task to evaluate the undo tablespace. The name of the advisor is 'Undo Advisor'.
After you have created the advisor task, you can view the output and recommendations in the Automatic Database Diagnostic Monitor in Enterprise Manager. Oracle Database 2 Day DBA for more information on using advisors and "Using the Segment Advisor" for an example of creating an advisor task for a different advisor.
This section describes the various steps involved in undo tablespace management and contains the following sections:. There are two methods of creating an undo tablespace. The second method is used with an existing database. You cannot create database objects in an undo tablespace. It is reserved for system-managed undo data. Oracle Database enables you to create a single-file undo tablespace.
Single-file, or bigfile, tablespaces are discussed in "Bigfile Tablespaces". You can create more than one undo tablespace, but only one of them can be active at any one time. The create undo statement is slightly different than the others which we have done so far. For the simplicity of the demonstration here I have added only one data file. In the third line we have auto extend clause. Using this clause we can set the maximum size to which the data file of undo tablespace can extend or say grow.
You can also limit the max size of your data file by simply writing the size. In the 5th and last line we have a Retention clause which is right now set on No Guarantee. The second method is used with an existing database. You cannot create database objects in an undo tablespace. It is reserved for system-managed undo data. Oracle Database enables you to create a single-file undo tablespace.
Single-file, or bigfile, tablespaces are discussed in "Bigfile Tablespaces". You can create more than one undo tablespace, but only one of them can be active at any one time. However, since most aspects of undo tablespaces are system managed, you need only be concerned with the following actions:. If an undo tablespace runs out of space, or you want to prevent it from doing so, you can add more files to it or resize existing datafiles.
An undo tablespace can only be dropped if it is not currently used by any instance. All contents of the undo tablespace are removed.
0コメント