Oracle 10g undo tablespace Management

Source: Internet
Author: User

I. Oracle 9i has two undo management methods:
AUM Automatic Undo Management
MUN Manual Undo Management
We recommend that you use AUM.
Undo segment is also called rollback segment.

Ii. Three major functions of undo:
1. provide Consistent Read
2. roll back the Transaction Rollback Transaction
3. Restore Instance Recovery

3. Configure AUM
1. Set the initialization parameter undo_management.
Auto AUM manage undo
Manual MUN manage undo
SQL> show parameter undo_management
2. undo segment will expand and contract. SMON is completed, and will be reduced once every 12 hours.
3. The unit of the parameter undo_retention is seconds, indicating how long the data in the undo block used by the firm needs to be retained when the transaction is committed or rolled back.
4. When AUM is used and undo_retention is set, the undo block has four statuses.
The Actice undo transaction has not been committed.
There are no active transactions on Inactive undo and can be overwritten by other transactions.
The duration of Expried undo exceeds the time specified by undo_retention.
The Freed undo content is empty and has never been used.
4. The default value of undo_retention is 900 seconds.
SQL> alter tablespace undonew add datafile 'u01/app/oracle/oradata/ora10g/undonew02.dbf' size 10 M autoextend on;

4. Switch undo_tablespace
1. The database can have multiple undo tablespaces, but only one can be used at a time.
2. Set the undo_tablespace parameter to the name of another undo tablespace, which is called the undo tablespace switch.
3. During the switchover, some transactions are not completed. After the switchover, the old undo tablespace is in pending offline status and cannot be deleted. After the transaction is committed, it can be deleted only when it changes to offline status.
4. If the undo tablespace cannot be expanded, the undo data with a retention time less than undo_retention will be overwritten if it is not enough. However, you can set the parameter retention to directly report an error in this case.
SQL> alter tablespace undonew add datafile 'u01/app/oracle/oradata/ora10g/undonew02.dbf 'size 10 M autoextend on maxsize 100 M retention guarantee;
SQL> alter tablespace undonew retention guarantee;
SQL> alter tablespace undonew retention noguarantee;

5. Calculate the undo tablespace size
1. Use view v $ undostat
Oracle updates the view every 10 minutes and records the number of undo records generated in 10 minutes in this view. The data is retained for 7 days, with a total of 1008 rows of records.
2. formula:
Undospace = undo retention * undo per second * overhead (24 undo blocks)

Where: undo retention parameter undo_retention
Undo per second is calculated by querying v $ undostat.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.