ITPub博客

首页 > 数据库 > Oracle > 【AWR】该怎样清理SYSAUX表空间相关数据(第二季)

【AWR】该怎样清理SYSAUX表空间相关数据(第二季)

原创 Oracle 作者:xysoul_云龙 时间:2014-02-17 22:14:42 0 删除 编辑

在上一篇文章中查看表空间信息是,还剩余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博客 ” ,链接:http://blog.itpub.net/29487349/viewspace-1082832/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
主要从事数据库相关工作,其他操作系统、中间件等也有涉及,热衷分享、开源,支持国产,期待中华民族全面的伟大复兴。近日骤然醒悟,欲在IT江湖中闯荡一番,如有幸在诸多侠客中留点踪迹,也算不虚此行。 【文盲筱烨】好读书爱运动的IT技术爱好者 微博:文盲筱烨 微信公众号:筱烨视点

注册时间:2014-02-15

  • 博文量
    168
  • 访问量
    757112