在检查数据库时发现表空间SYSAUX使用率很高，如下所示
|
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 1662 2 …………………………………………………………………….后边省略 |
查看快照信息，发现快照最小值为31074
|
Listing the last 8 days of Completed Snapshots
Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- eicdb1 EICDB 31074 18 Dec 2013 01:00 1 31075 18 Dec 2013 02:00 1 31076 18 Dec 2013 03:00 1 31077 18 Dec 2013 04:00 1 |
通过以下语句查看，发现下面几个表占用空间较大
|
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 22881 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. |
查看分区表snap_id信息，发现其最小值为25627，也就是说里边有许多过期数据存放）
|
SQL> select * from (select distinct SNAP_ID,DBID from sys.wrh$_active_session_history partition(WRH$_ACTIVE_2065804525_25627) order by SNAP_ID) where rownum<=10;
SNAP_ID DBID ---------- ---------- 25627 2065804525 25628 2065804525 25629 2065804525 25630 2065804525 25631 2065804525 25632 2065804525 25633 2065804525 25634 2065804525 25635 2065804525 25636 2065804525
10 rows selected |
查看表wrh$_active_session_history信息
|
SQL> select segment_name,PARTITION_NAME,segment_type,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' and segment_name='WRH$_ACTIVE_SESSION_HISTORY' order by 3; SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE BYTES/1024/1024 ------------------------------ ------------------------------ ------------------ --------------- WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_2065804525_25627 TABLE PARTITION 22881 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION .0625 |
检查其他数据库中sys.wrh$_active_session_history 表，发现该表每天都会创建一个分区表，末尾并以起始snap_id命名（很奇怪，为啥前一个数据库就一个分区表）。
|
SQL> select * from (select distinct SNAP_ID,DBID from sys.wrh$_active_session_history order by SNAP_ID) where rownum<=10;
SNAP_ID DBID ---------- ---------- 1008 1358035033 1009 1358035033 1010 1358035033 1011 1358035033 1012 1358035033 1013 1358035033 1014 1358035033 1015 1358035033 1016 1358035033 1017 1358035033
10 rows selected. SQL> set lines 999 SQL> col SEGMENT_NAME for a30 SQL> select OBJECT_NAME,SUBOBJECT_NAME,object_type,CREATED from dba_objects where OBJECT_NAME like 'WRH$_ACTIVE%' order by object_type;
OBJECT_NAME UBOBJECT_NAME OBJECT_TYPE CREATED ---------------------------------------- ------------------- ------------------- ------------ WRH$_ACTIVE_SESSION_HISTORY_PK INDEX 20-APR-10 WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1119 INDEX PARTITION 17-DEC-13 WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1095 INDEX PARTITION 16-DEC-13 WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_SES_MXDB_MXSN INDEX PARTITION 20-APR-10 WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1047 INDEX PARTITION 14-DEC-13 WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1191 INDEX PARTITION 19-DEC-13 WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1071 INDEX PARTITION 15-DEC-13 WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1143 INDEX PARTITION 18-DEC-13 WRH$_ACTIVE_SESSION_HISTORY_PK WRH$_ACTIVE_1358035033_1167 INDEX PARTITION 19-DEC-13 WRH$_ACTIVE_SESSION_HISTORY TABLE 20-APR-10 WRH$_ACTIVE_SESSION_HISTORY_BL TABLE 20-APR-10 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1167 TABLE PARTITION 18-DEC-13 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1143 TABLE PARTITION 17-DEC-13 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1119 TABLE PARTITION 16-DEC-13 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1095 TABLE PARTITION 15-DEC-13 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1047 TABLE PARTITION 13-DEC-13 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1191 TABLE PARTITION 19-DEC-13 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION 20-APR-10 WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1358035033_1071 TABLE PARTITION 14-DEC-13 SQL> alter table sys.wrh$_active_session_history truncate partition WRH$_ACTIVE_1358035033_1008 update global indexes;
Table truncated. SQL> SQL> col SNAP_INTERVAL for a18 SQL> col RETENTION for a18 SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL ---------- ------------------ ------------------ ---------- 1358035033 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
SQL> select * from v$version;
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 – Production |
通过上述，查看oracle10g数据库信息，发现，有8天的分区表数据，而数据库AWR保留策略为7天，也就是当清除snap_id信息时并没有完全清除表中数据信息。
暂时没想到好的解决办法，就先delete部分数据（该11g生产库运行期间cpu大多在百分之80-90设置更高，所以只能一点点的先delete部分数据（货架上的东西是没了，但货架还在，暂时也就这样了，再找找其他方法，其中有两个索引数据量也不小，可以分析一下）。语句如下：
|
delete from sys.wrh$_active_session_history partition(WRH$_ACTIVE_2065804525_25627) p where p.snap_id<=25850; |
曾想试试一下命令，删除之前统计信息（这个无法确定运行时长，当时没用）：
|
exec dbms_stats.purge_stats(systimestamp - 200); |
参数说明：
附：
删除快照命令：
|
exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>777,high_snap_id=>800,dbid=>2530619508) |
清除数据库中其他数据库AWR信息可以使用下面的语句：
|
exec dbms_swrf_internal.unregister_database(2065804525); |
遗留问题：
1、 delete分区表中部分数据后并没有完全收回空间，高水位问题（move占用资源，可避开高峰期），有其他什么方法可以解决；
2、 oracle11g库中（其他oracle11g库中也是每天生成一个分区表）就一个分区表保存数据，影响数据管理，暂时未找出原因；
3、 对删除快照、AWR过期数据清理流程不太清楚；
4、 为什么表中还保留一些过期数据，MMON后台进程怎么收集和清理AWR信息
|
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 31146 2013-12-21 2013-12-21 31147 2013-12-21 2013-12-21 31147 2013-12-21 2013-12-21 31148 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 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 |