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版本，是以上面的操作隻需要在一個節點上執行就行了。雖然腳本本身已經考慮了不影響線上業務，但是也需要在業務量小的時候去做上面的操作。該操作無需任何停機。我上面的截圖均是在所内測試環境中的截圖，實際查詢結果以執行操作的環境為準。