SYSAUX表空间被称为系统辅助表空间,是10g版本开始推出的新功能,主要的目的是为SYSTEM表空间减负,Oracle对SYSTEM表空间的维护有一套独立的体系,对SYSTEM表空间操作会占用额外的CPU资源,而且效率低下,在10g版本,增加了SYSAUX辅助表空间,将EM、AWR等组件的表从SYSTEM表空间挪到了SYSAUX表空间中,这样大大减少了SYSTEM表空间的消耗,也减少了Oracle对SYSTEM表空间维护的成本。
但是有几个比较坑爹的组件需要的表并没有挪到SYSAUX表空间,比如常见的审计用到的AUD$表,很多DBA都可能遇到SYSTEM表空间使用率过高,查询发现是AUD$表很大导致的,我们的数据库正好打开了审计功能,正好可以拿来做实验。下面先查询下SYSTEM和SYSAUX表空间的使用率。
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');
结果:
1 SYSAUX 684.69M 87640 598.56M 87.42% 86.13M
2 SYSTEM 12681M 1623168 11243.95M 88.67% 1437.05M
oracle数据库清理和回收system和sysaux表空间
http://m.blog.itpub.net/26736162/viewspace-2140644/
https://www.cnblogs.com/guilingyang/p/5753260.html
http://www.jiagulun.com/thread-271819-1-1.html