oracle单库重建undo表空间步骤

前言：undo表空间不足可直接增加空间；

alter tablespace UNDOTBS1   add datafile '/data/oradata/datafile/UNDOTBS102.dbf' size 30g autoextend off;   

注：单次增加最大不得超过32G

回收空间

缩小表空间直接可以resize命令缩小；
先用sql查看有没有回收的空间

alter database datafile '/data/oradata/datafile/UNDOTBS102.dbf' resize 20480m;

查看表空间语句

SELECT TABLESPACE_NAME "表空间",
       To_char(Round(BYTES / 1024, 2), '99990.00')
       || 'G'           "实有",
       To_char(Round(FREE / 1024, 2), '99990.00')
       || 'G'          "现有",
       To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')
       || 'G'          "使用",
       To_char(Round(10000 * USED / BYTES) / 100, '99990.00')
       || '%'          "比例"
FROM   (SELECT A.TABLESPACE_NAME                             TABLESPACE_NAME,
               Floor(A.BYTES / ( 1024 * 1024 ))              BYTES,
               Floor(B.FREE / ( 1024 * 1024 ))               FREE,
               Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED
        FROM   (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                       Sum(BYTES)      BYTES
                FROM   DBA_DATA_FILES
                GROUP  BY TABLESPACE_NAME) A,
               (SELECT TABLESPACE_NAME TABLESPACE_NAME,
                       Sum(BYTES)      FREE
                FROM   DBA_FREE_SPACE
                GROUP  BY TABLESPACE_NAME) B
        WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME)
--WHERE TABLESPACE_NAME LIKE 'CDR%' --这一句用于指定表空间名称
ORDER  BY Floor(10000 * USED / BYTES) DESC;

查看可用于回收的表空间语句

select a.file#,a.name,a.bytes/1024/1024 CurrentMB,  
       ceil(HWM * a.block_size)/1024/1024 ResizeTo,  
       (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,  
       'alter database datafile '''||a.name||''' resize '||  
       ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD  
from v$datafile a,  
     (select file_id,max(block_id+blocks-1) HWM  
       from dba_extents where file_id in   
              (select b.file#  From v$tablespace a ,v$datafile b  
                where a.ts#=b.ts# and a.name='UNDOTBS1')
       group by file_id) b  
where a.file# = b.file_id(+)  
and (a.bytes - HWM *block_size)>0  
order by 5 ;

本次是因为没有可回收的空间了才做的重建undo；

重建undo表空间步骤如下：

1）查看UNDO信息

sqlplus / as sysdba
show parameter undo

2）创建新的undo表空间UNDOTBS2

create undo tablespace UNDOTBS2 datafile '/data/oradata/LCIMSB/datafile/UNDOTBS201.dbf' size 20G autoextend on;

3）将新建的表空间UNDOTBS2指向默认表空间

alter system set undo_tablespace=UNDOTBS2 scope=both;

4）查看表空间信息是否切换

undo_tablespace已经从原来UNDOTBS1更改为UNDOTBS2

5）确保所有UNDOTBS1的status都已offline

select SEGMENT_NAME ,STATUS ,TABLESPACE_NAME from dba_rollback_segs;

6）当UNDOTBS1都为OFFLINE状态后，删除UNDOTBS1；

drop tablespace UNDOTBS1 including contents and datafiles;

7）重新查看表空间情况

UNDO表空间已由原来UNDOTBS1的90G改为UNDOTBS2的20G了，节省出70G空间。