undo tablespace

undo表空间用于存放undo数据，当执行DML操作(insert,update和delete)时，oracle会将这些操作的旧数据写入到undo段，在oracle9i之前，管理undo数据时使用的是undo segment，从oracle9i开始，我们不仅可以使用undo segment来管理undo数据，还可以使用undo tablespace。到了oracle10g已经完全弃用了undo segment了。
undo数据也叫做rollback数据，当我们执行DML操作时，事务操作前的数据就叫undo记录。undo段用于存放事务所修改数据的旧值，还存放了被修改的数据块的位置。
undo数据有以下几种作用：
(1)读一致性
用户在检索数据库中的数据时，oracle总是让用户只看到那些已经被提交的数据，这样可以确保读一致性。假定用户a正在执行一个DML操作，还未提交，此时用户b在检索用户a试图修改的数据，那么用户b检索到的就是还未修改之前的旧值，这个旧值就是在undo段中得到的。
(2)回退事务
当执行DML操作修改数据时，undo数据被存放在undo段，而新的数据是被存放在数据段中的，如果事务操作出现了问题，需要回退事务以去取消事务变化，这时rollback语句就可以取消事务变化。当执行rollback命令时，oracle会将undo段的undo数据写回到数据段中。
(3)事务恢复
事务恢复是例程恢复的一部分，它是由oracle server自动完成的，如果在数据库运行过程中出现例程失败(如断电，后台进程故障，内存故障等)，那么当重启oracle server时，后台进程SMON会自动执行例程恢复，执行例程恢复时，oracle会重新做所有未应用的记录，回退未提交事务。
(4)回闪查询flashback query
倒叙查询用于取得特定时间点的数据库数据，假定当前时间是上午11:00，某用户在上午10:00执行了一个update操作，修改并且提交了事务，为了取得10：00之前的未修改的数据，用户可以使用闪回查询特征。
下面我们看看和undo有关的初始化参数：
SYS@orcl 07-SEP-14>show parameter undo


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
undo_management 用于指定undo数据的管理方式： AUTO ,MANUAL 。如果是自动管理的话，oracle会使用undo tablespace来管理undo，如果使用手动管理的话，oracle会使用undo segment来管理undo数据。
undo_retention 用于控制undo数据的最大保留时间，其默认值是 900s ，通过设置这个参数，可以指定undo数据的保留时间，从而确定回闪查询特征可以查看到的最早时间点。
undo_tablespace 用于指定例程所要使用的undo表空间，使用自动undo管理模式时，通过配置这个参数可以指定例程所要使用的undo 表空间。

SYS@orcl 07-SEP-14>select tablespace_name,contents from dba_tablespaces;

TABLESPACE_NAME                CONTENTS
------------------------------ ---------
SYSTEM                         PERMANENT
SYSAUX                         PERMANENT
UNDOTBS1                       UNDO
TEMP                           TEMPORARY
USERS                          PERMANENT
EXAMPLE                        PERMANENT
TEST                           PERMANENT
HH                             PERMANENT
GOODS                          PERMANENT
DATA01                         PERMANENT

10 rows selected.

undo表空间是专门存放undo数据的，那么 在undo表空间上就不能建立任何数据对象，如表，索引，簇。

通常，我们要在数据库运行高峰阶段搜集undo表空间的统计信息，最终根据这些统计信息确定undo表空间的尺寸。
我们可以通过v$undostat来查看搜集undo的统计信息，oracle每隔10min就会生成一行统计信息：
SYS@orcl 07-SEP-14> select to_char(begin_time,'hh24:mi:ss') begin_time,
  2  to_char(end_time,'hh24:mi:ss') end_time,undoblks 
  3  from v$undostat;


BEGIN_TI END_TIME   UNDOBLKS
-------- -------- ----------
03:26:59 03:27:51         51
03:16:59 03:26:59        262

当使用自动undo管理模式时，oracle会在指定的undo表空间上自动建立10个undo段 ，我们可以通过 v$rollname 显示 所有联机undo段 的名称，通过 v$rollstat显示undo段的统计信息 。通过这两个视图之间的连接查询，就可以监视特定的undo段的特定信息。


SYS@orcl 07-SEP-14>col name for a30
SYS@orcl 07-SEP-14> select a.name,b.extents,b.writes
  2  from v$rollname a,v$rollstat b
  3  where a.usn=b.usn;


NAME                              EXTENTS     WRITES
------------------------------         ----------        ----------
SYSTEM                                  6       5408
_SYSSMU1_592353410$           4   193072
_SYSSMU2_967517682$           3     190646
_SYSSMU3_1204390606$         4     209024
_SYSSMU4_1003442803$         4     166528
_SYSSMU5_538557934$           4     304828
_SYSSMU6_2897970769$         4     191244
_SYSSMU7_3517345427$         4     157992
_SYSSMU8_3901294357$         4     164792
_SYSSMU9_1735643689$         4     232370
_SYSSMU10_4131489474$       4     181288


11 rows selected.


结果中名为 system的undo段是system表空间自己使用的回滚段(系统内部使用) ，其他10个是undo表空间上的10个回滚段，用于普通事务。


SYS@orcl 07-SEP-14>col name for a30
SYS@orcl 07-SEP-14>col tablespace_name for a30
SYS@orcl 07-SEP-14> select distinct a.name,b.extents,c.tablespace_name 
  2  from v$rollname a,v$rollstat b,dba_undo_extents c
  3  where a.usn=b.usn and a.name=c.segment_name;


NAME                              EXTENTS TABLESPACE_NAME
------------------------------ ---------- ------------------------------
_SYSSMU9_1735643689$                    4 UNDOTBS1
_SYSSMU10_4131489474$                   4 UNDOTBS1
_SYSSMU5_538557934$                     4 UNDOTBS1
_SYSSMU6_2897970769$                    4 UNDOTBS1
_SYSSMU4_1003442803$                    4 UNDOTBS1
_SYSSMU1_592353410$                     4 UNDOTBS1
_SYSSMU2_967517682$                     3 UNDOTBS1
_SYSSMU3_1204390606$                    4 UNDOTBS1
_SYSSMU7_3517345427$                    4 UNDOTBS1
_SYSSMU8_3901294357$                    4 UNDOTBS1

10 rows selected.

另外，我们也可以通过 v$session , v$transaction , v$rollname 的连接查询来确定正在执行的事务操作的会话，事务所使用的undo段，以及事务所占用的undo块的个数。

HH@orcl 07-SEP-14>insert into h1 values(6,'lucy');

1 row created.

SYS@orcl 07-SEP-14>select a.username,b.name,c.used_ublk
  2  from v$session a,v$rollname b,v$transaction c
  3  where a.saddr=c.ses_addr and b.usn=c.xidusn;
 


USERNAME   NAME                                      USED_UBLK
---------- ---------------------------------------- ----------
HH         _SYSSMU7_3517345427$                              1


dba_undo_extents用于显示undo表空间的所有区的详细信息，包括undo区尺寸和状态等信息。

SYS@orcl 07-SEP-14>select a.name,b.extent_id,b.bytes,b.status
  2  from v$rollname a,dba_undo_extents b
  3  where a.name=b.segment_name;


NAME                                      EXTENT_ID      BYTES STATUS
---------------------------------------- ---------- ---------- ---------
_SYSSMU1_592353410$                               0      65536 EXPIRED
_SYSSMU1_592353410$                               1      65536 EXPIRED
_SYSSMU1_592353410$                               2    1048576 UNEXPIRED
_SYSSMU1_592353410$                               3    1048576 EXPIRED
_SYSSMU2_967517682$                               0      65536 EXPIRED
_SYSSMU2_967517682$                               1      65536 EXPIRED
_SYSSMU2_967517682$                               2    1048576 UNEXPIRED
_SYSSMU3_1204390606$                              0      65536 EXPIRED
_SYSSMU3_1204390606$                              1      65536 EXPIRED
_SYSSMU3_1204390606$                              2    1048576 EXPIRED
_SYSSMU3_1204390606$                              3    1048576 UNEXPIRED


NAME                                      EXTENT_ID      BYTES STATUS
---------------------------------------- ---------- ---------- ---------
_SYSSMU4_1003442803$                              0      65536 EXPIRED
_SYSSMU4_1003442803$                              1      65536 EXPIRED
_SYSSMU4_1003442803$                              2    1048576 EXPIRED
_SYSSMU4_1003442803$                              3    1048576 UNEXPIRED
_SYSSMU5_538557934$                               0      65536 EXPIRED
_SYSSMU5_538557934$                               1      65536 EXPIRED
_SYSSMU5_538557934$                               2    1048576 UNEXPIRED
_SYSSMU5_538557934$                               3    1048576 UNEXPIRED
_SYSSMU6_2897970769$                              0      65536 EXPIRED
_SYSSMU6_2897970769$                              1      65536 EXPIRED
_SYSSMU6_2897970769$                              2    1048576 UNEXPIRED


NAME                                      EXTENT_ID      BYTES STATUS
---------------------------------------- ---------- ---------- ---------
_SYSSMU6_2897970769$                              3    1048576 EXPIRED
_SYSSMU7_3517345427$                              0      65536 EXPIRED
_SYSSMU7_3517345427$                              1      65536 EXPIRED
_SYSSMU7_3517345427$                              2    1048576 ACTIVE
_SYSSMU7_3517345427$                              3    1048576 EXPIRED
_SYSSMU8_3901294357$                              0      65536 EXPIRED
_SYSSMU8_3901294357$                              1      65536 EXPIRED
_SYSSMU8_3901294357$                              2    1048576 EXPIRED
_SYSSMU8_3901294357$                              3    1048576 UNEXPIRED
_SYSSMU9_1735643689$                              0      65536 EXPIRED
_SYSSMU9_1735643689$                              1      65536 EXPIRED


NAME                                      EXTENT_ID      BYTES STATUS
---------------------------------------- ---------- ---------- ---------
_SYSSMU9_1735643689$                              2    1048576 EXPIRED
_SYSSMU9_1735643689$                              3    1048576 UNEXPIRED
_SYSSMU10_4131489474$                             0      65536 EXPIRED
_SYSSMU10_4131489474$                             1      65536 EXPIRED
_SYSSMU10_4131489474$                             2    1048576 UNEXPIRED
_SYSSMU10_4131489474$                             3    1048576 EXPIRED


39 rows selected.


创建一个undo表空间undotbs2
SYS@orcl 07-SEP-14> create undo tablespace undotbs2
  2  datafile '/datafile/undotbs02.dbf' size 100m;

Tablespace created.

接着使用hh用户插入表数据，且不执行commit：
HH@orcl 07-SEP-14>insert into h2 values(3);

1 row created.

再到sys用户下，将数据库当前的undo表空间切换到undotbs2：
SYS@orcl 07-SEP-14> alter system set undo_tablespace=undotbs2;


System altered.
SYS@orcl 07-SEP-14>show parameter undo_tablespace


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace                      string      UNDOTBS2


现在我们尝试删除undo表空间undotbs2：
SYS@orcl 07-SEP-14>drop tablespace undotbs2 including contents;
drop tablespace undotbs2 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS2' is currently in use


错误显示， 当前实例正在使用的undo表空间是不能删除的。

查询回滚段的信息：
SYS@orcl 07-SEP-14>select distinct a.name,b.extents,c.tablespace_name
  2  from v$rollname a,v$rollstat b,dba_undo_extents c
  3  where a.usn=b.usn and a.name=c.segment_name;


NAME                              EXTENTS TABLESPACE_NAME
------------------------------ ---------- --------------------
_SYSSMU11_2056301534$                   2 UNDOTBS2
_SYSSMU14_1228883743$                   2 UNDOTBS2
_SYSSMU15_1493167890$                   2 UNDOTBS2
_SYSSMU18_2051411213$                   2 UNDOTBS2
_SYSSMU12_2851392508$                   2 UNDOTBS2
_SYSSMU13_3835649976$                   2 UNDOTBS2
_SYSSMU17_1294635024$                   2 UNDOTBS2
_SYSSMU19_275096719$                    2 UNDOTBS2
_SYSSMU16_292061993$                    2 UNDOTBS2
_SYSSMU20_2475344746$                   2 UNDOTBS2
_SYSSMU8_3901294357$                    4 UNDOTBS1

11 rows selected.

可以看到，虽然系统已经切换到了undotbs2， 但是undotbs1表空间上还有一个回滚段存在(用于存放hh用户的事务数据)
下面我们尝试删除undotbs1：
SYS@orcl 07-SEP-14>drop tablespace undotbs1 including contents;
drop tablespace undotbs1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
错误显示undotbs1仍然在被使用着。

此时如果要删除undotbs1， 就要将undotbs1是上的活动事务回滚或者提交，将回滚段释放 。


结束hh用户的事务：
HH@orcl 07-SEP-14>commit;

Commit complete.

过几分钟之后我们再去查询回滚段的信息：
SYS@orcl 07-SEP-14>select distinct a.name,b.extents,c.tablespace_name
  2  from v$rollname a,v$rollstat b,dba_undo_extents c
  3  where a.usn=b.usn and a.name=c.segment_name;


NAME                              EXTENTS TABLESPACE_NAME
------------------------------ ---------- --------------------
_SYSSMU11_2056301534$                   2 UNDOTBS2
_SYSSMU14_1228883743$                   2 UNDOTBS2
_SYSSMU15_1493167890$                   2 UNDOTBS2
_SYSSMU18_2051411213$                   2 UNDOTBS2
_SYSSMU12_2851392508$                   2 UNDOTBS2
_SYSSMU13_3835649976$                   2 UNDOTBS2
_SYSSMU17_1294635024$                   3 UNDOTBS2
_SYSSMU19_275096719$                    2 UNDOTBS2
_SYSSMU16_292061993$                    2 UNDOTBS2
_SYSSMU20_2475344746$                   2 UNDOTBS2


10 rows selected.
发现undotbs1上的回滚段已经消失了。
再次尝试删除undotbs1：
SYS@orcl 07-SEP-14>drop tablespace undotbs1 including contents;

Tablespace dropped.

由于undotbs1表空间已经删除了，需要将undo_tablespace=undotbs2修改到初始化参数文件中
SYS@orcl 07-SEP-14> alter system set undo_tablespace=undotbs2 scope=spfile;

System altered.

如果使用pfile启动，需要修改到pfile文件。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29800581/viewspace-1266262/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29800581/viewspace-1266262/