1 巡检时，发现SYSAUX表空间占用空间大，且ASM磁盘没有多余的空间 TABLESPACE_NAME TOT_SIZE TOT_FREE PCT_FREE MAX_FREE CHUNKS_FREE------------------------------ ---------------- ---------------- ---------------- ---------------- ----------------SYSAUX 41,060 16,901 41 2,303 593 2 使用如下语句，查看哪些对象占用sysaux表空间多，发现基本都是AWR相关的表及索引占用空间多，一般AWR会保留8天的数据，会频繁的删除数据及插入数据，故有可能导致表中有大量的空数据块。，故准备对表进行收缩，来验证是否能够收缩空间。 select OWNER,SEGMENT_NAME,SEGMENT_TYPE,trunc(sum(BYTES)/1024/1024) from dba_segments where tablespace_name='SYSAUX' group by OWNER,SEGMENT_NAME,SEGMENT_TYPE having(trunc(sum(BYTES)/1024/1024)) > 300 order by SEGMENT_TYPE,trunc(sum(BYTES)/1024/1024) ; OWNER SEGMENT_NAME SEGMENT_TYPE TRUNC(SUM(BYTES)/1024/1024) -------------------- ---------------------------------------- -------------------- --------------------------- SYS WRH$_DLM_MISC_PK INDEX PARTITION 344 SYS WRH$_SEG_STAT_PK INDEX PARTITION 360 SYS WRH$_SQLSTAT_PK INDEX PARTITION 456 SYS WRH$_SERVICE_STAT_PK INDEX PARTITION 496 SYS WRH$_SYSTEM_EVENT_PK INDEX PARTITION 824 SYS WRH$_PARAMETER_PK INDEX PARTITION 1152 SYS WRH$_LATCH_PK INDEX PARTITION 1664 SYS WRH$_SYSSTAT_PK INDEX PARTITION 2048 SYS WRH$_LATCH_MISSES_SUMMARY_PK INDEX PARTITION 2496 SYS SYS_LOB0000006409C00004$$ LOBSEGMENT 376 SYS WRH$_DLM_MISC TABLE PARTITION 392 SYS WRH$_SYSTEM_EVENT TABLE PARTITION 843 SYS WRH$_SEG_STAT TABLE PARTITION 872 SYS WRH$_PARAMETER TABLE PARTITION 936 SYS WRH$_SYSSTAT TABLE PARTITION 1280 SYS WRH$_SQLSTAT TABLE PARTITION 1728 SYS WRH$_LATCH_MISSES_SUMMARY TABLE PARTITION 1857 SYS WRH$_LATCH TABLE PARTITION 2049 18 rows selected. 3 对AWR相关的表进行收缩，来验证是否能够收缩空间
OWNER SEGMENT_NAME SEGMENT_TYPE TRUNC(SUM(BYTES)/1024/1024)
-------------------- ---------------------------------------- -------------------- ---------------------------SYS WRH$_LATCH TABLE PARTITION 2049 执行收缩：alter table sys.WRH$_LATCH enable row movement;alter table sys.WRH$_LATCH shrink space cascade;alter table sys.WRH$_LATCH disable row movement; 查看表占用的空间，从2049M 收缩到25M，释放了2024M的空间，效果还是蛮不错的
OWNER SEGMENT_NAME SEGMENT_TYPE TRUNC(SUM(BYTES)/1024/1024)
-------------------- ---------------------------------------- -------------------- ---------------------------SYS WRH$_LATCH TABLE PARTITION 25 4 将其它表都进行收缩，sysaux表空间的空间释放了有20G，如下： TABLESPACE_NAME TOT_SIZE TOT_FREE PCT_FREE MAX_FREE CHUNKS_FREE------------------------------ ---------------- ---------------- ---------------- ---------------- ----------------SYSAUX 41,060 36,065 88 2,303 1,060
