admin管理员组文章数量:1532656
Sysaux表空间是system表空间的辅助表空间,他主要存放awr快照,审计信息等信息,默认情况下,sysaux表空间在日常使用的过程中会越来越大使用量,那么怎么才能安全有效的清理sysaux表空间呢?
col Tablespace_Name for a11
col Sum_m for 9999
col Max_m for 9999
col Free_Blk_Cnt for 9999
col Sum_Free_m for 9999
col PCT_USED for a8
col PCT_FREE for a10
Select Tablespace_Name,
Sum_m,
Max_m,
Count_Blocks Free_Blk_Cnt,
Sum_Free_m,
To_Char(100 * Sum_Free_m / Sum_m, '99.9999') || '%' As Pct_Free,
100 - To_Char(100 * Sum_Free_m / Sum_m, '99.9999') || '%' As Pct_used
From (Select Tablespace_Name, Sum(Bytes) / 1024 / 1024 As Sum_m
From Dba_Data_Files
Group By Tablespace_Name)
Left Join (Select Tablespace_Name As Fs_Ts_Name,
Max(Bytes) / 1024 / 1024 As Max_m,
Count(Blocks) As Count_Blocks,
Sum(Bytes / 1024 / 1024) As Sum_Free_m
From Dba_Free_Space
Group By Tablespace_Name)
On Tablespace_Name = Fs_Ts_Name
ORDER BY Sum_Free_m / Sum_m;
可以看到sysaux使用空间有260M左右,再去查询下sysaux表空间里具体存放了什么数据
col Item for a30
col Schema for a20
set lines 200
set pages 100
SELECT occupant_name"Item",
round(space_usage_kbytes/1024/1024,3)"Space Used (GB)",
schema_name "Schema",
move_procedure "MoveProcedure"
FROM v$sysaux_occupants
ORDER BY 2 Desc;
主要就是AWR的数据占用,长期没有清理的话,就有可能存在暴增的情况,那么怎么清理空间呢?有两种方式
方式一
适用于数据量不是很大的情况下,也是oracle官方给出的清理方案,利用存储过程进行清理(这里有一个问题 就是该存储过程其实执行的是delete语句,在存储过程执行过程中,会生成大量的归档,需评估以后慎用)
1、首先查出snap_id
select min(snap_id),max(snap_id) from dba_hist_snapshot ;
2、执行存储过程清除
EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(1, 150);
方式二
trucate基表
先查出sysaux基表信息,按照大小排序
col SEGMENT_NAME for a30
select * from
(select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc)
where rownum<=10;
查找WRH$表发现这些基表中有相同的字段snap_id,我们就利用这个字段进行处理,先利用snap_id进行大基表的备份。
select min(snap_id),max(snap_id) from dba_hist_snapshot ;
CREATE TABLE WRH$_ACTIVE_SESSION_HISTORY_B AS
SELECT * FROM WRH$_ACTIVE_SESSION_HISTORY WHERE SNAP_ID>150 ;
验证备份表信息
SELECT COUNT(*) FROM WRH$_ACTIVE_SESSION_HISTORY_B;
删除大的基表
TRUNCATE TABLE WRH$_ACTIVE_SESSION_HISTORY;
将备份数据恢复到源表
INSERT INTO WRH$_ACTIVE_SESSION_HISTORY SELECT * FROM WRH$_ACTIVE_SESSION_HISTORY_B;
COMMIT;
然后验证下数据是否写入到了源表中
select count(1) from wrh$_active_session_history;
最后删除备份临时表
drop table wrh$_active_session_history_b purge ;
再次查询表空间使用
col tablespace_name format a12 ;
col tablespace_name format a12 ;
select t1.tablespace_name "tablespace name",t1.flag "tablespace type",trunc(t1.bytes-nvl(t2.bytes,0),2) "usage(G)",
trunc(nvl(t2.bytes,0),2) "free(G)",trunc(t1.bytes,2) "tatal size(G)",100-round(100*nvl(t2.bytes,0)/t1.bytes,2) "usage(%)",
round(100*nvl(t2.bytes,0)/t1.bytes,2) "free(%)",trunc(t1.maxbytes,2) "maxextends(G)"
from (
SELECT tablespace_name,sum(d1.bytes)/1024/1024/1024bytes,'NORMAL' FLAG,
sum(decode(d1.autoextensible,'NO',d1.bytes,d1.maxbytes))/1024/1024/1024 maxbytes
FROM dba_data_files d1
GROUP BY tablespace_name
UNION all
SELECT tablespace_name,sum(d2.bytes)/1024/1024/1024bytes,'TEMP' FLAG,
sum(decode(d2.autoextensible,'NO',d2.bytes,d2.maxbytes))/1024/1024/1024 maxbytes
FROM dba_temp_files d2
GROUP BY tablespace_name ) t1,(
SELECT tablespace_name,sum(f.bytes)/1024/1024/1024 bytes
FROM dba_free_space f
GROUP BY tablespace_name ) t2
where t1.tablespace_name = t2.tablespace_name(+)
ORDER by t1.flag,t1.tablespace_name ;
版权声明:本文标题:sysaux表空间清理 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://m.elefans.com/dongtai/1726092810a1055156.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论