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

AWR佔用sysaux表空間太大

相關文章