oracle数据库清理和回收system和sysaux表空间
前几天和一个网友讨论了下SYSAUX表空间使用率过高的问题，今天有时间整理一下，正好我们的测试数据库也存在这个问题。本案例数据库版本为11.2.0.4.0。
SYSAUX表空间被称为系统辅助表空间，是10g版本开始推出的新功能，主要的目的是为SYSTEM表空间减负，Oracle对SYSTEM表空间的维护有一套独立的体系，对SYSTEM表空间操作会占用额外的CPU资源，而且效率低下，详见我之前发表的文章为什么不要把用户表存储到SYSTEM表空间。在10g版本，增加了SYSAUX辅助表空间，将EM、AWR等组件的表从SYSTEM表空间挪到了SYSAUX表空间中，这样大大减少了SYSTEM表空间的消耗，也减少了Oracle对SYSTEM表空间维护的成本。
但是有几个比较坑爹的组件需要的表并没有挪到SYSAUX表空间，比如常见的审计用到的AUD$表，很多DBA都可能遇到SYSTEM表空间使用率过高，查询发现是AUD$表很大导致的，我们的数据库正好打开了审计功能，正好可以拿来做实验。
下面先查询下SYSTEM和SYSAUX表空间的使用率。
sys@IVLDB> SELECT * FROM (
2 SELECT D.TABLESPACE_NAME,
3 SPACE || 'M' "SUM_SPACE(M)",
4 BLOCKS "SUM_BLOCKS",
5 SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
6 ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
7 "USED_RATE(%)",
8 FREE_SPACE || 'M' "FREE_SPACE(M)"
9 FROM ( SELECT TABLESPACE_NAME,
10 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
11 SUM (BLOCKS) BLOCKS
12 FROM DBA_DATA_FILES
13 GROUP BY TABLESPACE_NAME) D,
14 ( SELECT TABLESPACE_NAME,
15 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
16 FROM DBA_FREE_SPACE
17 GROUP BY TABLESPACE_NAME) F
18 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
19 UNION ALL
20 SELECT D.TABLESPACE_NAME,
21 SPACE || 'M' "SUM_SPACE(M)",
22 BLOCKS SUM_BLOCKS,
23 USED_SPACE || 'M' "USED_SPACE(M)",
24 ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
25 NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
26 FROM ( SELECT TABLESPACE_NAME,
27 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
28 SUM (BLOCKS) BLOCKS
29 FROM DBA_TEMP_FILES
30 GROUP BY TABLESPACE_NAME) D,
31 ( SELECT TABLESPACE_NAME,
32 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
33 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
34 FROM V$TEMP_SPACE_HEADER
35 GROUP BY TABLESPACE_NAME) F
36 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
37 ORDER BY 1)
38 WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM');
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
----------------- -------------- ---------- --------------- --------------- ---------------
SYSAUX 22156M 2835968 21126.81M 95.35% 1029.19M
SYSTEM 8686M 1111808 8251.7M 94.99% 434.3M
可见，SYSAUX表空间已经使用了21GB左右，SYSTEM表空间已经使用了8GB左右，下面查看下使用SYSTEM和SYSAUX表空间的比较大的表有哪些。
sys@IVLDB> select * from (
2 select segment_name,sum(bytes)/1024/1024 total_mb,tablespace_name from dba_segments where tablespace_name in
('SYSTEM','SYSAUX') group by segment_name,tablespace_name order by 2 desc)
3 where rownum <=20;
SEGMENT_NAME TOTAL_MB TABLESPACE_NAME
------------------------------------------------- ---------- --------------
AUD$ 6680 SYSTEM
WRH$_ACTIVE_SESSION_HISTORY 5248.0625 SYSAUX
WRH$_EVENT_HISTOGRAM_PK 2499.0625 SYSAUX
WRH$_EVENT_HISTOGRAM 1794.0625 SYSAUX
WRH$_LATCH_MISSES_SUMMARY_PK 905.0625 SYSAUX
WRH$_SQLSTAT 816.0625 SYSAUX
WRH$_LATCH 800.0625 SYSAUX
C_OBJ#_INTCOL# 768 SYSTEM
WRH$_LATCH_MISSES_SUMMARY 760.0625 SYSAUX
WRH$_ACTIVE_SESSION_HISTORY_PK 712.0625 SYSAUX
WRH$_SYSSTAT_PK 672.0625 SYSAUX
WRH$_LATCH_PK 560.0625 SYSAUX
WRH$_SYSSTAT 504.0625 SYSAUX
I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST 456 SYSAUX
WRH$_PARAMETER_PK 454.0625 SYSAUX
WRH$_SEG_STAT 408.0625 SYSAUX
WRH$_PARAMETER 384.0625 SYSAUX
WRH$_SYSTEM_EVENT 368.0625 SYSAUX
WRI$_OPTSTAT_HISTGRM_HISTORY 312 SYSAUX
I_H_OBJ#_COL# 312 SYSTEM
可见，大表大部分都是AUD$和WRH$开头的AWR基表，AUD$使用SYSTEM表空间，AWR的基表使用SYSAUX表空间，下面再查看下SYSAUX表空间的使用情况，可以通过v$sysaux_occupants视图查询到。
sys@IVLDB> SELECT occupant_name "Item", 2 space_usage_kbytes / 1048576 "Space Used (GB)", 3 schema_name "Schema", 4 move_procedure "Move Procedure" 5 FROM v$sysaux_occupants 6 ORDER BY 1 ; Item Space Used (GB) Schema Move Procedure ------------------------------ --------------- -------------------- ----------------------------------- AO .038391113 SYS DBMS_AW.MOVE_AWMETA AUDIT_TABLES 0 SYS DBMS_AUDIT_MGMT.move_dbaudit_tables AUTO_TASK .000366211 SYS EM 0 SYSMAN emd_maintenance.move_em_tblspc EM_MONITORING_USER .001708984 DBSNMP EXPRESSION_FILTER .003540039 EXFSYS JOB_SCHEDULER .010498047 SYS LOGMNR .013061523 SYSTEM SYS.DBMS_LOGMNR_D.SET_TABLESPACE LOGSTDBY .001342773 SYSTEM SYS.DBMS_LOGSTDBY.SET_TABLESPACE ORDIM .000427246 ORDSYS ordsys.ord_admin.move_ordim_tblspc ORDIM/ORDDATA .013244629 ORDDATA ordsys.ord_admin.move_ordim_tblspc ORDIM/ORDPLUGINS 0 ORDPLUGINS ordsys.ord_admin.move_ordim_tblspc ORDIM/SI_INFORMTN_SCHEMA 0 SI_INFORMTN_SCHEMA ordsys.ord_admin.move_ordim_tblspc PL/SCOPE .001525879 SYS SDO .064758301 MDSYS MDSYS.MOVE_SDO SM/ADVISOR .199707031 SYS SM/AWR 18.8637695 SYS SM/OPTSTAT 1.14306641 SYS SM/OTHER .012268066 SYS SMON_SCN_TIME .008178711 SYS SQL_MANAGEMENT_BASE .001647949 SYS STATSPACK 0 PERFSTAT STREAMS .000976563 SYS TEXT .003540039 CTXSYS DRI_MOVE_CTXSYS TSM 0 TSMSYS ULTRASEARCH 0 WKSYS MOVE_WK ULTRASEARCH_DEMO_USER 0 WK_TEST MOVE_WK WM .003417969 WMSYS DBMS_WM.move_proc XDB .123962402 XDB XDB.DBMS_XDB.MOVEXDB_TABLESPACE XSAMD .005004883 OLAPSYS DBMS_AMD.Move_OLAP_Catalog XSOQHIST .038391113 SYS DBMS_XSOQ.OlapiMoveProc 31 rows selected.
可见SM/AWR组件就使用了将近19GB的SYSAUX表空间，也就是说审计和AWR占用了大量的SYSTEM和SYSAUX表空间，而这些数据是可以定期清理的，都没有必要保留太长的时间。
下面先清理审计的数据，如果要保留部分AUD$里面记录的审计数据，可以把想要的数据插入到一张临时表，然后直接truncate这张表就可以了，truncate操作会直接回收AUD$占用的空间。
sys@IVLDB> truncate table AUD$;
Table truncated.
sys@IVLDB> select bytes/1024/1024 from dba_segments where segment_name='AUD$';
BYTES/1024/1024
---------------
5
可见，truncate这张表之后，6680M的空间直接降为5M，释放了大量的SYSTEM表空间的空间。下面再来回收下SYSAUX表空间，这个相对比较麻烦，也比较耗时。
如上文所示，通过查看v$sysaux_occupants视图，可以确定占用SYSAUX表空间过多的大部分都是AWR的基表，这样只要删除部分AWR数据理论上就可以回收一部分SYSAUX表空间，通常AWR的数据都会设置保留期限，10g版本默认保留7天，11g版本默认保留8天，可以通过dba_hist_wr_control视图来查看(注：并不是所有DBA开头的表都是数据字典，也有很多是视图，dba_hist_wr_control就是视图)。
sys@IVLDB> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- ------------------- ------------------- ----------
1357933872 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
大家可能会有疑问了，AWR的数据既然只保留七八天，为什么还会占用这么多的SYSAUX表空间呢？这个问题我个人认为主要有以下两个原因，首先，AWR删除过期的数据是通过DELETE操作完成的，这样就会产生大量的碎片，特别是SYSAUX表空间存在自动扩展的数据文件，而且这个数据文件没有扩展到最大，还有扩展的空间情况下会很明显，其次就是ASH的数据有些情况下是不受AWR的保留策略影响的，这个从下面的SQL就可以看出。
sys@IVLDB> select count(*) from WRH$_ACTIVE_SESSION_HISTORY;
COUNT(*)
----------
16918966
sys@IVLDB> select min(snap_id),max(snap_id) from wrh$_active_session_history;
MIN(SNAP_ID) MAX(SNAP_ID)
------------ ------------
1 15533
可以看到，ASH的数据从第一个快照开始一直都在保留，导致WRH$_ACTIVE_SESSION_HISTORY表很大，使用DBMS_WORKLOAD_REPOSITORY包清理过期或者不需要的AWR数据，可以回收这部分空间。
sys@IVLDB> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id =>1,high_snap_id => 15500); PL/SQL procedure successfully completed.
清理了AWR数据之后，你会发现SYSAUX表空间的空间并没有被回收，使用率还和之前一样，这是因为清理AWR操作是通过DELETE操作实现的，表的水位线并没有下降导致的。
sys@IVLDB> SELECT occupant_name "Item", 2 space_usage_kbytes / 1048576 "Space Used (GB)", 3 schema_name "Schema", 4 move_procedure "Move Procedure" 5 FROM v$sysaux_occupants 6 where occupant_name='SM/AWR'; Item Space Used (GB) Schema Move Procedure -------------- --------------- -------------------- ------------------ SM/AWR 18.8638306 SYS
AWR的数据还是使用将近19GB的空间，查询SYSTEM和SYSAUX表空间的使用率会发现SYSTEM表空间的使用率已经降低了很多，因为上文TRUNCATE了AUD$表，这张表使用的是SYSTEM表空间，上文删除了很多AWR的数据，AWR的数据使用SYSAUX表空间，并没有回收。
sys@IVLDB> SELECT * FROM (
2 SELECT D.TABLESPACE_NAME,
3 SPACE || 'M' "SUM_SPACE(M)",
4 BLOCKS "SUM_BLOCKS",
5 SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
6 ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
7 "USED_RATE(%)",
8 FREE_SPACE || 'M' "FREE_SPACE(M)"
9 FROM ( SELECT TABLESPACE_NAME,
10 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
11 SUM (BLOCKS) BLOCKS
12 FROM DBA_DATA_FILES
13 GROUP BY TABLESPACE_NAME) D,
14 ( SELECT TABLESPACE_NAME,
15 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE
16 FROM DBA_FREE_SPACE
17 GROUP BY TABLESPACE_NAME) F
18 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
19 UNION ALL
20 SELECT D.TABLESPACE_NAME,
21 SPACE || 'M' "SUM_SPACE(M)",
22 BLOCKS SUM_BLOCKS,
23 USED_SPACE || 'M' "USED_SPACE(M)",
24 ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
25 NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
26 FROM ( SELECT TABLESPACE_NAME,
27 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
28 SUM (BLOCKS) BLOCKS
29 FROM DBA_TEMP_FILES
30 GROUP BY TABLESPACE_NAME) D,
31 ( SELECT TABLESPACE_NAME,
32 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
33 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
34 FROM V$TEMP_SPACE_HEADER
35 GROUP BY TABLESPACE_NAME) F
36 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
37 ORDER BY 1)
38 WHERE TABLESPACE_NAME IN ('SYSAUX','SYSTEM');
TABLESPACE_NAME SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)
-------------------- --------------- ---------- --------------- ------------- --------------
SYSAUX 22156M 2835968 21126.81M 95.35% 1029.19M
SYSTEM 8686M 1111808 1990.25M 22.91% 6695.75M
查询WRH$_ACTIVE_SESSION_HISTORY表会发现数据已经被删除了很多，从删除之前的16918966条记录变为删除后的4706条记录，但是表的大小没变，还是5GB多。
sys@IVLDB> select count(*) from WRH$_ACTIVE_SESSION_HISTORY;
COUNT(*)
----------
4706
下面通过MOVE操作回收这个表的水位线，来回收这部分被删除数据占用的空间。这个表是分区表，分区表不支持表级别的MOVE操作，直接对分区表进行MOVE操作会遇到ORA-14511错误。
sys@IVLDB> alter table WRH$_ACTIVE_SESSION_HISTORY move;
alter table WRH$_ACTIVE_SESSION_HISTORY move
*
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object
查看这个表的分区信息，只有两个分区。
sys@IVLDB> select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY'; SEGMENT_NAME PARTITION_NAME GB ------------------------------ ------------------------------ ---------- WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1357933872_0 5.125 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN .000061035
下面按照分区进行MOVE操作，来回收空间。
sys@IVLDB> alter table WRH$_ACTIVE_SESSION_HISTORY move partition WRH$_ACTIVE_1357933872_0; Table altered. sys@IVLDB> alter table WRH$_ACTIVE_SESSION_HISTORY move partition WRH$_ACTIVE_SES_MXDB_MXSN; Table altered.
对分区表进行MOVE之后，需要重建索引，查看这个表的索引信息。
sys@IVLDB> select index_name from dba_indexes where table_name='WRH$_ACTIVE_SESSION_HISTORY'; INDEX_NAME ------------------------------ WRH$_ACTIVE_SESSION_HISTORY_PK
这个表只有一个主键，而且是分区索引，也不能对分区索引直接进行REBUILD操作，否则会遇到ORA-14086错误。
sys@IVLDB> alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild;
alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
下面查看下这个索引的分区信息。
sys@IVLDB> select partition_name from dba_ind_partitions where index_name='WRH$_ACTIVE_SESSION_HISTORY_PK'; PARTITION_NAME ------------------------------ WRH$_ACTIVE_1357933872_0 WRH$_ACTIVE_SES_MXDB_MXSN
然后按照分区进行重建索引。
sys@IVLDB> alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild partition WRH$_ACTIVE_1357933872_0; Index altered. sys@IVLDB> alter index WRH$_ACTIVE_SESSION_HISTORY_PK rebuild partition WRH$_ACTIVE_SES_MXDB_MXSN; Index altered.
再次查看，WRH$_ACTIVE_SESSION_HISTORY的空间已经回收。
sys@IVLDB> select sum(bytes)/1024/1024 from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY';
SUM(BYTES)/1024/1024
--------------------
2.0625
sys@IVLDB> select sum(bytes)/1024/1024 from dba_segments where segment_name='WRH$_ACTIVE_SESSION_HISTORY_PK';
SUM(BYTES)/1024/1024
--------------------
.3125
可见，这个表大小由之前的5248.0625MB将为2.0625MB，重建索引后，索引的大小也由之前的712.0625MB降为了0.3125MB，这一张表就回收了6GB左右的空间。
sys@IVLDB> SELECT occupant_name "Item", 2 space_usage_kbytes / 1048576 "Space Used (GB)", 3 schema_name "Schema", 4 move_procedure "Move Procedure" 5 FROM v$sysaux_occupants 6 where occupant_name='SM/AWR'; Item Space Used (GB) Schema Move Procedure -------------- --------------- -------------------- ------------------ SM/AWR 13.0436401 SYS
通过v$sysaux_occupants视图，可以查询到AWR占用空间由之前的将近19GB降为了不到13GB。按照同样的方法，回收下WRH$_EVENT_HISTOGRAM表的空间，这也是分区表表1794MB，只有一个索引(是主键)2499MB。
sys@IVLDB> select partition_name from dba_tab_partitions where table_name='WRH$_EVENT_HISTOGRAM'; PARTITION_NAME ------------------------------ WRH$_EVENT_HISTO_MXDB_MXSN WRH$_EVENT__1357933872_0 sys@IVLDB> alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT_HISTO_MXDB_MXSN; Table altered. sys@IVLDB> alter table WRH$_EVENT_HISTOGRAM move partition WRH$_EVENT__1357933872_0; Table altered. sys@IVLDB> select index_name from dba_indexes where table_name='WRH$_EVENT_HISTOGRAM'; INDEX_NAME ------------------------------ WRH$_EVENT_HISTOGRAM_PK sys@IVLDB> select partition_name from dba_ind_partitions where index_name='WRH$_EVENT_HISTOGRAM_PK'; PARTITION_NAME ------------------------------ WRH$_EVENT_HISTO_MXDB_MXSN WRH$_EVENT__1357933872_0 sys@IVLDB> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT_HISTO_MXDB_MXSN; Index altered. sys@IVLDB> alter index WRH$_EVENT_HISTOGRAM_PK rebuild partition WRH$_EVENT__1357933872_0; Index altered.
清理WRH$_ACTIVE_SESSION_HISTORY和WRH$_EVENT_HISTOGRAM两张表，SYSAUX表空间就释放出10GB的空间，其他占用SYSAUX空间比较大的表也可以安装这样的方法去释放空间，这里就不一一演示了。
可能有人会问了，既然已经删除了数据，那么如果不回收这部分空间，ORACLE就不会再使用这部分空间了吗？为什么非要回收呢？这个高水位线的问题主要影响以下几个方面，不只是使用SYSAUX表空间的表，对所有碎片较多的表都适用。
1.影响查询速度，因为这样的表本身比较大，索引也会很大，查询会很慢。
2.消耗资源，因为表和索引都很大，查询时会消耗很多I/O资源。
3.空间占用，虽然大部分数据被DELETE掉了，但是这部分空间仍旧是这个段(SEGMENT)的区(EXTENT)，即使可以再利用也只能是这个表的新增数据才可以使用，而且还得是所在表空间无法自动扩展或者没有足够的扩展空间的情况下，如果表空间可以自动扩展或者有足够的扩展空间，那么还是不会使用这部分空间的，这就导致表和索引会越来越大，占用的空间越来越大，而一旦将可扩展的空间用尽，那么其他表将无法扩展，DELETE掉的数据空间，其他对象是无法使用的。
除了AWR的保留策略会影响SYSAUX表空间的使用率外，AWR收集数据的级别也对SYSAUX表空间的使用率影响很大，AWR收集数据的级别由statistics_level参数控制，这个参数有三个值，BASIC、TYPICAL、ALL，BASIC表示关闭统计信息收集，TYPICAL表示普通收集级别，只收集够日常用的统计信息，ALL是最给力的，凡是ORACLE能收集的所有信息都要收集，所以生成数据量会很大，相对来说，对性能和占用空间的影响也是最大的，通常TYPICAL就已经够用了。
不同的版本statistics_level参数的默认值不同，有的版本默认值为ALL，有的版本默认值为TYPICAL，具体哪些版本使用ALL为默认值，我记不清了，如果您的数据库设置statistics_level参数的值为ALL，建议调整为TYPICAL。
sys@IVLDB> show parameter statistics_level NAME TYPE VALUE ------------------------------------ ----------- --------- statistics_level string TYPICAL
如果您的数据库也遇到了SYSAUX表空间很大的情况，建议在清理AWR数据时，回收这部分空间，对SYSAUX表空间的对象操作，基本不会影响数据库的正常使用，SYSAUX表空间存放的对象都是数据库运行非必须的对象，技术这个表空间损坏或者丢失，数据库一样可以正常运行。
oracle数据库清理和回收system和sysaux表空间：等您坐沙发呢！
发表评论
