sysaux big

select snap_id,
to_char(begin_interval_time, 'YYYY-MM-DD'),
 to_char(end_interval_time, 'YYYY-MM-DD')
 from dba_hist_snapshot
order by snap_id;




wrh$_active_session_history
alter table WRH$_ACTIVE_SESSION_HISTORY shrink space;


 select snap_id,
to_char(begin_interval_time, 'YYYY-MM-DD'),
to_char(end_interval_time, 'YYYY-MM-DD')
from dba_hist_snapshot
 order by snap_id;




1. Check how many partitions do exist for the offending table


select table_name,partition_name from dba_tab_partitions
where table_name = 'WRH$_ACTIVE_SESSION_HISTORY';




2. Try to force the creation of multiple partitions by splitting any current oversized partitions:


alter session set "_swrf_test_action" = 72;




3. Now that we have more partitions and less data per partition for a more unique period of time
  Drop snapshots manually using a small[er] range.


DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id IN NUMBER,
high_snap_id IN NUMBER
dbid IN NUMBER DEFAULT NULL);
 - or -
exec dbms_stats.purge_stats(sysdate-&days);


4. If the drop snapshot did not finish or consume for UNDO space. Terminate the session and wait for a day or two the default auto purge job should automatically drop the old snapshots


DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(1,9516,1499186110)




Manually Purge the Optimizer Statistics & AWR Snaphots to Reduce Space Usage of SYSAUX Tablespace (文件 ID 1965061.1)
WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy (文件 ID 387914.1)
NOTE:387914.1 - WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy
Suggestions if Your SYSAUX Tablespace Grows Rapidly or Too Large (文件 ID 1292724.




Some notes may help with your case:


SYSAUX Grows Because Optimizer Stats History is Not Purged (Doc ID 1055547.1)
Automatic Purge Job Fails with ORA-14758 After Dropping P_PERMANENT Partition Causing SYSAUX Tablespace Growth (Doc ID 1905788.1)
Bug 8553944 - SYSAUX tablespace grows (Doc ID 8553944.8)


NOTE:790039.1 - How to Drop Plans from the SQL Plan Management (SPM) Repository
NOTE:287679.1 - How to Address Issues Where AWR Data Uses Significant Space in the SYSAUX Tablespace
NOTE:387914.1 - WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy
NOTE:255452.1 - Values for init.ora parameter STATISTICS_LEVEL






NOTE:957630.1 - Health Check Alert: Automatic statistics collection is excessive
NOTE:1369591.1 - Master Note: Optimizer Statistics
NOTE:790039.1 - How to Drop Plans from the SQL Plan Management (SPM) Repository
NOTE:287679.1 - How to Address Issues Where AWR Data Uses Significant Space in the SYSAUX Tablespace
NOTE:387914.1 - WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged Based Upon the Retention Policy
NOTE:255452.1 - Values for init.ora parameter STATISTICS_LEVEL