sysaux 表空間爆滿處理方法

--SysAUX表空間統計表佔用空間

select d.owner, d.segment_name, bytes / 1024 / 1024, d.segment_type

from dba_segments d

where d.tablespace_name = 'SYSAUX'

order by 3 desc

--檢視系統預設時間31天

select dbms_stats.get_stats_history_retention from dual;

--檢視統計資訊最早時間 08-6月 -22

select dbms_stats.get_stats_history_availability from dual;

--設定保留10天：

EXEC dbms_stats.alter_stats_history_retention(10);

--刪除十天之前的資料(該過程相當於delete操作，不會回收高水位線)

exec dbms_stats.purge_stats(sysdate-10);

1）先重新rebuild 佔空間大的索引，釋放空間在move 表，回收高水位線

alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online ;

2）move表回收高水位線

alter table sys.WRI$_OPTSTAT_HISTGRM_HISTORY move;

3)檢視索引

select * from dba_indexes where table_name='WRI$_OPTSTAT_HISTGRM_HISTORY'

I_WRI$_OPTSTAT_H_ST

I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST

3）move 表導致索引失效

alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online ;

alter index sys.I_WRI$_OPTSTAT_H_ST rebuild online ;

4）檢視錶空間

select a.tablespace_name,c.allocation_type,c.segment_space_management,

case mod(c.initial_extent,1024*1024) when 0 then c.initial_extent/1024/1024||'M'

else c.initial_extent/1024||'K' end initial_extent,

    a.total_Mbytes,a.total_Mbytes - b.free_Mbytes used_Mbytes,b.free_Mbytes,

trunc(b.free_Mbytes/a.total_Mbytes * 100,2) pct_free,null dummy

from (

select tablespace_name,sum(bytes)/1024/1024 total_MBytes

from dba_data_files

group by tablespace_name

) a,   (

select tablespace_name,sum(bytes)/1024/1024 free_Mbytes

from dba_free_space

group by tablespace_name

) b, dba_tablespaces c

where a.tablespace_name = b.tablespace_name(+) and a.tablespace_name = c.tablespace_name(+)

/

5）再次確認sysaux 大的物件

select d.owner, d.segment_name, bytes / 1024 / 1024, d.segment_type

from dba_segments d

where d.tablespace_name = 'SYSAUX' --and D.segment_type='TABLE'

order by 3 desc