处理SM/AWR占用sysaux很大空间的问题

1.计算SYSAUX的occupants的大小

 

select *

  from (SELECT occupant_name "Item",

               trunc((space_usage_kbytes / 1048576), 2) "Space Used (GB)",

               schema_name "Schema",

               move_procedure "Move Procedure"

          FROM v$sysaux_occupants

         ORDER BY 2 desc)

 where rownum <= 10

 

2.查看无效的index

 

select * from dba_indexes where degree not in ('1','0','DEFAULT');

 

3.统计表空间大小

 

select    b.file_id as file_id,

          b.tablespace_name as tablespace_id,

          b.file_name as file_name,

          round(b.bytes / 1024 / 1024 ) as total_size,

          round((b.bytes / 1024 / 1024 ) - sum(nvl(a.bytes / 1024 / 1024 , 0))) as used_size,

          round(sum(nvl(a.bytes / 1024 / 1024 , 0))) as free_size,

          round(sum(nvl(a.bytes, 0)) / (b.bytes) * 100) 　as free_percent 　　

      from dba_free_space a, dba_data_files b 　　

    where a.file_id = b.file_id 　　

    group by b.tablespace_name, b.file_name, b.file_id, b.bytes 　　

    order by b.tablespace_name

 

4.查询分SYSAUX区段的大小

 

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;

 

5.删除保留旧snapshot信息的历史分区

 

alter table WRH$_ACTIVE_SESSION_HISTORY drop partition WRH$_ACTIVE_1172987837_0

 

select min(snap_id),max(snap_id) from  wrh$_active_session_history;

 

select * from dba_hist_wr_control;

 

SQL> alter table WRH$_SYSSTAT drop partition WRH$_SYSSTA_1172987837_0;

 

Table altered.

 

SQL> select max(SNAP_ID),min(SNAP_ID) from WRH$_PARAMETER;

 

MAX(SNAP_ID) MIN(SNAP_ID)

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

       20040         8274

 

SQL> alter table WRH$_PARAMETER drop partition WRH$_PARAME_1172987837_0;

 

Table altered.

 

SQL>  alter table WRH$_SYSTEM_EVENT drop partition WRH$_SYSTEM_1172987837_0;

 

Table altered.

 

SQL> alter table WRH$_SEG_STAT drop partition WRH$_SEG_ST_1172987837_0;

 

Table altered.

 

SQL> alter table WRH$_DLM_MISC drop partition WRH$_DLM_MI_1172987837_0;

 

Table altered.

 

SQL> alter table WRH$_SERVICE_STAT drop partition WRH$_SERVIC_1172987837_0;

 

Table altered.

 

SQL> select count(*) from WRH$_SQLSTAT;

 

  COUNT(*)

----------

     68479

    SQL> select max(SNAP_ID),min(SNAP_ID) from WRH$_SQLSTAT;

 

MAX(SNAP_ID) MIN(SNAP_ID)

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

       20039         8274

 

SQL> select min(snap_id),max(snap_id) from  wrh$_active_session_history;

 

MIN(SNAP_ID) MAX(SNAP_ID)

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

       19853        20039

 

SQL> alter table WRH$_SQLSTAT drop partition WRH$_SQLSTA_1172987837_0;

 

Table altered.

 

SQL> select max(SNAP_ID),min(SNAP_ID) from WRH$_SQLSTAT;

 

MAX(SNAP_ID) MIN(SNAP_ID)

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

       20039        19853

 

SQL> alter table WRH$_LATCH_MISSES_SUMMARY drop partition WRH$_LATCH__1172987837_0;

 

Table altered.

 

SQL> alter table WRH$_LATCH drop partition WRH$_LATCH_1172987837_0;

 

Table altered.

 

SQL> alter table WRH$_EVENT_HISTOGRAM drop partition WRH$_EVENT__1172987837_0;

 

Table altered.

 

SQL> select max(SNAP_ID),min(SNAP_ID) from WRH$_SQL_PLAN;

 

MAX(SNAP_ID) MIN(SNAP_ID)

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

       20090         8274

 

SQL> select count(*) from WRH$_SQL_PLAN;

 

  COUNT(*)

----------

   8714427

 

SQL> delete from WRH$_SQL_PLAN where SNAP_ID<19853;

 

8496572 rows deleted.

 

SQL> commit;

 

Commit complete.

 

SQL> select max(SNAP_ID),min(SNAP_ID) from WRH$_SQL_PLAN;

 

MAX(SNAP_ID) MIN(SNAP_ID)

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

       20090        19853

 

SQL> alter table  WRH$_SQL_PLAN enable ROW MOVEMENT;

 

Table altered.

 

SQL> alter table  WRH$_SQL_PLAN shrink space;

 

Table altered.

 

SQL> alter table  WRH$_SQL_PLAN disable ROW MOVEMENT;

 

Table altered.

 

6.再次查询表空使用率

 

    select    b.file_id as file_id,

          b.tablespace_name as tablespace_id,

          b.file_name as file_name,

          round(b.bytes / 1024 / 1024 ) as total_size,

          round((b.bytes / 1024 / 1024 ) - sum(nvl(a.bytes / 1024 / 1024 , 0))) as used_size,

          round(sum(nvl(a.bytes / 1024 / 1024 , 0))) as free_size,

          round(sum(nvl(a.bytes, 0)) / (b.bytes) * 100) 　as free_percent 　　

      from dba_free_space a, dba_data_files b 　　

    where a.file_id = b.file_id 　　

    group by b.tablespace_name, b.file_name, b.file_id, b.bytes 　　

    order by b.tablespace_name

 

空间释放完毕。