In order to guarantee the success of queries even at the price of compromising the success of DML operations, you can enable retention guarantee. This option must be used with caution, because it can cause DML operations to fail if the undo tablespace is not big enough.
However, with proper settings, long-running queries can complete without risk of receiving the "snapshot too old" message, and you can guarantee a time window in which the execution of Flashback features will succeed.
Retention is specified in units of seconds. This parameter determines the low threshold value of undo retention. The system retains undo for at least the time specified in this parameter. The setting of this parameter should account for any flashback requirements of the system. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space.
This action can potentially cause some queries to fail with the "snapshot too old" message. Automatic tuning of undo retention is not supported for LOBs. Oracle Database 10 g lets you guarantee undo retention. When you enable this option, the database never overwrites unexpired undo data--that is, undo data whose age is less than the undo retention period. This option is disabled by default, which means that the database can overwrite the unexpired undo data in order to avoid failure of DML operations if there is not enough free space left in the undo tablespace.
By enabling the guarantee option, you instruct the database not to overwrite unexpired undo data even if it means risking failure of currently active DML operations. Therefore, use caution when using this feature. A typical use of the guarantee option is when you want to ensure deterministic and predictable behavior of Flashback Query by guaranteeing the availability of the required undo data.
You can size the undo tablespace appropriately either by using automatic extension of the undo tablespace or by manually estimating the space you will need for undo. This section discusses both methods. 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 for datafiles of the undo tablespace so that they automatically increase in size when more space is needed. By combining automatic extension of the undo tablespace with automatically tuned undo retention, you can ensure that long-running queries will succeed by guaranteeing the undo required for such queries.
If you have decided on a fixed-size undo tablespace, the Undo Advisor can help you estimate needed capacity, and you can then calculate the amount of retention your system will need. Enterprise Manager is the preferred method of accessing the advisor. An adjustment to the collection interval and retention period for AWR statistics can affect the precision and the type of recommendations the advisor produces.
Please refer to "Automatic Workload Repository" for additional information. Oracle Database provides an Undo Advisor that provides advice on and helps automate the establishment of your undo environment. You 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'. Once 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 "Segment Advisor" for an example of creating an advisor task for a different advisor. You can calculate space requirements manually using the following formula:. This value should take into consideration long-running queries and any flashback requirements.
In the steady state, you can query the view to obtain the transaction rate. The overhead figure can also be obtained from the view. 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. 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 its status is ACTIVE, this means that undo is being used in an active process and will not be truncated in any way. It can be truncated if required. This means that this is the first undo information to be truncated. You can use the following query to control user-based undo usage.
If you have decided on a fixed-size undo tablespace, the Undo Advisor can help us estimate needed capacity, and you can then calculate the amount of retention your system will need. An adjustment to the collection interval and retention period for AWR statistics can affect the precision and the type of recommendations the advisor produces.
Undo Advisor. Oracle Database provides an Undo Advisor that provides advice on and helps automate the establishment of your undo environment. You 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'. Calculating space requirements for Undo tablespace. You can calculate space requirements manually using the following formula:. In the steady state, you can query the view to obtain the transaction rate. The overhead figure can also be obtained from the view. Managing Undo Tablespaces.
Creating Undo Tablespace. 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. You can create more than one undo tablespace, but only one of them can be active at any one time.
Altering Undo Tablespace. However, since most aspects of undo tablespaces are system managed, you need only be concerned with the following actions:. This means that some undo information stills stored above the datafile size we want to set.
Another way to set undo tablespace to the size that we want is, to create another undo tablespace, set it the default one, take offline the old and then just drop the big old tablespace.
These are also the only attributes you are permitted to alter. 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.
Dropping Undo Tablespace. An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions e.
All contents of the undo tablespace are removed. Switching Undo Tablespaces. You can switch from using one undo tablespace to another. If any of the following conditions exist for the tablespace being switched to, an error is reported and no switching occurs:. The tablespace does not exist The tablespace is not an undo tablespace The tablespace is already being used by another instance in RAC environment.
The database is online while the switch operation is performed, and user transactions can be executed while this command is being executed.
0コメント