缩小undo表空间全记录(原创)

今天同事让我把undo表空间缩小为原来的40GB，之前由于开发商的需求，将undo表空间扩大到了200GB之大，现在需要缩小为40GB。虽然整件事有点不和逻辑，但事已至此也用不管他们怎么想的，毕竟人家跟客户走得近，而且有些事也不是我说得算，照做就是了。下面是过程

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

PL/SQL Release 10.2.0.5.0 - Production

CORE    10.2.0.5.0      Production

TNS for IBM/AIX RISC System/6000: Version 10.2.0.5.0 - Productio

NLSRTL Version 10.2.0.5.0 - Production

查看当前undo表空间使用情况

SQL> SELECT DISTINCT STATUS "状态",

  2                  COUNT(*) "EXTENT数量",

  3                  SUM(BYTES) / 1024 / 1024 / 1024 "UNDO大小"

  4    FROM DBA_UNDO_EXTENTS

  5   GROUP BY STATUS;

????      EXTENT????   UNDO????

--------- ---------- ----------

EXPIRED         3524 49.8547363

UNEXPIRED       2985 162.694336

万幸，由于已经是下班时间，没有获得的undo表空间，今天就能完成全部工作，偷乐中。准备数据文件如下

#lslv lv_undo_tmp01

LOGICAL VOLUME:     lv_undo_tmp01          VOLUME GROUP:   cxdatavg

LV IDENTIFIER:      00f65ad200004c000000012e3d0271ff.104 PERMISSION:     read/write

VG STATE:           active/complete        LV STATE:       closed/syncd

TYPE:               jfs2                   WRITE VERIFY:   off

MAX LPs:            512                    PP SIZE:        512 megabyte(s)

COPIES:             1                      SCHED POLICY:   striped

LPs:                80                     PPs:            80

STALE PPs:          0                      BB POLICY:      relocatable

INTER-POLICY:       maximum                RELOCATABLE:    no

INTRA-POLICY:       middle                 UPPER BOUND:    4

MOUNT POINT:        N/A                    LABEL:          None

MIRROR WRITE CONSISTENCY: off                                    

EACH LP COPY ON A SEPARATE PV ?: yes (superstrict)                      

Serialize IO ?:     NO                                     

STRIPE WIDTH:       4                                      

STRIPE SIZE:        64m                                    

DEVICESUBTYPE : DS_LVZ                                        

COPY 1 MIRROR POOL: None                                   

COPY 2 MIRROR POOL: None                                   

COPY 3 MIRROR POOL: None          

确认当前undo表空间

SQL> show parameter undo

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

创建新的undo表空间undotemptbs1

SQL> create undo tablespace UNDOTEMPTBS1 datafile '/dev/rlv_undo_tmp01';

create undo tablespace UNDOTEMPTBS1 datafile '/dev/rlv_undo_tmp01'

*

ERROR at line 1:

ORA-01144: File size (5242879 blocks) exceeds maximum of 4194303 blocks

报错ORA-01144，仔细检查后才发现在没有使用大表空间的情况下，原来单个数据文件超过了32GB，导致报错ORA-01144，具体原因如下

rowid在磁盘上需要10 个字节(byte)的存储空间并使用18 个字符来显示它包含下列组件：

数据对象编号：每个数据对象如表或索引在创建时都分配有此编号，并且此编号在数据库中是唯一的;

相关文件编号：此编号对于一个表空间中的每个文件是唯一的;

块编号：表示包含此行的块在文件中的位置;

行编号：标识块头中行目录位置的位置;

在内部数据对象编号需要32位(bit)，相关文件编号需要10位(bit)，块编号需要22位(bit)，位行编号需要16位，加起来总共是80位或10 个字节，ROWID 使用以64为基数的编码方案来显示该方案将6个位置用于数据对象，编号3个位置用于相关文件编号,6个位置用于块编号,3个位置用于行编号以64为基数的编码方案使用字符A-Z a-z 0-9 + 和/共64 个字符，如下例所示：

AAAB/R AAB AAAGmS AAA

由于block是22位(bit),

22bit就是一个22位二进制数，换算成十进制就是 2的22次方-1=4194303 也就是说，一个datafile最多容纳这么多个blocks

数据块大小（block  size)为2K的数据文件的size最大只能是8G，而依此类推8K数据块的一个数据文件的最大size只能是32G。系统中undo表空间只能使用8k的小表空间，故undo最大的单个数据文件为32GB。

于是创建单个文件大小为30GB的undo表空间

SQL> create undo tablespace UNDOTEMPTBS1 datafile '/dev/rlv_undo_tmp01' size 30G;

Tablespace created.
切换数据库默认undo表空间为UNDOTEMPTBS1

SQL> alter system set undo_tablespace=UNDOTEMPTBS1;

System altered.

SQL> show parameter  undo_tablespace    

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace                      string      UNDOTEMPTBS1
删除原有undo表空间UNDOTBS1

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

SQL> show parameter undo_tablespace

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace                      string      UNDOTEMPTBS1
创建undo表空间UNDOTBS1，这里/dev/rlv_cx_undo1_1和/dev/rlv_cx_undo1_2是之前undo表空间使用的数据文件

SQL> create undo tablespace UNDOTBS1 

  2  datafile '/dev/rlv_cx_undo1_1' size 20400m,

'/dev/rlv_cx_undo1_2' size 20400m;  3  

Tablespace created.

切换数据库默认undo表空间为UNDOTBS1

SQL> alter system set undo_tablespace=UNDOTBS1;

System altered.

删除原有undo表空间UNDOTEMPTBS1

SQL> drop tablespace UNDOTEMPTBS1 including contents and datafiles;

Tablespace dropped.
确认当前undo表空间

SQL> SHOW PARAMETER UNDO_tablespace

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_tablespace                      string      UNDOTBS1

SQL> set linesize 150

SQL> column file_name format a65

SQL> column tablespace_name format a20

SQL> select f.tablespace_name tablespace_name,round((d.sumbytes/1024/1024/1024),2) total_g,

round(f.sumbytes/1024/1024/1024,2) free_g,

round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_g,

round((d.sumbytes-f.sumbytes)*100/d.sumbytes,2) used_percent

from (select tablespace_name,sum(bytes) sumbytes from dba_free_space group by tablespace_name) f,

(select tablespace_name,sum(bytes) sumbytes from dba_data_files group by tablespace_name) d 

where f.tablespace_name= d.tSQL> SQL>   2    3    4    5    6    7  ablespace_name

order by d.tablespace_name; 

TABLESPACE_NAME         TOTAL_G     FREE_G     USED_G USED_PERCENT

-------------------- ---------- ---------- ---------- ------------

。。。。。。。。。。。。。省略若干行。。。。。。。。。。。。

SYSAUX                     9.96       8.56        1.4        14.03

SYSTEM                     9.96       8.76        1.2        12.06

UNDOTBS1                  39.84      39.84          0          .01

UNDOTBS2                  39.84      13.73      26.12        65.54

USERS                      4.98        2.3       2.68        53.89

WFLTAIS_DAT               19.92      19.92          0            0

。。。。。。。。。。。。。省略若干行。。。。。。。。。。。。

SQL> select file_name from dba_data_files where file_name like '%undo%';

FILE_NAME

-----------------------------------------------------------------

/dev/rlv_cx_undo1_1

/dev/rlv_cx_undo2_1

/dev/rlv_cx_undo1_2

/dev/rlv_cx_undo2_2

准备删除多余的数据文件，查看状态如下

#lsvg -l cxdatavg|grep undo

lv_cx_undo1_2       jfs2       40      40      4    open/syncd    N/A

lv_cx_undo2_2       jfs2       40      40      4    open/syncd    N/A

lv_cx_undo1_3       jfs2       40      40      4    open/syncd    N/A

lv_cx_undo1_4       jfs2       40      40      4    open/syncd    N/A

lv_cx_undo1_5       jfs2       40      40      4    open/syncd    N/A

lv_cx_undo1_6       jfs2       40      40      4    open/syncd    N/A

lv_cx_undo1_7       jfs2       40      40      4    open/syncd    N/A

lv_cx_undo1_8       jfs2       40      40      4    open/syncd    N/A

lv_cx_undo1_9       jfs2       40      40      4    open/syncd    N/A

lv_cx_undo1_10      jfs2       40      40      4    open/syncd    N/A

lv_undo_tmp01       jfs2       80      80      4    closed/syncd  N/A

lv_cx_undo1_1       jfs2       40      40      4    open/syncd    N/A

lv_cx_undo2_1       jfs2       40      40      4    open/syncd    N/A

发现数据文件出了lv_undo_tmp01，其他的状态都还是open，查看

$ fuser /dev/rlv_cx_undo1_3

/dev/rlv_cx_undo1_4:  25428220

$ fuser /dev/rlv_cx_undo1_4

/dev/rlv_cx_undo1_4:  25428220

$ fuser /dev/rlv_cx_undo1_5

/dev/rlv_cx_undo1_5:  25428220

$ fuser /dev/rlv_cx_undo1_6

/dev/rlv_cx_undo1_6:  25428220

$ fuser /dev/rlv_cx_undo1_7

/dev/rlv_cx_undo1_7:  25428220

$ fuser /dev/rlv_cx_undo1_8

/dev/rlv_cx_undo1_8:  25428220

$ fuser /dev/rlv_cx_undo1_9

/dev/rlv_cx_undo1_9:  25428220

$ fuser /dev/rlv_cx_undo1_10

/dev/rlv_cx_undo1_10:  25428220

$ ps -ef|grep 25428220

     dsg 25428220 19660886   0   May 14      - 101:08 oraclezgcxdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) 

  oracle 28770354 11141152   0 19:09:42  pts/2  0:00 grep 25428220 

全都是因为dsg的25428220还在访问那些数据文件。百度了下，有的说是重启可以解决问题。个人觉得重启还不如kill，性质都一样。由于公司采用了dsg的数据同步软件进行数据同步，重启也需要经过客户同意，工作比较繁琐，也就先放着看看，看过一段时间会不会自动释放。

虽然还有后续工作，但至少已经完成了基本目标，聊以自慰，下班。

本文原创，装载请注明出处、作者

如有错误，欢迎指正

邮箱:[e-mail]

评论

5 楼 zzwssfd 2012-06-15  

麻烦了，谢谢你

4 楼 czmmiao 2012-06-15  

1、由于是undo表空间，只是smallfile类型，单个数据文件无法超过32G.
2、生产库，aix系统

3 楼 zzwssfd 2012-06-15  

1，我理解的你说的“数据文件”是undo在OS里面的文件，不是oracle里面说的data file。
2，oracle会认为这个表空间的size会超过32G，这个还是没能理解。还望麻烦你
3，你的系统是AIX的？

2 楼 czmmiao 2012-06-15  

zzwssfd 写道

请教：
1，为何要准备数据文件
2， create undo tablespace UNDOTEMPTBS1 datafile '/dev/rlv_undo_tmp01';这里并没有指定undo 表空间的大小，为何oracle会认为这个表空间的size会超过32G

1、准备数据文件是为了建立新的undo表空间做切换用
2、请仔细看下该lv的大小为40GB

1 楼 zzwssfd 2012-06-15  

请教：
1，为何要准备数据文件
2， create undo tablespace UNDOTEMPTBS1 datafile '/dev/rlv_undo_tmp01';这里并没有指定undo 表空间的大小，为何oracle会认为这个表空间的size会超过32G