sysaux表空间清理

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 ;