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. 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 redo is terminated. No other members of the consumer group can perform further updates until undo space is freed from the pool.
This section lists views that are useful for viewing information about undo space in the automatic undo management mode and provides some examples. In addition to views listed here, you can obtain information from the views available for viewing tablespace and datafile information. Please refer to " Viewing Datafile Information" for information on getting information about those views. Oracle Database also provides proactive help in managing tablespace disk space use by alerting you when tablespaces run low on available space.
Please refer to "Managing Space in Tablespaces " for information on how to set alert thresholds for the undo tablespace. To prevent excessive alerts, the long query alert is issued at most once every 24 hours.
When the alert is generated, you can check the Undo Advisor Page of Enterprise Manager to get more information about the undo tablespace. The following dynamic performance views are useful for obtaining space information about the undo tablespace:. Statistics are available for undo space consumption, transaction concurrency, the tuning of undo retention, and the length and SQL ID of long-running queries in the instance.
Each row in the view contains statistics collected in the instance for a ten-minute interval. Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the partial current time period. The view contains a total of rows, spanning a 7 day cycle. Your Oracle Database includes several features that are based upon undo information and that allow administrators and users to access database information from a previous point in time.
These features are part of the overall flashback strategy incorporated into the database and include:. The retention period for undo information is an important factor for the successful execution of Flashback features. It determines how far back in time a database version can be established. Specifically, you must choose an undo retention interval that is long enough to enable users to construct a snapshot of the database for the oldest version of the database that they are interested in.
Using Oracle Flashback Query feature, users or applications can execute queries as of a previous time in the database. Application developers can use Flashback Query to design an application that allows users to correct their mistakes with minimal DBA intervention. You, as the DBA, need only configure the undo tablespace with an appropriate size and undo retention period. No further action on your part should be required.
The Flashback Version Query feature enables users to query the history of a given row. Otherwise, not all rows can be retrieved. One use for this view could be if a user finds, by using the Flashback Transaction Query feature, that a row value has been changed inappropriately. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. If you are still using rollback segments to manage undo space, Oracle strongly recommends that you migrate your database to automatic undo management.
Oracle Database provides a function that provides information on how to size your new undo tablespace based on the configuration and usage of the rollback segments in your system.
DBA privileges are required to execute this function:. 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.
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 RAC environment.
The following example unassigns the current undo tablespace:. Establishing User Quotas for Undo Space. 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 it's undo limit, the current UPDATE transaction generating the redo is terminated. No other members of the consumer group can perform further updates until undo space is freed from the pool.
Monitoring Undo Tablespaces. Oracle Database also provides proactive help in managing tablespace disk space use by alerting you when tablespaces run low on available space. To prevent excessive alerts, the long query alert is issued at most once every 24 hours. When the alert is generated, you can check the Undo Advisor Page of Enterprise Manager to get more information about the undo tablespace.
The following dynamic performance views are useful for obtaining space information about the undo tablespace:. View Description. To findout the undo segments in the database. Statistics are available for undo space consumption, transaction concurrency, the tuning of undo retention, and the length and SQL ID of long-running queries in the instance.
Each row in the view contains statistics collected in the instance for a 10minute interval. Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the partial current time period. The view contains a total of rows, spanning a 7 day cycle. Flashback Features. Traditionally transaction undo information was stored in Rollback Segments until a commit or rollback statement was issued, at which point it was purged.
In Oracle 9i this method of manual undo management is still available in addition to a new automatic method which frees DBAs from routine undo management tasks and tuning.
Log into your account. Forgot your password? Privacy Policy. Password recovery. Recover your password. Friday, November 12,
0コメント