就在前几天，又有一个客户向我咨询undo表空间使用率的问题。这让我想起几年前曾经有个省份的案例，客户的实际运维人员是一位刚毕业不久的女孩，几乎不懂Oracle原理，项目经理交给她的任务也是基础运维工作，比如其中一项就是监测数据库各个表空间的使用率，并对使用率超过95%的表空间进行扩展，他们的Oracle版本是10gR2。
由于该客户业务是运营商话单相关的，业务数据量很大（几十T的规模），所以预留存储的空间也很充足。
有一次该客户有其他问题找到我远程处理的时候，我惊奇的发现他们的undo表空间居然有2个多T大小。进而询问运维人员是怎么回事，想必结果大家已经猜到了，这女孩说她日常巡检经常发现undo表空间使用率超过95%，所以她就不停地扩展，直到如今已经加到2个多T规模的大小。她甚至认为undo表空间也是某一个业务的表空间，这就尴尬了。
那么，究竟什么是undo？undo都有哪些实际作用呢？Oracle 10g的官方文档是这样描述的：
What Is Undo?
Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.
Undo records are used to:
Roll back transactions when a ROLLBACK statement is issued
Recover the database
Provide read consistency
Analyze data as of an earlier point in time by using Oracle Flashback Query
Recover from logical corruptions using Oracle Flashback features
When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.
具体来看下我10.2.0.5实验环境下undo相关参数的默认设置：
SQL> show parameter undo NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1可以看到undo_management默认设置为AUTO，关于这个值，官档这样描述：
Automatic undo management uses an undo tablespace.To enable automatic undo management, set the UNDO_MANAGEMENT initialization parameter to AUTO in your initialization parameter file. In this mode, undo data is stored in an undo tablespace and is managed by Oracle Database.
而对于undo_retention这个值，默认是900，单位是s，也就是15分钟。很多实际的环境，也会考虑将其设置的大一些，比如10800,即3小时。
来看下官档对于undo retention和与之相关的retention guarantee的具体描述：
Undo Retention
After a transaction is committed, undo data is no longer needed for rollback or transaction recovery purposes. However, for consistent read purposes, long-running queries may require this old undo information for producing older images of data blocks. Furthermore, the success of several Oracle Flashback features can also depend upon the availability of older undo information. For these reasons, it is desirable to retain the old undo information for as long as possible.
When automatic undo management is enabled, there is always a current undo retention period, which is the minimum amount of time that Oracle Database attempts to retain old undo information before overwriting it. Old (committed) undo information that is older than the current undo retention period is said to be expired. Old undo information with an age that is less than the current undo retention period is said to be unexpired.
Oracle Database automatically tunes the undo retention period based on undo tablespace size and system activity. You can specify a minimum undo retention period (in seconds) by setting the UNDO_RETENTION initialization parameter. The database makes its best effort to honor the specified minimum undo retention period, provided that the undo tablespace has space available for new transactions. When available space for new transactions becomes short, the database begins to overwrite expired undo. If the undo tablespace has no space for new transactions after all expired undo is overwritten, the database may begin overwriting unexpired undo information. If any of this overwritten undo information is required for consistent read in a current long-running query, the query could fail with the snapshot too old error message.
The following points explain the exact impact of the UNDO_RETENTION parameter on undo retention:
The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database may overwrite unexpired undo information when tablespace space becomes low.