oracle sysaux清理,sysaux表空间清理

--利用数据库内置包和脚本来处理https://www.cndba.cn/asker/article/2238

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);

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

方式二：

https://www.cndba.cn/asker/article/2238https://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/1024https://www.cndba.cn/asker/article/2238

1 SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_1411280035_0 3253

2 SYS WRH$_EVENT_HISTOGRAM_PK WRH$_EVENT__1411280035_0 2496

3 SYS WRH$_EVENT_HISTOGRAM WRH$_EVENT__1411280035_0 1600

4 SYS WRH$_LATCH_MISSES_SUMMARY_PK WRH$_LATCH__1411280035_0 1152

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

5 SYS WRH$_SQLSTAT WRH$_SQLSTA_1411280035_0 808

6 SYS WRH$_LATCH_MISSES_SUMMARY WRH$_LATCH__1411280035_0 784

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';