Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to deal with oracle sysaux table space full". In daily operation, I believe many people have doubts about how to deal with oracle sysaux table space full. Xiaobian consulted all kinds of data and sorted out simple and easy to use operation methods. I hope to help you answer the doubts of "oracle sysaux table space full"! Next, please follow the small series to learn together!

Query tablespaces with the following statement

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) dwhere d.tablespace_name = f.tablespace_nameorder by 5 desc;

Query usage of individual sysaux tablespaces

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 PARTITION7293WRH$_LATCH_MISSES_SUMMARY_PK INDEX PARTITION2664WRH$_LATCH_MISSES_SUMMARY TABLE PARTITION2336WRH$_EVENT_HISTOGRAM_PK INDEX PARTITION2087WRH$_EVENT_HISTOGRAM TABLE PARTITION1835WRH$_SQLSTAT TABLE PARTITION1690WRH$_LATCH TABLE PARTITION1101

Generating truncate statements

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; At this point, the study of "how to deal with oracle sysaux table space is full" is over, I hope to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.