处理SM/AWR占用sysaux很大空间的问题

1.计算SYSAUX的occupants的大小

 

select *

  from (SELECT occupant_name "Item",

               trunc((space_usage_kbytes / 1048576), 2) "Space Used (GB)",

               schema_name "Schema",

               move_procedure "Move Procedure"

          FROM v$sysaux_occupants

         ORDER BY 2 desc)

 where rownum <= 10

 

2.查看无效的index

 

select * from dba_indexes where degree not in ('1','0','DEFAULT');

 

3.统计表空间大小

 

select    b.file_id as file_id,

          b.tablespace_name as tablespace_id,

          b.file_name as file_name,

          round(b.bytes / 1024 / 1024 ) as total_size,

          round((b.bytes / 1024 / 1024 ) - sum(nvl(a.bytes / 1024 / 1024 , 0))) as used_size,

          round(sum