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
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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20747382/viewspace-2130420/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20747382/viewspace-2130420/