在上一篇文章中檢視錶空間資訊是，還剩餘1662M，今天我再次檢視

SQL> set pagesize 9999

SQL> set linesize 132

SQL> col TABLESPACE_NAME for a25

SQL> select

2 f.tablespace_name,

3 a.total,

4 f.free,

5 round((f.free/a.total)*100) "% Free"

6 from

7 (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,

8 (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f

9 WHERE a.tablespace_name = f.tablespace_name(+)

10 order by "% Free"

11 /

TABLESPACE_NAME TOTAL FREE % Free

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

SYSAUX 81920 1195 1

…………………………………………………………………….後邊省略

檢視快照資訊，發現快照最小值為31146

SQL> select snap_id,

2 to_char(begin_interval_time, 'YYYY-MM-DD'),

3 to_char(end_interval_time, 'YYYY-MM-DD')

4 from dba_hist_snapshot

5 order by snap_id;

SNAP_ID TO_CHAR(BE TO_CHAR(EN

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

31146 2013-12-21 2013-12-21

31147 2013-12-21 2013-12-21

31148 2013-12-21 2013-12-21

31149 2013-12-21 2013-12-21

…………………..

31228 2013-12-24 2013-12-24

31229 2013-12-24 2013-12-24

162 rows selected.

再檢視wrh$_active_session_history資料資訊，最小值也是31146，也就是說，之前快照資訊已經刪除，但空間沒釋放

SQL> select * from (select distinct SNAP_ID,DBID from sys.wrh$_active_session_history order by SNAP_ID) where rownum<=10;

SNAP_ID DBID

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

31146 2065804525

31147 2065804525

31148 2065804525

31149 2065804525

31150 2065804525

31151 2065804525

31152 2065804525

31153 2065804525

31154 2065804525

31155 2065804525

透過以下語句檢視，發現下面有兩個索引佔用空間也比較大

SQL> 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;

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024

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

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2065804525_25627 TABLE PARTITION 23150

I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 16125

I_WRI$_OPTSTAT_H_ST INDEX 12002

WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 10008

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_2065804525_25627 INDEX PARTITION 3030

WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 2135

WRH$_SYSMETRIC_HISTORY TABLE 1732

I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX 1685

WRH$_SYSMETRIC_HISTORY_INDEX INDEX 1671

WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT__2065804525_25594 INDEX PARTITION 1291

10 rows selected.

檢視索引資訊

SQL> select owner,index_name,table_owner,table_name,table_type from dba_indexes where index_name='I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST';

OWNER INDEX_NAME TABLE_OWNER TABLE_NAME TABLE_TYPE

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

SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST SYS WRI$_OPTSTAT_HISTGRM_HISTORY TABLE

對該索引進行空間收縮操作

SQL> alter index I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST shrink space;

Index altered.

---------此命令執行時常2個小時，由於shrink是拆東牆補西牆，不會額外佔用其他可用空間，俺也就耐心等候了

待命令執行完成後，檢查表空間資訊

SQL> set pagesize 9999

SQL> set linesize 132

SQL> col TABLESPACE_NAME for a25

SQL> select

2 f.tablespace_name,

3 a.total,

4 f.free,

5 round((f.free/a.total)*100) "% Free"

6 from

7 (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,

8 (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f

9 WHERE a.tablespace_name = f.tablespace_name(+)

10 order by "% Free"

11 /

TABLESPACE_NAME TOTAL FREE % Free

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

UNDOTBS1 184320 9663 5

SYSAUX 81920 7223 9

…………………….

哇，終於不是第一個了

再次檢視SYSAUX表空間空間詳細使用狀況，發現該索引騰出6G左右的空間來，繼續，使用同樣命令將索引I_WRI$_OPTSTAT_H_ST空間收縮一下

SQL> 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;

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024

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

WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2065804525_25627 TABLE PARTITION 23150

I_WRI$_OPTSTAT_H_ST INDEX 12002

I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 10041.5625

WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 10008

WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_2065804525_25627 INDEX PARTITION 3030

WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 2135

WRH$_SYSMETRIC_HISTORY TABLE 1732

I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX 1685

WRH$_SYSMETRIC_HISTORY_INDEX INDEX 1671

WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT__2065804525_25594 INDEX PARTITION 1291

10 rows selected.

命令執行完成後，表空間使用資訊如下：

SQL> set pagesize 9999

SQL> set linesize 132

SQL> col TABLESPACE_NAME for a25

SQL> select

2 f.tablespace_name,

3 a.total,

4 f.free,

5 round((f.free/a.total)*100) "% Free"

6 from

7 (select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files group by tablespace_name) a,

8 (select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) f

9 WHERE a.tablespace_name = f.tablespace_name(+)

10 order by "% Free"

11 /

TABLESPACE_NAME TOTAL FREE % Free

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

UNDOTBS1 184320 223 0

SYSAUX 81920 11786 14

……………………………..

暫時就放心了，至少最近幾天SYSAUX空間不會滿，現在WRH$_ACTIVE_SESSION_HISTORY分割槽狀況還是不理想，正在跟蹤，目前資料快照保留策略為3天，等兩天後，將分割槽表WRH$_ACTIVE_2065804525_25627及其索引drop掉，空間就可以騰出很客觀的一部分了，更好的整理其他索引、表的資料。還是沒搞明白它是怎麼分割槽的，每天都去看，跟它死磕。

附：可透過以下命令檢視WRH$_ACTIVE_SESSION_HISTORY每個分割槽的最大最小快照號，方便清理資料：

檢視分割槽

select table_name,partition_name

from dba_tab_partitions

where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

alter session set "_swrf_test_action" = 72;

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

SQL> set serveroutput on

declare

CURSOR cur_part IS

SELECT partition_name from dba_tab_partitions

SQL> 2 3 4 WHERE table_name = 'WRH$_ACTIVE_SESSION_HISTORY';

5 query1 varchar2(200);

6 query2 varchar2(200);

7 TYPE partrec IS RECORD (snapid number, dbid number);

8 TYPE partlist IS TABLE OF partrec;

9 Outlist partlist;

10 begin

11 dbms_output.put_line('PARTITION NAME SNAP_ID DBID');

12 dbms_output.put_line('--------------------------- ------- ----------');

13 for part in cur_part loop

14 query1 := 'select min(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';

15 execute immediate query1 bulk collect into OutList;

16 if OutList.count > 0 then

17 for i in OutList.first..OutList.last loop

18 dbms_output.put_line(part.partition_name||' Min '||OutList(i).snapid||' '||OutList(i).dbid);

19 end loop;

20 end if;

21 query2 := 'select max(snap_id), dbid from sys.WRH$_ACTIVE_SESSION_HISTORY partition ('||part.partition_name||') group by dbid';

22 execute immediate query2 bulk collect into OutList;

23 if OutList.count > 0 then

24 for i in OutList.first..OutList.last loop

25 dbms_output.put_line(part.partition_name||' Max '||OutList(i).snapid||' '||OutList(i).dbid);

26 dbms_output.put_line('---');

27 end loop;

28 end if;

29 end loop;

30 end;

31 /

PARTITION NAME SNAP_ID DBID

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

WRH$_ACTIVE_2065804525_25627 Min 31146 2065804525

WRH$_ACTIVE_2065804525_25627 Max 31204 2065804525

---

WRH$_ACTIVE_2065804525_31206 Min 31206 2065804525

WRH$_ACTIVE_2065804525_31206 Max 31233 2065804525

---

PL/SQL procedure successfully completed.

清除過期快照

exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>26501,high_snap_id=>26600,dbid=>2065804525)

繼續ing

【AWR】該怎樣清理SYSAUX表空間相關資料（第二季）

相關文章