--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