处理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(nvl(a.byt