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. 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.
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 database is online while the switch operation is performed, and user transactions can be executed while this command is being executed. When the switch operation completes successfully, all transactions started after the switch operation began are assigned to transaction tables in the new undo tablespace.
The switch operation does not wait for transactions in the old undo tablespace to commit. In this mode, existing transactions can continue to execute, but undo records for new user transactions cannot be stored in this undo tablespace.
From then on, the undo tablespace is available for other instances in an Oracle Real Application Cluster environment. Use this statement with care because there may be no undo tablespace available. The Oracle Database Resource Manager can be used to establish user quotas for undo space. You can specify an undo pool for each consumer group. An undo pool controls the amount of total undo that can be generated by a consumer group. When the total undo generated by a consumer group exceeds its undo limit, the current UPDATE transaction generating the undo is terminated.
But only one of them can be active and used by the system. The other one is currently not used. The following command will give you the total space used, and the free space still available in the undo tablespace. The above command will display all the tablespace available in your system. Just look for your undo tablespace from the list. The value displayed are in MB. Deleting an unused oracle UNDO tablespace is similar to dropping any other tablespace.
If you have multiple datafiles associated with an UNDO tablespace, delete those first. Next, drop those data files one by one using alter tablespace drop datafile command as shown below. Please note that you cannot drop the 1st datafile that was used while creating the tablespace.
This is because Oracle will use a different internal method to decide when to expire those blocks. Hi, can yo please update the second script? Your email address will not be published. Check Undo Tablespace Usage You can use the following query to check the undo tablespace usage. Author: dbtut We are a team with over 10 years of database management and BI experience. Share Facebook Twitter LinkedIn.
0コメント