收缩undo表空间

Leshami

发布于 2018-08-13 15:13:31

2.7K0

发布于 2018-08-13 15:13:31

通常情况下，如果undo表空间的处于自动扩展且未指定最大值的情形，对于使用小表空间模式的数据库，undo表空间可能会一再增长，直到达到32GB。或者是在指定了自动扩展及其最大值而月底或年末的批量数据计算导致undo表空间疯狂超范围增长后不再释放。对于这些情形我们需要手动收缩表空间以达到节省空间资源以及数据库管理开销，如rman备份等。本文列出了收缩undo表空间的基本步骤并给出示例。有关表空间，undo表空间的文章可参考: Oracle 表空间与数据文件 Oracle 回滚(ROLLBACK)和撤销(UNDO) 检查及设置合理的undo表空间 1、undo表空间收缩的基本步骤 a、使用较小的尺寸创建一个新的undo表空间 SQL> create undo tablespace UNDO_RBS1 datafile 'undorbs1.dbf' size <new size>;

b、设置新的undo表空间为系统undo表空间 SQL> alter system set undo_tablespace=undo_rbs1;

c、删除原始的undo表空间及其数据文件 SQL> drop tablespace undo_rbs0 including contents.

d、使用原始undo表空间名创建一个新的小尺寸的undo表空间并且且换回系统undo，删除过渡undo表空间(此步骤可选)

2、收缩undo表空间示例

--环境
goex_admin@CICCFIX> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

--创建测试表t
goex_admin@CICCFIX> CREATE TABLE t
  2  AS
  3  SELECT rownum AS id,
  4         round(5678+dbms_random.normal*1234) AS n1,
  5         mod(255+trunc(dbms_random.normal*1000),255) AS n2,
  6         dbms_random.string('p',255) AS pad
  7  FROM dual
  8  CONNECT BY level <= 10000
  9  ORDER BY dbms_random.value;

Table created.

--观察当前回滚段的情形,注意第一行为system表空间的撤销段，用于系统表空间的撤销
--其余的为public，也就是说任意用户都可以使用这些基于undo表空间的回滚段
goex_admin@CICCFIX> @rollback_segments

Rollback Name      Tablspace   Init/Next Extents    Min/Max Ex Status              Bytes Extents  Shrinks    Wraps    Opt. Size
------------------ ----------- -------------------- ---------- -------- ---------------- ------- -------- -------- ------------
SYS.SYSTEM         SYSTEM      114688 /             1 / 32765  ONLINE            393,216       6        0        0
PUBLIC._SYSSMU10$  UNDOTBS     131072 /             2 / 32765  ONLINE          3,276,800       5       77      209
PUBLIC._SYSSMU21$  UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6       75      229
PUBLIC._SYSSMU22$  UNDOTBS     131072 /             2 / 32765  ONLINE         14,811,136      16      194    1,004
PUBLIC._SYSSMU3$   UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6      133      394
PUBLIC._SYSSMU4$   UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6      115      386
PUBLIC._SYSSMU5$   UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6      123      392
PUBLIC._SYSSMU6$   UNDOTBS     131072 /             2 / 32765  ONLINE          5,373,952       7      119      367
PUBLIC._SYSSMU7$   UNDOTBS     131072 /             2 / 32765  ONLINE          5,373,952       7      106      367
PUBLIC._SYSSMU8$   UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6      121      421
PUBLIC._SYSSMU9$   UNDOTBS     131072 /             2 / 32765  ONLINE         11,665,408      13      114      368

--当前undo表空间的大小
goex_admin@CICCFIX> col file_name format a55
goex_admin@CICCFIX> select tablespace_name,file_name,AUTOEXTENSIBLE,bytes/1024/1024 size_mb
  2  from dba_data_files where tablespace_name like '%UNDO%';

TABLESPACE_NAME                FILE_NAME                                               AUT    SIZE_MB
------------------------------ ------------------------------------------------------- --- ----------
UNDOTBS                        /u02/database/CICCFIX/undo/undotbs1CICCFIX2.dbf         YES 24280.3125

--当前数据库存在的事务，为零值，表示没有未提交的事务
goex_admin@CICCFIX> select count(*) from v$transaction;

  COUNT(*)
----------
         0

--更新测试表以产生大量的undo
goex_admin@CICCFIX> update t set pad=dbms_random.string('l',255);  

10000 rows updated.

--再次观察undo使用情况
--可以看到7号undo段上的extents由7增加到11，其字节数由5,373,952增加到9,568,256，增加了4M多
goex_admin@CICCFIX> @rollback_segments

Rollback Name      Tablspace   Init/Next Extents    Min/Max Ex Status              Bytes Extents  Shrinks    Wraps    Opt. Size
------------------ ----------- -------------------- ---------- -------- ---------------- ------- -------- -------- ------------
SYS.SYSTEM         SYSTEM      114688 /             1 / 32765  ONLINE            393,216       6        0        0
PUBLIC._SYSSMU10$  UNDOTBS     131072 /             2 / 32765  ONLINE          3,276,800       5       77      209
PUBLIC._SYSSMU21$  UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6       75      229
PUBLIC._SYSSMU22$  UNDOTBS     131072 /             2 / 32765  ONLINE         14,811,136      16      194    1,004
PUBLIC._SYSSMU3$   UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6      133      394
PUBLIC._SYSSMU4$   UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6      115      386
PUBLIC._SYSSMU5$   UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6      123      392
PUBLIC._SYSSMU6$   UNDOTBS     131072 /             2 / 32765  ONLINE          5,373,952       7      119      367
PUBLIC._SYSSMU7$   UNDOTBS     131072 /             2 / 32765  ONLINE          9,568,256      11      106      371
PUBLIC._SYSSMU8$   UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6      121      421
PUBLIC._SYSSMU9$   UNDOTBS     131072 /             2 / 32765  ONLINE         11,665,408      13      114      368

--也可以看到该事物所使用的块数为527及其undo段号7
goex_admin@CICCFIX> SELECT addr,xidusn,used_ublk FROM v$transaction;

ADDR                 XIDUSN  USED_UBLK
---------------- ---------- ----------
000000009CA69A00          7        527

--Author : Leshami
--Blog   : http://blog.csdn.net/leshami

--下面回滚事务
goex_admin@CICCFIX> rollback;

--再次查看undo信息，其占用的undo段并没有立即释放
goex_admin@CICCFIX> @rollback_segments

Rollback Name      Tablspace   Init/Next Extents    Min/Max Ex Status              Bytes Extents  Shrinks    Wraps    Opt. Size
------------------ ----------- -------------------- ---------- -------- ---------------- ------- -------- -------- ------------
SYS.SYSTEM         SYSTEM      114688 /             1 / 32765  ONLINE            393,216       6        0        0
PUBLIC._SYSSMU10$  UNDOTBS     131072 /             2 / 32765  ONLINE          3,276,800       5       77      209
PUBLIC._SYSSMU21$  UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6       75      229
PUBLIC._SYSSMU22$  UNDOTBS     131072 /             2 / 32765  ONLINE         14,811,136      16      194    1,004
PUBLIC._SYSSMU3$   UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6      133      394
PUBLIC._SYSSMU4$   UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6      115      386
PUBLIC._SYSSMU5$   UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6      123      392
PUBLIC._SYSSMU6$   UNDOTBS     131072 /             2 / 32765  ONLINE          5,373,952       7      119      367
PUBLIC._SYSSMU7$   UNDOTBS     131072 /             2 / 32765  ONLINE          9,568,256      11      106      371
PUBLIC._SYSSMU8$   UNDOTBS     131072 /             2 / 32765  ONLINE          4,325,376       6      121      421
PUBLIC._SYSSMU9$   UNDOTBS     131072 /             2 / 32765  ONLINE         11,665,408      13      114      368

--下面我们来先尝试收缩一下undo，收缩到23g，成功
goex_admin@CICCFIX> alter database datafile '/u02/database/CICCFIX/undo/undotbs1CICCFIX2.dbf' resize 23g;

Database altered.

--再次尝试收缩到10g出现错误提示，数据库中不存在事务，而undo表空间得不到释放
goex_admin@CICCFIX> alter database datafile '/u02/database/CICCFIX/undo/undotbs1CICCFIX2.dbf' resize 10g;
alter database datafile '/u02/database/CICCFIX/undo/undotbs1CICCFIX2.dbf' resize 10g
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

--下面新增一个新的undo表空间
goex_admin@CICCFIX> create undo tablespace undotbs2 datafile '/u02/database/CICCFIX/undo/undotbs_CICCFIX.dbf' size 100m autoextend on;

--设置系统undo表空间为新的表空间undotbs2
goex_admin@CICCFIX> alter system set undo_tablespace='UNDOTBS2' scope=both; 

-->注，如果使用了pfile文件启动实例，此处会收到错误提示，可以省略scope参数，直接修改内存中的值，下次重启前需要相应修改pfile中的undo_tablespace

--观察undo段的变化情况，可以看到新的原来的undotbs表空间上的undo段全部处于OFFLINE状态，新的undo表空间为online状态
goex_admin@CICCFIX> @rollback_segments 

Rollback Name      Tablspace   Init/Next Extents    Min/Max Ex Status              Bytes Extents  Shrinks    Wraps    Opt. Size
------------------ ----------- -------------------- ---------- -------- ---------------- ------- -------- -------- ------------
SYS.SYSTEM         SYSTEM      114688 /             1 / 32765  ONLINE            393,216       6        0        0
PUBLIC._SYSSMU1$   UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU10$  UNDOTBS     131072 /             2 / 32765  OFFLINE         3,276,800       5
PUBLIC._SYSSMU11$  UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU12$  UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU13$  UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU14$  UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU15$  UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU16$  UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU17$  UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU18$  UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU2$   UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU21$  UNDOTBS     131072 /             2 / 32765  OFFLINE         4,325,376       6
PUBLIC._SYSSMU22$  UNDOTBS     131072 /             2 / 32765  OFFLINE        20,054,016      21
PUBLIC._SYSSMU3$   UNDOTBS     131072 /             2 / 32765  OFFLINE         4,325,376       6
PUBLIC._SYSSMU4$   UNDOTBS     131072 /             2 / 32765  OFFLINE         4,325,376       6
PUBLIC._SYSSMU5$   UNDOTBS     131072 /             2 / 32765  OFFLINE         4,325,376       6
PUBLIC._SYSSMU6$   UNDOTBS     131072 /             2 / 32765  OFFLINE         5,373,952       7
PUBLIC._SYSSMU7$   UNDOTBS     131072 /             2 / 32765  OFFLINE         9,568,256      11
PUBLIC._SYSSMU8$   UNDOTBS     131072 /             2 / 32765  OFFLINE         4,325,376       6
PUBLIC._SYSSMU9$   UNDOTBS     131072 /             2 / 32765  OFFLINE        11,665,408      13

--删除之前过大的undo表空间及数据文件
goex_admin@CICCFIX> drop tablespace undotbs including contents and datafiles;

--删除之后，原来undo表空间上的回滚段不复存在。新的undo段为1-2，11-18，总计10个undo段
goex_admin@CICCFIX> @rollback_segments

Rollback Name      Tablspace   Init/Next Extents    Min/Max Ex Status              Bytes Extents  Shrinks    Wraps    Opt. Size
------------------ ----------- -------------------- ---------- -------- ---------------- ------- -------- -------- ------------
SYS.SYSTEM         SYSTEM      114688 /             1 / 32765  ONLINE            393,216       6        0        0
PUBLIC._SYSSMU1$   UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU11$  UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU12$  UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU13$  UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU14$  UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU15$  UNDOTBS2    131072 /             2 / 32765  ONLINE            196,608       3        0        2
PUBLIC._SYSSMU16$  UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        1
PUBLIC._SYSSMU17$  UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0
PUBLIC._SYSSMU18$  UNDOTBS2    131072 /             2 / 32765  ONLINE            327,680       5        0        3
PUBLIC._SYSSMU2$   UNDOTBS2    131072 /             2 / 32765  ONLINE            131,072       2        0        0

--下面是收缩之后的结果   
goex_admin@CICCFIX> select tablespace_name,file_name,AUTOEXTENSIBLE,bytes/1024/1024 size_mb
  2  from dba_data_files where tablespace_name like '%UNDO%';

TABLESPACE_NAME                FILE_NAME                                               AUT    SIZE_MB
------------------------------ ------------------------------------------------------- --- ----------
UNDOTBS2                       /u02/database/CICCFIX/undo/undotbs_CICCFIX.dbf          YES        100

3、后记 a、undo表空间的释放通过创建新的过渡表空间且将新创建的设置为系统undo表空间以达到空间释放目的 b、原始undo表空间能否删除，需要看原undo表空间上是否存在事务，如果存在则不能立即删除，需要等到提交或回滚后再删除(或杀掉session) c、原始undo表空间删除后可能存在空间不能立即返回给OS的情形，可能需要重启数据库等 d、注意修改系统undo表空间时当前使用的参数文件(pfile,spfile)可能引发的下次重启收到undo错误的相关提示

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2013年10月27日，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库