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

This article will explain in detail what is the SYSAUX table space cleaning method for Oracle database standards. The content of the article is of high quality. Therefore, Xiaobian shares it with you for reference. I hope that after reading this article, you will have a certain understanding of relevant knowledge.

overview

SYSAUX table space is called system auxiliary table space, which is a new function introduced since version 10g. Its main purpose is to reduce the burden of SYSTEM table space. Oracle has a set of independent system for maintaining SYSTEM table space. The operation of SYSTEM table space will occupy extra CPU resources and is inefficient. In version 10g, SYSAUX auxiliary table space is added, and tables of EM, AWR and other components are moved from SYSTEM table space to SYSAUX table space, which greatly reduces the consumption of SYSTEM table space and also reduces Oracle's maintenance cost for SYSTEM table space.

The following is a recent procedure for cleaning up sysaux tablespaces, for reference only.

1. Query usage of SYSTEM and SYSAUX tablespaces

SELECT * FROM (SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS "SUM_BLOCKS", SPACE - NVL(FREE_SPACE, 0) || 'M' "USED_SPACE(M)", ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)", FREE_SPACE || 'M' "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE, SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL SELECT D.TABLESPACE_NAME, SPACE || 'M' "SUM_SPACE(M)", BLOCKS SUM_BLOCKS, USED_SPACE || 'M' "USED_SPACE(M)", ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE, SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 1) WHERE TABLESPACE_NAME IN ('SYSAUX', 'SYSTEM');

It can be seen that the SYSAUX table space has been used for about 43GB, and the SYSTEM table space has been used for about 3 GB.

2. Check out the larger tables using the SYSTEM and SYSAUX tablespaces

select * from (select segment_name,PARTITION_NAME, sum(bytes) / 1024 / 1024 total_mb, tablespace_name from dba_segments where tablespace_name in ('SYSTEM', 'SYSAUX') group by segment_name, tablespace_name order by 3 desc) where rownum 1,high_snap_id => 25100); select count(*) from WRH$_ACTIVE_SESSION_HISTORY;

After cleaning up AWR data, you can find that the space in SYSAUX table space has not been reclaimed, and the utilization rate is still the same as before. This is because the cleaning of AWR operation is realized through Delete operation, and the water mark of the table has not dropped.

5. Manually generate truncate, which needs to be executed under SYS

select distinct 'truncate table ' || segment_name || ';', s.bytes/1024/1024 MB 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 desc;

Actual execution sql: From the above statement, you can see that basically all the paragraphs starting with WRH$_, these types of paragraphs are basically related to AWR, and the following are executed by sys users

truncate table WRH$_SQL_BIND_METADATA; --save AWR collection SQL binding information table truncate table WRH$_ACTIVE_SESSION_HISTORY; --save AWR collection history session information table select segment_name,partition_name,bytes/1024/1024 gb from dba_segments where segment_name in ('WRH$_LATCH','WRH$_SQLSTAT'); alter table WRH$_LATCH truncate partition WRH$_LATCH_1013373590_0; alter table WRH$_SQLSTAT truncate partition WRH$_SQLSTA_1013373590_0; truncate table WRI$_ADV_SQLT_PLANS;- Save AWR collection SQL suggestion plan information table alter table WRH$_SYSSTAT shrink space; alter index WRH$_SYSSTAT_PK shrink space; alter table WRH$_LATCH shrink space; alter table WRH$_SEG_STAT shrink space; alter table WRH$_SQLSTAT shrink space; alter table WRH$_PARAMETER shrink space; --Save AWR collection parameter information table alter index WRH $_PARAMETER_PK shrink space; truncate table WRH$_EVENT_HISTOGRAM; truncate table WRH$_SQL_PLAN; --Save SQL execution plan table collected by AWR truncate table WRH$_SQLTEXT;--Save SQL text table collected by AWR

6. verification

Demand met, over.

About Oracle database standard SYSAUX table space cleaning method is what to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.