用如下语句查询表空间
select upper(f.tablespace_name) "ts-name", d.tot_grootte_mb "ts-bytes(m)", d.tot_grootte_mb - f.total_bytes "ts-used (m)", f.total_bytes "ts-free(m)", to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100, 2), '990.99') "ts-per" from (select tablespace_name, round(sum(bytes) / (1024 * 1024), 2) total_bytes, round(max(bytes) / (1024 * 1024), 2) max_bytes from sys.dba_free_space group by tablespace_name) f, (select dd.tablespace_name, round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb from sys.dba_data_files dd group by dd.tablespace_name) d where d.tablespace_name = f.tablespace_name order by 5 desc;
查询各个sysaux表空间的使用情况
SQL> select * from (select segment_name, segment_type,bytes / 1024 / 1024 from dba_segments where tablespace_name = 'SYSAUX'and bytes / 1024 / 1024 >1000 order by bytes desc);
SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024 --------------------------------------------------------------------------------- ------------------ --------------- WRH$_ACTIVE_SESSION_HISTORY TABLE PARTITION7293 WRH$_LATCH_MISSES_SUMMARY_PK INDEX PARTITION2664 WRH$_LATCH_MISSES_SUMMARY TABLE PARTITION2336 WRH$_EVENT_HISTOGRAM_PK INDEX PARTITION2087 WRH$_EVENT_HISTOGRAM TABLE PARTITION1835 WRH$_SQLSTAT TABLE PARTITION1690 WRH$_LATCH TABLE PARTITION1101
生成truncate语句
select distinct 'truncate table '||segment_name||';',s.bytes/1024/1024
from dba_segments s
where s.segment_name like 'WRH$%'
and segment_type in ('TABLE PARTITION', 'TABLE')
and s.bytes/1024/1024>100
order by s.bytes/1024/1024/1024 desc;
truncate table WRH$_ACTIVE_SESSION_HISTORY; truncate table WRH$_ACTIVE_SESSION_HISTORY; truncate table WRH$_LATCH_MISSES_SUMMARY; truncate table WRH$_EVENT_HISTOGRAM; truncate table WRH$_SQLSTAT; truncate table WRH$_LATCH; truncate table WRH$_SYSSTAT; truncate table WRH$_SEG_STAT; truncate table WRH$_PARAMETER; truncate table WRH$_SYSTEM_EVENT; truncate table WRH$_SQL_PLAN; truncate table WRH$_DLM_MISC; truncate table WRH$_SERVICE_STAT; truncate table WRH$_ROWCACHE_SUMMARY; truncate table WRH$_TABLESPACE_STAT; truncate table WRH$_MVPARAMETER;
