sysaux表空间清理

最新推荐文章于 2024-04-09 19:29:27 发布

冥净

最新推荐文章于 2024-04-09 19:29:27 发布

阅读量553

点赞数 4

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/weixin_45833902/article/details/136344884

版权

文章介绍了如何通过SQL查询监控SYSaux和SYSTEM表空间的使用情况，重点关注大表和AWR相关表的空间占用。同时提到了清理临时表空间、审计日志以及使用DBMS_AUDIT_MGMT清理统一审计轨迹的方法。

摘要由CSDN通过智能技术生成

确认当前 sysaux表空间的使用率

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');

2.查看下使用SYSTEM和SYSAUX表空间的比较大的表
可见，大表大部分都是AUD和WRH开头的AWR基表，AUD$使用SYSTEM表空间，AWR的基表使用SYSAUX表空间

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, PARTITION_NAME,tablespace_name 
 order by 3 desc) 
 where rownum <= 20;

查看SYSAUX表空间的具体使用情况通过v$sysaux_occupants视图查询

SELECT occupant_name "Item", 
 space_usage_kbytes / 1048576 "Space Used (GB)", 
 schema_name "Schema", 
 move_procedure "Move Procedure" 
 FROM v$sysaux_occupants 
 ORDER BY space_usage_kbytes desc;

查看 awr 的保留期限
通常AWR的数据都会设置保留期限，10g版本默认保留7天，11g版本默认保留8天，可以通过dba_hist_wr_control视图来查看

select * from dba_hist_wr_control;

手工生成truncate，需要在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; 

 --------------------------------------------------------------

truncate table WRH$_SQL_BIND_METADATA; —-保存AWR收集SQL绑定信息表 
truncate table WRH$_ACTIVE_SESSION_HISTORY; ——保存AWR收集历史会话信息表 

select segment_name,partition_name,bytes/1024/1024/1024 gb from dba_segments where segment_name in('WRH$_LATCH','WRH$_SQLSTAT');

SEGMENT_NAME                                                                      PARTITION_NAME                 GB
--------------------------------------------------------------------------------- ------------------------------ ----------
WRH$_LATCH                                                                        WRH$_LATCH_1923904796_0            1.1875
WRH$_SQLSTAT                                                                      WRH$_SQLSTA_1923904796_0           .90625
WRH$_LATCH                                                                        WRH$_LATCH_MXDB_MXSN           .000061035
WRH$_SQLSTAT                                                                      WRH$_SQLSTAT_MXDB_MXSN         .000061035

alter table WRH$_LATCH truncate partition WRH$_LATCH_1923904796_0; 
alter table WRH$_SQLSTAT truncate partition WRH$_SQLSTA_1923904796_0; 


truncate table WRI$_ADV_SQLT_PLANS; —保存AWR收集SQL建议计划信息表 
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; —-保存AWR收集参数信息表 
alter index WRH$_PARAMETER_PK shrink space;  

truncate table WRH$_EVENT_HISTOGRAM; 
truncate table WRH$_SQL_PLAN; —-保存AWR收集的SQL执行计划表 
truncate table WRH$_SQLTEXT; –—保存AWR收集的SQL文本表

3、清空审计表：

truncate table audsys.aud$unified
*
ERROR at line 1:
ORA-46385: DML and DDL operations are not allowed on table "AUDSYS"."AUD$UNIFIED".

4、用以下办法成功

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
container => DBMS_AUDIT_MGMT.CONTAINER_CURRENT,
use_last_arch_timestamp => FALSE);
END;
/