Oracle表空间满清理方案汇总分享

前言思考

一、第一种增加表空间的数据文件数量达到总容量的提升

二、第二种解决方案针对system和sysaux的操作

2.1SYSTEM表空间优化

2.2sysaux表空间回收

2.2.1针对sysaux的表空间爆满还有第二套方案维护

三、第三种解决方案使用alter tablespace resize更改表空间的数据文件的容量；

编辑

前言思考

刚接触Oracle的项目时候，发现我们业务系统的oracle使用3年了，各个表空间都使用快爆满了。经过一段时间的研究分享两套解决方案给大家，首先分两种表空间的扩容和回收。

表空间的分类

第一种表空间是系统自带的表空间system和sysaux，每个表空间的作用和说明，可以翻阅Oracle的官方文档。第二种表空间是我们业务系统创建的表空间用于存放数据的了，比如HS_RISK_DATAHS_INFO_IDX等，是使用数据存储时候表空间会自动增加和扩容。

表空间的扩容和回收

第一种解决方案是在磁盘还有一定量的空间情况下面增加表空间的数据文件，让表空间的数据总量增大。因为增加表空间的数据文件是占磁盘存储量。针对SYSTEM,SYSAUX和HS_RISK_DATA、HS_INFO_IDX的表空间都适用，并且每日的数据都会双向写入表空。

第二种解决方案针对系统自带表空间system和sysaux的操作，针对system的表空间是减少用到的SM/AWR组件表使用空间，使用truncate对表空间收缩，针对sysaux的表空间爆满是删除AWR数据，在通过Move操作回收表的水平线，在重建表的索引。

针对sysaux的表空间爆满还有第三套方案：删除EM相关的内容统计信息，AWR快照、审计信息等。利用SQL语句找出快照最小最大的ID进行删除快照。

第三种解决方案在磁盘空间充沛的情况下面，使用alter tablespace resize更改表空间的数据文件的容量。最好每次增加的容量在1g-2g之间，因为一下增加太多，系统磁盘空间需要划分更多存储空间过去，并且耗费时间。

实验环节展示一下三种扩容方案

实验环境配置：centos6.9, Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

一、第一种增加表空间的数据文件数量达到总容量的提升

使用下列SQL语句查询表空间使用率和空闲率（注保证磁盘存储空间足够多，每增一个数据文件会占据存储空间值）。

SELECT UPPER(F.TABLESPACE_NAME)                                                                 "表空间名",
       D.TOT_GROOTTE_MB                                                                         "表空间大小(M)",
       D.TOT_GROOTTE_MB - F.TOTAL_BYTES                                                         "已使用空间(M)",
       TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99') "使用比",
       F.TOTAL_BYTES                                                                            "空闲空间(M)",
       F.MAX_BYTES                                                                              "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
             ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
             ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
      FROM SYS.DBA_FREE_SPACE
      GROUP BY TABLESPACE_NAME) F,
     (SELECT DD.TABLESPACE_NAME,
             ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
      FROM SYS.DBA_DATA_FILES DD
      GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;

记录一下没增加数据文件之前的参数值。待会增加以后表空间使用比会降下来

使用SQL语句查询表空间的数据文件存放的位置。

--查询表空间各个数据文件路径
select   tablespace_name,   file_id,   file_name,   
round(bytes/(1024*1024*1024),0)   total_space   
from   dba_data_files   
order   by   tablespace_name;

使用SQL在表空间存放在位置添加数据文件，注意按照顺序增加，并且size参数后面增加数据文件容量最好是按照Gb增加，避免增加过多数据文件。并且自动满后扩展到8Gb接触限制条件，无限增长了。

--增加表空间数据文件路径
alter tablespace SYSAUX add datafile '/u01/oradata/uatdb/sysaux02.dbf' size 2G autoextend on next 8G maxsize unlimited;
alter tablespace SYSTEM add datafile '/u01/oradata/uatdb/system02.dbf' size 2G autoextend on next 8G maxsize unlimited;
alter tablespace HS_HIS_DATA add datafile '/u01/oradata/uatdb/hisdat02.dbf' size 2G autoextend on next 8G maxsize unlimited;
alter tablespace HS_HIS_IDX add datafile '/u01/oradata/uatdb/hisidx02.dbf' size 2G autoextend on next 8G maxsize unlimited;

增加表空间的数据文件以后，在使用SQL查询之后，表空间的数据文件会增加一个。

再次查询总的表空间容量参数之后，表空间使用比会降低。表空间容量会上涨了。不管是系统表空间还是业务表空间都得到缓解了。

centos6.9系统中设置oracle中表空间的配置文件的路径也会随着数据文件增加，容量上涨。

二、第二种解决方案针对system和sysaux的操作

2.1SYSTEM表空间优化

首先我们先用SQL语句查询SYSTEM和SYSAUX用了多少表空间。基本上都快爆满了，sysaux占据97.22%，system占据97.03%记得这个数值。后期优化时候，这个数值会下来

SELECT occupant_name "Item", 
         space_usage_kbytes / 1048576 "Space Used (GB)", 
        schema_name "Schema", 
          move_procedure "Move Procedure" 
     FROM v$sysaux_occupants 
     where occupant_name='SM/AWR';
     
SELECT * FROM ( 
  SELECT D.TABLESPACE_NAME, 
          SPACE || 'M' "SUM_SPACE(M)", 
          BLOCKS "SUM_BLOCKS", 
          SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", 
          ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' 
             "USED_RATE(%)", 
          FREE_SPACE || 'M' "FREE_SPACE(M)" 
     FROM (  SELECT TABLESPACE_NAME, 
                    ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 
                    SUM (BLOCKS) BLOCKS 
               FROM DBA_DATA_FILES 
           GROUP BY TABLESPACE_NAME) D, 
          (  SELECT TABLESPACE_NAME, 
                    ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE 
               FROM DBA_FREE_SPACE 
           GROUP BY TABLESPACE_NAME) F 
    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
   UNION ALL                                                           
   SELECT D.TABLESPACE_NAME, 
          SPACE || 'M' "SUM_SPACE(M)", 
          BLOCKS SUM_BLOCKS, 
          USED_SPACE || 'M' "USED_SPACE(M)", 
          ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", 
          NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" 
     FROM (  SELECT TABLESPACE_NAME, 
                    ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 
                    SUM (BLOCKS) BLOCKS 
               FROM DBA_TEMP_FILES 
           GROUP BY TABLESPACE_NAME) D, 
          (  SELECT TABLESPACE_NAME, 
                    ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, 
                    ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE 
               FROM V$TEMP_SPACE_HEADER 
           GROUP BY TABLESPACE_NAME) F 
    WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
   ORDER BY 1)  
   WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM');

下面查看下使用SYSTEM和SYSAUX表空间的比较大的表有哪些。

可见，大表大部分都是AUD$和WRH$开头的AWR基表，AUD$使用SYSTEM表空间，AWR的基表使用SYSAUX表空间，下面再查看下SYSAUX表空间的使用情况，可以通过v$sysaux_occupants视图查询到。

可见AUD组件就使用了将近6GB的SYSAUX表空间，也就是说审计和AWR占用了大量的SYSTEM和SYSAUX表空间，而这些数据是可以定期清理的，都没有必要保留太长的时间。
下面先清理审计的数据，如果要保留部分AUD$里面记录的审计数据，可以把想要的数据插入到一张临时表，然后直接truncate这张表就可以了，truncate操作会直接回收AUD$占用的空间。

truncate操作（需要用SYSDBA权限来执行）

执行完truncate操作后，system的表空间会降低很多。因为大部分AUD$里面记录的审计数据会删除掉

2.2sysaux表空间回收

可见SM/AWR组件就使用了将近6GB的SYSAUX表空间，也就是说审计和AWR占用了大量的SYSAUX表空间。

下面再来回收下SYSAUX表空间，这个相对比较麻烦，也比较耗时。
如上文所示，通过查看v$sysaux_occupants视图，可以确定占用SYSAUX表空间过多的大部分都是AWR的基表，这样只要删除部分AWR数据理论上就可以回收一部分SYSAUX表空间，通常AWR的数据都会设置保留期限，10g版本默认保留7天，11g版本默认保留8天，可以通过dba_hist_wr_control视图来查看(注：并不是所有DBA开头的表都是数据字典，也有很多是视图，

大家可能会有疑问了，AWR的数据既然只保留七八天，为什么还会占用这么多的SYSAUX表空间呢？这个问题我个人认为主要有以下两个原因，首先，AWR删除过期的数据是通过DELETE操作完成的，这样就会产生大量的碎片，特别是SYSAUX表空间存在自动扩展的数据文件，而且这个数据文件没有扩展到最大，还有扩展的空间情况下会很明显，其次就是ASH的数据有些情况下是不受AWR的保留策略影响的，这个从下面的SQL就可以看出。

可以看到，ASH的数据从第一个快照开始一直都在保留，导致WRH$_ACTIVE_SESSION_HISTORY表很大，使用DBMS_WORKLOAD_REPOSITORY包清理过期或者不需要的AWR数据，可以回收这部分空间。（注意SQL语句中snap_id的最高值，一定根据自己筛查出来的结果定值。）

清理了AWR数据之后，你会发现SYSAUX表空间的空间并没有被回收，使用率还和之前一样，这是因为清理AWR操作是通过DELETE操作实现的，表的水位线并没有下降导致的。下面通过MOVE操作回收这个表的水位线，来回收这部分被删除数据占用的空间。

（注意PARTITION_NAME中的取值范围，不能单纯复制粘贴，注意我的结尾880025_0）

下面按照分区进行MOVE操作，来回收空间

对分区表进行MOVE之后，需要重建索引，查看这个表的索引信息。

再次查看，WRH$_ACTIVE_SESSION_HISTORY的空间已经回收。

回收表空间WRH$_ACTIVE_SESSION_HISTORY后，总体的SYSAUX的表空间下降些许。原来是95.02%

通过v$sysaux_occupants视图，可以查询到AWR占用空间由之前的将近6GB降为了不到5GB。按照同样的方法，回收下WRH$_EVENT_HISTOGRAM表的空间在通过回收下WRH$_EVENT_HISTOGRAM表的空间（下图1是找到WRH$_EVENT_HISTOGRAM所在索引）

同样的方法回收表空间，在进行重铸索引。

回收下WRH$_EVENT_HISTOGRAM表的空间，剩下的SYSAUX表空间数据

2.2.1针对sysaux的表空间爆满还有第二套方案维护

SYSAUX表空间做为SYSTEM表空间的辅助表空间，主要存放EM相关的内容以及表统计信息，AWR快照，审计信息等。修改统计信息的保持时间，默认为31天，这里修改为7天，过期的统计信息会自动被删除。

修改AWR快照的保存的参数（我这里没有执行成功，可能被某个Oracle参数限制住了）

---修改AWR快照的保存时间为7天(72460),每小时收集一次
begin 
  dbms_workload_repository.modify_snapshot_settings ( 
  interval => 60, 
  retention => 10080, 
  topnsql => 100 
  );

查询最最小和最大快照ID：（楼上我操作删除快一次快照，到22513，现在最小的快照值为22514开始）

通过执行下图的SQL语句，按照快照最小，最大值来进行删除

三、第三种解决方案使用alter tablespace resize更改表空间的数据文件的容量；

先查询表空间里面有多少的数据文件值，知道数据文件的名字

通过下列语句，可以看到每个表空间的数据文件容量为多少了。

 select a.file# as "数据文件id",
       a.name as "数据文件路径",
       a.bytes / 1024 / 1024 as "当前数据文件大小(MB)",
       ceil(HWM * a.block_size) / 1024 / 1024 as "可调整至大小(MB)",
       (a.bytes - HWM * a.block_size) / 1024 / 1024 AS "释放空间大小(MB)",
       'alter database datafile ''' || a.name || ''' resize ' ||
       ceil(ceil(HWM * a.block_size) / 1024 / 1024) || 'M;' as "SQL语句"
  from v$datafile a,
       (SELECT file_id, MAX(block_id + blocks - 1) HWM
          FROM DBA_EXTENTS
         GROUP BY file_id) b
 where a.file# = b.file_id(+)
   And (a.bytes - HWM * a.block_size) > 0
   and rownum < 30
 order by "释放空间大小(MB)" desc