近期一客户19c RAC CDB数据库的SYSAUX表空间增长超大,分析原因为Optimizer statistics advisor特性导致的WRI$_ADV_OBJECTS对象记录数变多, 以下为清理方法。
1, 找出最大对象
SQL> set lines 120
SQL> col occupant_name format a30
SQL> select occupant_name,space_usage_kbytes from v$sysaux_occupants order by space_usage_kbytes desc;
或
prompt
prompt List of TOP 10 largest objects in SYSTEM AND SYSAUX TABLESPACE:
prompt
select * from (
select tablespace_name,topseg_seg_owner,topseg_segment_name,segment_type,mb,partitions, row_number() over(partition by tablespace_name order by mb desc) rn from (
select
tablespace_name,
owner topseg_seg_owner,
segment_name topseg_segment_name,
--partition_name,
segment_type,
round(SUM(bytes/1048576)) MB,
case when count(*) >= 1 then count(*) else null end partitions
from dba_segments</