How to change the default Oracle Statistics History Retention period for RSA Identity Governance & Lifecycle
Originally Published: 2015-03-30
Article Number
Applies To
RSA Version/Condition: All
Platform (Other): Oracle 11, 12
Issue
The Oracle statistics history retention value is set to 31 days by default. Some customers have found that the SYSAUX tablespace has space issues due to the large amounts of space consumed by 31 days of database statistics. The purpose of this RSA Knowledge Base Article is to provide instructions for reducing the default statistics history retention setting. However, the same steps may be used to increase the default statistics history retention setting if desired.
Resolution
- Login to SQL as avuser:
- Run the following query to determine how many days of statistics Oracle is currently retaining on your system. Unless this has been previously changed by someone at your site, this value will be 31.
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
Sample output:
- Run the following two queries to confirm the oldest date retained. For example, if your system is new and installed less than 31 days ago, you will have less than 31 days of history retained.
select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY, (systimestamp - DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY) as NUM_DAYS_RET from dual;
Sample output:
select (DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY) , to_date(to_char(DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY,'dd-MON-YY hh:mi:ss AM') ,'dd-MON-YY hh:mi:ss AM') as STATS_AVAIL_FROM , to_date(to_char(systimestamp,'dd-MON-YY hh:mi:ss AM'),'dd-MON-YY hh:mi:ss AM') as TODAY , round(to_date(to_char(DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY,'dd-MON-YY hh:mi:ss AM') ,'dd-MON-YY hh:mi:ss AM') - to_date(to_char(systimestamp,'dd-MON-YY hh:mi:ss AM'),'dd-MON-YY hh:mi:ss AM')) as NUM_RET_DAYS_RD , (systimestamp - DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY) as NUM_DAYS_RET_PRECISE from dual;
Sample output:
- Log out of SQL
- Login as sysdba or the SYS user.
- Execute the following SQL where xx is the number of days you want to retain database statistics history.
exec dbms_stats.alter_stats_history_retention(xx);
For example, to change the history retention to 15 days execute:
exec dbms_stats.alter_stats_history_retention(15);
- Log in as avuser.
- Run the following query to confirm that the Oracle statistics history retention has been adjusted down from 31 to 15 days:
select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
Sample output:
