sysaux表空间使用率过大，一般是因为数据库快照过多，占用大量空间

方式一：

--利用数据库内置包和脚本来处理
SQL> conn / as sysdba
SQL> @?/rdbms/admin/awrinfo.sql

select systimestamp - min(savtime) from sys.wri$_optstat_histgrm_history;

SQL>  exec dbms_stats.purge_stats(sysdate -20);

SQL> set lines 150
SQL> col SEGMENT_NAME for a30
SQL> col PARTITION_NAME for a50
SQL> SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GB FROM dba_segments WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( low_snap_id IN NUMBER,high_snap_id IN NUMBER, dbid IN NUMBER DEFAULT NULL);

通过上述方式呢，对于数据量较大的视图执行效果太缓慢。

 http://www.cndba.cn/asker/article/2238
方式二：
  

   http://www.cndba.cn/asker/article/2238
  

手动找到具体的数据表，清理数据（建议）

 
select owner,segment_name,partition_name,bytes/1024/1024 from dba_segments where tablespace_name='SYSAUX' order by 4 desc;
 OWNER SEGMENT_NAME PARTITION_NAME BYTES/1024/1024 
1 SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1411280035_0 3253 http://www.cndba.cn/asker/article/2238
2 SYS WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT__1411280035_0 2496 http://www.cndba.cn/asker/article/2238
3 SYS WRH$_EVENT_HISTOGRAM WRH$_EVENT__1411280035_0 1600 
  

   http://www.cndba.cn/asker/article/2238
  

4 SYS WRH$_LATCH_MISSES_SUMMARY_PK WRH$_LATCH__1411280035_0 1152 
5 SYS WRH$_SQLSTAT WRH$_SQLSTA_1411280035_0 808 
  

   http://www.cndba.cn/asker/article/2238
  
http://www.cndba.cn/asker/article/2238

6 SYS WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH__1411280035_0 784http://www.cndba.cn/asker/article/2238









SELECT owner, segment_name, partition_name, segment_type, bytes/1024/1024/1024 Size_GB FROM dba_segments WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';

select min(snap_id), max(snap_id)  from dba_hist_snapshot where dbid = 1411280035
select dbid, status, count(*) from SYS.wrm$_snapshot group by dbid, status;
select min(snap_id), max(snap_id) from SYS.wrm$_snapshot

--清除sys.wrh$_active_session_history除了SYS.wrm$_snapshot之外的快照信息
select count(*)
from sys.wrh$_active_session_history a
where not exists (select 1
  from sys.wrm$_snapshot b
      where a.snap_id = b.snap_id
          and a.dbid = b.dbid
          and a.instance_number = b.instance_number);

delete
from sys.wrh$_active_session_history a
where not exists (select 1
         from sys.wrm$_snapshot b
    where a.snap_id = b.snap_id
     and a.dbid = b.dbid
      and a.instance_number = b.instance_number);

alter table sys.wrh$_active_session_history move  partition WRH$_ACTIVE_1411280035_0;
select min(snap_id),max(snap_id) from sys.wrh$_active_session_history partition(WRH$_ACTIVE_SES_MXDB_MXSN)
select min(snap_id),max(snap_id) from sys.wrh$_active_session_history partition(WRH$_ACTIVE_1381049201_0)


--清除sys.WRH$_EVENT_HISTOGRAM除了SYS.wrm$_snapshot之外的快照信息
select count(*)
from sys.WRH$_EVENT_HISTOGRAM a
where not exists (select 1
  from sys.wrm$_snapshot b
      where a.snap_id = b.snap_id
          and a.dbid = b.dbid
          and a.instance_number = b.instance_number);

delete
from sys.WRH$_EVENT_HISTOGRAM a
where not exists (select 1
         from sys.wrm$_snapshot b
    where a.snap_id = b.snap_id
     and a.dbid = b.dbid
      and a.instance_number = b.instance_number);

alter table sys.WRH$_EVENT_HISTOGRAM move  partition WRH$_EVENT__1411280035_0 ;
select min(snap_id),max(snap_id) from sys.WRH$_EVENT_HISTOGRAM partition(WRH$_EVENT_HISTO_MXDB_MXSN)
select min(snap_id),max(snap_id) from sys.WRH$_EVENT_HISTOGRAM partition(WRH$_EVENT__1411280035_0)


--编译失效索引：
select  'alter index ' || a.index_owner || '.' || a.index_name || ' rebuild partition ' || a.partition_name ||' ;' sql_t
  from dba_ind_partitions a, dba_part_indexes b
 where a.index_owner=b.owner
 and a.index_name=b.index_name
 and b.table_name='WRH$_ACTIVE_SESSION_HISTORY'
 and b.table_name not like 'BIN%'
 and a.status='UNUSABLE';

 select  'alter index ' || owner || '.' || index_name || ' rebuild ;' sql_t
  from dba_indexes
 where
 table_name='WRH$_ACTIVE_SESSION_HISTORY' and
 status not in ('VALID','N/A');


 select  'alter index ' || a.index_owner || '.' || a.index_name || ' rebuild subpartition ' || a.SUBPARTITION_NAME  ||' ;' sql_t
  from dba_ind_subpartitions a, dba_part_indexes b
 where a.index_owner=b.owner
 and a.index_name=b.index_name
 and b.table_name='WRH$_ACTIVE_SESSION_HISTORY'
 and b.table_name not like 'BIN%'
 and a.status='UNUSABLE';




http://www.cndba.cn/asker/article/2238


http://www.cndba.cn/asker/article/2238

版权声明：本文为博主原创文章，未经博主允许不得转载。

sysaux