第11章 Managing Undo Data(管理撤销数据)

certification objectives

• Explain the Purpose of Undo
• Understand How Transactions Generate Undo
• Manage Undo

认证目标：

• 解释撤销的目的
• 理解事务如何产生的撤销
• 管理撤销

Chapter 10 described what happens in memory,and on disk,when you execute INSERT,UPDATE,or DELETE statements-the manner in which changed data is written to blocks of table and index segments,and the old version of the data is written out to blocks of an undo segment.It also covered the theory behind this,summarized as the ACID test,which every relational database must pass.In this chapter you will see the practicalities of how undo data is managed.

第10章描述了内存和硬盘中发生了什么，当你执行INSERT,UPDATE或DELETE语句-这种更改数据的举动会写入表和索引段的块，上一版本的数据会写入一个撤销段。本章也涵盖了这背后的理论，就如同ACID测试（任何关系数据库必须通过）总结的那样。本章你将会看到撤销数据如何被管理的实际例子。

Explain the Purpose of Undo

Undo data is the information needed to reverse the effects of DML statements.It is often refered to as rollback data,but try tio avoid that term.In earlier releases of Oracle.the terms rollback data  and undo data were used interchangeably,but from 9i onward they are different:their function is the same,but their management is not.Whenever a transaction changes data,the preupdate version of the data is written out to a rollback segment or to an undo segment.The difference is crucial.Rollback segments can still exist in an 11g database,but with release 9i of the database Oracle introduced the undo segment as an alternative,Oracle strongly advises that all databases should use undo segments-rollback segmetns are retained for backward compatibility,but they are not referenced in the OCP exam and are therefore not covered in this book.But even though "rollback" as as noun should no longer be used in the Oracle environment "rollback" as a verb is as relevant as ever.

撤销数据是反转DML结果的必要信息。它常常被当做回滚数据，但是尽量避免如此理解这个术语。在Oracle的早期版本中，回滚数据和撤销数据这2个术语是可以相互交换的。但是从9i开始它们就不同了，正准备更新版本的数据写入回滚段或撤销段。这个不同很重要。回滚段在11g中仍然存在，但根据9i版本的介绍，撤销段只是可供选择的，Oracle强烈建议所有的数据库都应该使用撤销段-为了向后兼容回滚段也将继续保留。但是它们在OCP考试中并没有提及，因此本书中没有介绍。尽管rollback作为一个名词将不会再Oracle环境中使用，但是rollback作为一个动词仍然像以前一样重要。

 

To rollback a transaction means to use data from the undo segments to construct an image of the data as it was before the transaction occurred.This is usually done automatically to satisfy the requirements of the ACID test,but the flashback query capacity(introduced with 9i and greatly enhanced since) leverages the power of the undo mechanism by giving you the option of querying the database as it was at some time in the past.And of course ,any user can use the ROLLBACK command interactively to back out any DML statements that he/she has issued and not committed.

回滚一个事务意味着使用回滚段的数据构建数据在事务发生前的影像。为了满足ACID测试需求，通常都是自动完成的，但是闪回查询能力（在9i中有介绍且极大地增强）影像撤销原理（通过给你提供可选的在过去的某个时间存在的数据库查询）的能力。当然，任何用户都能使用rollback命令交互式地撤销任何他/她已经发布但没有提交的DML语句。

The ACID test requires,that the database should keep preupdate versions of data in order that incomplete transactions can be reversed-either automatically in the case of an error or on demand through the use of the rollback command.This type of rollback is premanent and published to all users.Second,for consistency,the database must be able to present a query with a version of the database as it was at the time the query started.The server process running the query will go to the undo segments and construct what is called a read-consistent image of the bolcks being queried,if they were changed after the query started.This  type of rollback is temporary and only visible to the session running the query.Third,undo segments are also used for transaction isolation.This is perhaps the most complex use of undo data.The principle of isolation requires that no transaction can be in any way dependent upon another,incomplete,transaction.In effect,even though a multiuser database will have many transactions in progress at once,the end result must be as though the transactions were executing one after another.The use of undo data combined with row and table locks(as described in Chapter 10) guarantees transaction isolation:the impossibility of incompatible transactions.Even though sereval transactions may be running concurrently,isolation requires that the end result must be as if the transactions where serialized.

根据ACID的需要，数据库必须保有准备提交的数据版本，目的是未完成的事务可以反转-在出现错误的情况下自动反转或通过使用rollback命令。这种类型的rollback是永久的，并发布给所有的用户。第二，为了一致性，数据库必须有能力展现一个查询开始时那个版本的数据库的查询。服务进程运行这个查询，如果他们（块）在查询开始后改变了，将会去撤销段建立一个名为“读一致性”的正在查询的块的影像。这种类型的rollback是临时的，仅仅是为了看到查询的会话。第三，撤销段也被用于事务的隔离性。这可能是撤销数据最复杂的应用。隔离性原则是没有事务在任何形式上依赖于另一个未完成的事务。实际上，及时一个多用户数据库一次存在许多正在进行的事务，最后的结果仍然是这些事务一个接一个地执行。带行和表锁（在第10章中有介绍）的撤销数据被用来保证事务的隔离性：不可能存在不相容的事务。即使几个事务同时在运行，隔离性要求最后的结果必须是事务之间是有序的。

 

From release 9i onward,undo data can also be used for flashback queries.This is a completely optional but very powerful tool that allows users to query a past image of the database.For flahsback queries,undo data is used to construct a version of one or more tables as they were at some previous time by applying undo data.As with rollback for the purposes of consistency,rollback for flashback purposes is only temporary,and only visible to the session concerned.

从9i版本开始，撤销数据也可以在flashback查询中使用。这是一个完全可选择但功能强大的工具，可以让用户查询数据库过去的影像。对于flashback查询，撤销数据被用来创建通过应用撤销数据存在于过去某个时间的一个或多个表的版本。由于rollback一致性的目的，flashback的回滚的目的仅仅是临时的，仅能在涉及到的会话中看得到。

 

As a final word  on rollback as opposed to undo,observe the results of two queries against DBA_SEGMENTS.This shows that within the database there is one segment of type ROLLBACK,and 260 segments of type TYPE2 UNDO.So rollback segments di still exist in 11g database.Undo segments can only exist in an undo tablespace;this is one of their features.But at database creation time,there may not be an undo tablespace.Therefore,at dataabse creation time,Oracle creates a single old-fashioned rollback segment in the SYSTEM tablespace,along with the data directory.This is used during database creation but is never used in normal running.All user transactions will use undo segments,listed in DBA_SEGMENTS as segment type TYPE2 UNDO.

rollback作为一个与撤销对立的词汇，出现在在DBA_SEGMENTS表上的2个查询的结果。显示在数据库中有一个ROLLBACK类型的段，和260个类型为TYPE2 UNDO的段。所有rollback段在11g数据库中也是存在的。撤销段只能存在于撤销表空间内，这是它们的一个特性。但是在数据库创建的时候，可能没有一个撤销空间。因此，在数据库创建的时候，Oracle在SYSTEM空间内连同数据字典创建一个single old-fashioned rollback段。这只在数据库创建的时候使用，而不会内在常规运行中使用。所有的事务都会使用undo segments,在DBA_SEGMENTS表中以TYPE2 UNDO类型列出。

Use of undo segments is incompatible with use of rollback segments:it is one or the other,depending on the setting of the UNDO_MANAGEMENT parameter.

撤销段的使用与回滚段的使用不兼容：非此即彼，决定于参数UNDO_MANAGEMENT的设置。

 

If you ask Oracle Porduct Development "What is a TYPE1 UNDO segment?" the reply will be,"That's a ROLLBACK segment."

如果你要问Oracle产品开发“什么是TYPE1 UNDO段 ？”回答是“那是一个ROLLBACK段”。

 

select d.segment_type,count(1) from dba_segments d group by segment_type;

 

 

 

select segment_name,tablespace_name,segment_type
from dba_segments 
where segment_type in ('ROLLBACK','TYPE2 UNDO')
order by segment_name;

 

 

 

Exercise:Use Undo Data

 

In this exerise,you will investigate(研究) the undo configuration and usage(用法) in your database.Use either SQL*Plus or SQL Developer.

1 Connect to the database as user SYSTEM.

使用SYSTEM用户连接数据库。

2 Determine(确认) whether the database is using undo segments or rollback segments with this query:

使用这个查询确认数据库是否使用了撤销段或回滚段：

 

select value from v$parameter where name='undo_management';

 

This should return the value AUTO.If it does not,issue this command,and then restart the instance:

结果返回值为AUTO。如果不是，执行下面的命令，并重启实例。

 

alter system set undo_mangement=auto scope = spfile

3 Determine what undo tablespaces have been created,and which one is being used with these two queries:

使用下面2个查询确认创建的撤销表空间有哪些，以及哪一个正在被使用：

 

select tablespace_name from dba_tablespaces where contents='UNDO';

select value from v$parameter where name='undo_tablespace';

 

4 Determine what undo segments are in use in the database,and how big they are:

确认数据库中使用到的撤销段是什么，以及它们的大小：

 

select d.segment_name,d.tablespace_name,r.rssize,r.usn 
from dba_rollback_segs d,v$rollstat r where d.segment_id=r.usn;

5 Find out how much undo data was being generated in your database in the recent past:

找出有最近你的数据库中产生了多少撤销数据：

 

select begin_time,end_time,(undoblks * 
(select value from v$parameter where name='db_block_size')/1000)||'k' 
undo_bytes from v$undostat;

 

 

Understand How Transaction Generate Undo

When a transaction starts,Oracle will assign it to one(and only one) undo segment.Any one transaction can only be protected by one undo segment-it is not possible for the undo data generated by one transaction to cut across multiple undo segments.This is not a problem,because undo segments are not of a fixed size.So if a transaction does manage to fill its undo segment,Oracle will automatically add another extent to the segment,so that the transaction can continue.It is possible for multiple transactions to share one undo segment,but in normal running this should not occur.

 

当一个事务开始时，Oracle将会为其分配一个（只能是一个）撤销段。任何一个事务只能被一个撤销段保护——一个事务不可能在多个撤销段之间产生撤销数据。这不是一个问题，因为撤销段没有固定的大小。所以如果一个事物要填充它的撤销段，Oracle会自动为这个段增加另一个扩展，这样事务才能继续。多个事务有可能共享一个撤销段，但是实际上这种情况不会发生。

 

No transaction can ever span multiple undo segments,but one undo segment can support multiple transactions.

没有事务能跨越多个撤销段，但一个撤销段能支持多个事务。

 

Undo segments are not the same as rollback segments,but the views DBA_ROLLBACK_SEGS and V$ROLLSTAT include rows for both without any flag to say which type of segment they are.The naming convention distinguishes them:undo segment names are automatically generated and prefixed with _SYSSMU.

撤销段和回滚段不相同，但视图DBA_ROLLBACK_SEGS和V$ROLLBACK中包含这两种段的记录并没有任何标识区分它们的类型。可以从命名规则上区分它们：撤销段的名称会自动产生并以_SYSSMU为前缀。