1、需求背景
通过zmc日常监控发现oracle rac数据库的syaux表空间使用率达到91.6%。
进一步查询该表空间的大表对象：
可以看到占用前三的大对象，都是由统计信息导致的。1、3行是索引，第2行是表，而1,3行都是第2行的表上面的索引。
2、清理思路
由metlink可知，oracle会通过dbms_stats.purge_stats自动定时清理统计数据，但是dbms_stats.purge_stats包清理的时候是使用的delete语句，这样长久以往，会导致表的高水位不断上涨而不降，同时索引也会导致大量碎片。所以针对这个逻辑，我们清理的时候就要考虑降表的高水位，而针对索引就需要shrink或者rebuild。
3、执行步骤
3.1、前提条件检查
3.1.1、确认该大表对应索引
select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,STATUS from dba_indexes t where t.table_name='WRI$_OPTSTAT_HISTGRM_HISTORY';
3.1.2、确认users表空间大小足够
由于我们降表的高水位是使用的move，所以我们需要借助一个中间表空间，先将WRI$_OPTSTAT_HISTGRM_HISTORY该表move到一个新的表空间中，然后在move回来。而该中间表空间我们使用users。由于sysaux表空间并不大，所以对中间表空间的空间要求并不大，你也可以选取一个任意空间足够的业务表空间来操作，但是注意，如果使用非users表空间，注意要改下面的执行脚本中的users表空间为你指定的业务表空间。确认了tab_cc表空间比较大，所以如下脚本调整使用了tab_cc表空间。
3.1.3、确认收集统计时间
set lines 200
col WINDOW_NAME for a20
col REPEAT_INTERVAL for a60
col DURATION for a20
SELECT w.window_name,
w.repeat_interval,
w.duration,
w.enabled
FROM dba_autotask_window_clients c, dba_scheduler_windows w
WHERE c.window_name = w.window_name AND c.optimizer_stats = 'ENABLED';
oracle12.2默认自动收集统计信息是周一到周五晚上10点，持续4个小时收集统计信息，周六周日每天6点，持续20个小时收集统计信息。执行该清理操作，最好避开这两个时间。
3.2、清理脚本
该脚本需要使用sys用户以dba的身份登录执行。
执行前查询表空间使用率：
[email protected][/home/oraclerac]$export ORACLE_SID=test1
[email protected][/home/oraclerac]$sqlplus / as sysdba
set linesize 1000
col tablespace_name format a20 ;
select t1.tablespace_name "tablespace_name",t1.flag "type",trunc(t1.bytes-nvl(t2.bytes,0),2) "used space(G)",
trunc(nvl(t2.bytes,0),2) "free space(G)",trunc(t1.bytes,2) "sum space(G)",100-round(100*nvl(t2.bytes,0)/t1.bytes,2) "used pct",
round(100*nvl(t2.bytes,0)/t1.bytes,2) "free pct",trunc(t1.maxbytes,2) "max extend space"
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表空间大对象使用情况：
set linesize 1000
col segment_name format a40
col segment_type format a20
col total_mb format 99999.99
select *
from (select segment_name, SEGMENT_TYPE, sum(bytes) / 1024 / 1024 total_mb
from dba_segments
where tablespace_name = 'SYSAUX'
group by segment_name, SEGMENT_TYPE
order by 3 desc)
where rownum <= 20;
执行清理脚本：
declare
v_sql varchar2(4000);
TYPE tab_partion IS record(
TABLE_OWNER dba_tab_partitions.TABLE_OWNER%TYPE,
TABLE_NAME dba_tab_partitions.TABLE_NAME%TYPE,
PARTITION_NAME dba_tab_partitions.PARTITION_NAME%TYPE,
TABLESPACE_NAME dba_tab_partitions.TABLESPACE_NAME%TYPE
);
type t_tab_partitions is table of tab_partion index by binary_integer ;
v_tab_partitions t_tab_partitions ;
begin
v_sql:='ALTER INDEX I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST SHRINK SPACE' ;
execute immediate v_sql ;
v_sql:='ALTER INDEX I_WRI$_OPTSTAT_H_ST SHRINK SPACE' ;
execute immediate v_sql ;
select TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME
bulk collect into v_tab_partitions
from dba_tab_partitions t where t.table_name='WRI$_OPTSTAT_HISTGRM_HISTORY' ;
for i in 1..v_tab_partitions.count loop
v_sql:='alter table '||v_tab_partitions(i).TABLE_OWNER||'.'||v_tab_partitions(i).TABLE_NAME||' move partition '||v_tab_partitions(i).PARTITION_NAME||' tablespace tab_cc' ;
execute immediate v_sql ;
v_sql:='alter table '||v_tab_partitions(i).TABLE_OWNER||'.'||v_tab_partitions(i).TABLE_NAME||' move partition '||v_tab_partitions(i).PARTITION_NAME||' tablespace sysaux' ;
execute immediate v_sql ;
end loop ;
v_sql:='ALTER INDEX I_WRI$_OPTSTAT_H_ST rebuild online' ;
execute immediate v_sql ;
v_sql:='ALTER INDEX I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online' ;
execute immediate v_sql ;
end ;
/
确保执行成功。
然后在使用上面的查询表空间使用率和查询大对象的语句查询，会发现之前三个大对象的空间占用已经变小了。而sysaux表空间的使用率也已经降下来了。
4、影响说明
由于使用的是oracle12.2版本的rac版本，所以上面的操作只需要在一个节点上执行就行了。虽然脚本本身已经考虑了不影响在线业务，但是也需要在业务量小的时候去做上面的操作。该操作无需任何停机。我上面的截图均是在所内测试环境中的截图，实际查询结果以执行操作的环境为准。