在上一篇文章中查看表空间信息是，还剩余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 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) |
继续ing
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29487349/viewspace-1082832/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- 【RESIZE】Oracle收缩表空间主要命令
- 【AUDIT]Oracle审计配置及常用sql
- 【MEMORY】Oracle内存结构资源常用视图及sql
- 【ORA-】ORA-12547: TNS:lost contact错误的跟踪分析
- 【NUMBER】Oracle数据库优化之理解NUMBER存储机制
- 【STAT】函数索引和使用表达式统计信息有什么不同
- 【SQL】Oracle数据库sql优化小技巧索引不管用怎么办01
- 【AWR】DBA_HIST视图查看存储在AWR中的历史数据
- 【SQL】Oracle 19c SQL隔离详解(SQL Quarantine)
- 【小惊喜】总算一个小惊喜，Oracle ACEA
