When a query tries to modify database, original data entries are kept in separate location and then changes are applied to data files. The data which is kept in separate location is called Undo data. Undo data is used to maintain data integrity and data consistency. Oracle uses Undo data in following situations
1-After query execution, user may commit or rollback transaction. If user issues a rollback, original data is copied from Undo data entries.
2-Suppose a user execute query which take 20 minutes to execute. Other users may change the data during these 20 minutes. This may cause read inconsistency. To insure read consistency, oracle database uses original data from undo data if data is changed during the execution of a query.
3-Flashback features including Oracle Flashback Query, Oracle Flashback version, Flashback transaction query and Oracle Flashback Table make use of undo data.
Creating an Undo Table space
Although Database Configuration Assistant (DBCA) automatically creates an undo table space for new installations of Oracle Database Release 11g, there may be occasions when you want to manually create an undo table space.
There are two methods of creating an undo table space. The first method creates the undo table space when the CREATE DATABASE statement is issued. This occurs when you are creating a new database, and the instance is started in automatic undo management mode (UNDO_MANAGEMENT = AUTO). The second method is used with an existing database. It uses the CREATE UNDO TABLE SPACE statement.
You cannot create database objects in an undo table space. It is reserved for system-managed undo data.
Using CREATE DATABASE to Create an Undo Table space
You can create a specific undo table space using the UNDO TABLE SPACE clause of the CREATE DATABASE statement.
The following statement illustrates using the UNDO TABLE SPACE clause in a CREATE DATABASE statement. The undo table space is named un dotbs_01 and one data file, /u01/oracle/rbdb1/undo0101.dbf, is allocated for it.
CREATE DATABASE rbdb1
CONTROL FILE REUSE
UNDO TABLE SPACE undotbs_01 DATA FILE ‘/u01/oracle/rbdb1/undo0101.dbf’;
If the undo tablespace cannot be created successfully during CREATE DATABASE, the entire CREATE DATABASE operation fails. You must clean up the database files, correct the error and retry the CREATE DATABASE operation.
The CREATE DATABASE statement also lets you create a single-file undo table space at database creation.
Space management for rollback segments is complex. Oracle strongly recommends leaving the database in automatic undo management mode.
Using the CREATE UNDO TABLE SPACE Statement
The CREATE UNDO TABLE SPACE statement is the same as the CREATE TABLE SPACE statement, but the UNDO keyword is specified. The database determines most of the attributes of the undo table space, but you can specify the DATA FILE clause.
This example creates the undotbs_02 undo table space with the AUTO EXTEND option:
CREATE UNDO TABLE SPACE undotbs_02
DATAFILE ‘/u01/oracle/rbdb1/undo0201.dbf’ SIZE 2M REUSE AUTO EXTEND ON;
You can create more than one undo table space, but only one of them can be active at any one time.
Altering an Undo Table space
Undo table spaces are altered using the ALTER TABLE SPACE statement. However, since most aspects of undo table spaces are system managed, you need only be concerned with the following actions:
- Adding a data file
- Renaming a data file
- Bringing a data file online or taking it offline
- Beginning or ending an open backup on a data file
- Enabling and disabling undo retention guarantee
These are also the only attributes you are permitted to alter.
If an undo table space runs out of space, or you want to prevent it from doing so, you can add more files to it or resize existing data files.
The following example adds another data file to undo table space un dotbs_01:
ALTER TABLE SPACE undotbs_01
ADD DATAFILE ‘/u01/oracle/rbdb1/undo0102.dbf’ AUTO EXTEND ON NEXT 1M
MAX SIZE UNLIMITED;
You can use the ALTER DATABASE…DATA FILE statement to resize or extend a datafile.
Note: Undo table space is introduced from oracle 9i on wards. Before oracle 9i rollback table space is used.
Dropping an Undo Table space
Use the DROP TABLE SPACE statement to drop an undo table space. The following example drops the undo table space undotbs_01:DROP TABLE SPACE undotbs_01;
An undo table space can only be dropped if it is not currently used by any instance. If the undo table space contains any outstanding transactions (for example, a transaction died but has not yet been recovered), the DROP TABLE SPACE statement fails. However, since DROP TABLE SPACE drops an undo table space even if it contains unexpired undo information (within retention period), you must be careful not to drop an undo table space if undo information is needed by some existing queries.
DROP TABLE SPACE for undo table spaces behaves like DROP TABLE SPACE…INCLUDING CONTENTS. All contents of the undo table space are removed.
Switching Undo Table spaces
You can switch from using one undo table space to another. Because the UNDO_TABLE SPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo table space.
The following statement switches to a new undo table space:
ALTER SYSTEM SET UNDO_TABLE SPACE = undotbs_02;
Assuming undotbs_01 is the current undo tablespace, after this command successfully executes, the instance uses undotbs_02in place of undotbs_01 as its undo tablespace.
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 a RAC environment only)
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. If there are any pending transactions in the old undo tablespace, the old undo tablespace enters into a PENDING OFFLINE mode (status). In this mode, existing transactions can continue to execute, but undo records for new user transactions cannot be stored in this undo tablespace.
An undo table space can exist in this PENDING OFFLINE mode, even after the switch operation completes successfully. A PENDING OFFLINE undo table space cannot be used by another instance, nor can it be dropped. Eventually, after all active transactions have committed, the undo table space automatically goes from the PENDING OFFLINE mode to the OFFLINE mode. From then on, the undo table space is available for other instances (in an Oracle Real Application Cluster environment).
If the parameter value for UNDO TABLE SPACE is set to ” (two single quotes), then the current undo table space is switched out and the next available undo table space is switched in. Use this statement with care because there may be no undo table space available.
The following example un assigns the current undo table space:
ALTER SYSTEM SET UNDO_TABLE SPACE = ”;
Establishing User Quotas for Undo Space
The Oracle Database Resource Manager can be used to establish user quotas for undo space. The Database Resource Manager directive UNDO_POOL allows DBAs to limit the amount of undo space consumed by a group of users (resource consumer group).
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. No other members of the consumer group can perform further updates until undo space is freed from the pool.
When no UNDO_POOL directive is explicitly defined, users are allowed unlimited undo space.
Managing Space Threshold Alerts for the Undo Table space
Oracle Database also provides proactive help in managing table space disk space use by alerting you when table spaces run low on available space.
In addition to the proactive undo space alerts, Oracle Database also provides alerts if your system has long-running queries that cause SNAPSHOT TOO OLD errors. 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 table space.
Undo Management and Undo Retention Period:
Oracle supports two types of Undo management, Manual Undo Management and Auto Undo Management. In manual management, undo data is kept in rollback segments. When new database is created, oracle creates one rollback segments is system table space. Additional rollback segments can be created on requirement. Memory management for rollback segments is really complex. By default database is configured to use Manual Undo Management but it is not recommended by oracle.
Oracle database 9i and latest releases including 10g provide auto management of undo data. In auto management, undo data is stored in undo table space. Oracle creates one undo table space when a new database is created. Undo table space can also be created using following SQL statement.
CREATE UNDO TABLESPACE undotbs_02
DATAFILE ‘/u01/oracle/data/undo0201.dbf’ SIZE 100M REUSE;
Use UNDO_MANAGEMENT initialization parameter to enable auto undo management. Set the value of this initialization parameter to Auto to start undo management as shown below
When oracle instance start, it select first undo table space to keep undo data. UNDO_TABLE SPACE initialization parameter can be used to utilize a specific undo table space if you have more than one undo table spaces.
Note: By default undo table spaces are available with no guarantee option. This option is only applicable for undo table spaces.
The size of undo table space can be fixed or auto extend. Even with outo extend option enabled, maximum size of undo table space can be controlled by MAX SIZE clause. If undo table space has fixed size or MAX SIZE defined then old undo entries can be overwritten by new entries. The size of undo table space should be large enough to insure the undo data is available when required. This is done by setting Undo Retention period.
Oracle database tries to retain undo data for time given by undo Retention period. Oracle database tries best to ensure this retention period. To insure read consistency, Undo retention period should be at least equal to time taken by your longest running query. If query is running and oracle finds that the data required by query is overwritten in undo tablespace, query fails with snapshot too old error message. If an SQL statement has not been committed or rolled back, its undo entries are said to be Active.
Oracle ensures that these entries retain until corresponding entries are committed. Undo entries are marked expired which are old than current retention period. Undo retention period can be set by using UNDO_RETENTION initialization parameter. Value for this parameter can be set in parameter file as shows below
The value is in seconds
Use following statements to set Retention period at run time
ALTER SYSTEM SET UNDO_RETENTION = 3000;
If you are using Logical level flashback features, make sure that retention period is set according to requirements as flash back features use undo data to get past information.
By default retention period in not guaranteed, oracle database may overwrite undo data if space for new undo entries is not available. You can use RETENTION GUARANTEE clause while creating undo table space to guarantee retention period.
how to identify undotablespace
Sql>select * from v$table space;
How to identify undo segments and their status
Sql>select table space_name, SEGMENT_name, STATUS from dba_rollback_segs;
Note: undo table space, undo segments can be offline or online but system table space undo segments cannot be offline.
How to create another undo table space
Sql>Create table space undo_prd data file ‘/u01/oracle/data/undo prd.dbf’ size 10m;
Note: Onle one undo tablespace segments can be online at a time.
How to change the other undo table space segments online:
Sql>alter system set undo_table space =undo_prd;
How to set the undo retention time:
Sql>Alter system set undo_retention = 400;
How to identify guaranty and no guaranty option:
Sql>Select table space_name,retention from dba_table spaces;
How to set the undo table space with guaranty option:
Sql>Alter table space undo_prd retention GUARANTEE;
Sql> Alter table space undo_prd retention NO GUARANTEE;
How to identify pending offline Undo segments
Sql>show parameter undo
Sql>select usn, XACTS, STATUS from v$rollstats;
Open other window
siva@Sql>create table test(sno number);
siva@Sql>insert into test values(123);
Goto sys user:
Sql>select usn,xacts,status from v$rollstat;
Sql>alter system set undo_table space = undo_prd;
Go to siva user type commit then goto sys user check the transaction.
How to identify deffered rollback segments:
1 command@Sql>Select table space_name, segments_type from dba_segments where segment_type like”%DEF%”;
Sql>Alter table space user_prd offline;
Then apply the command. This command is works when table space is in offline mode.