on Jun 14, 2016Article Content
| Article Number | 000029937 |
| Applies To | RSA Product Set: Identity Management and Governance RSA Product/Service Type: N/A RSA Version/Condition: 6.9.1/6.9 Platform: JBoss/WebSphere/Weblogic Platform (Other): Oracle O/S Version: N/A Product Name: RSA-0018015 |
| Issue | Oracle 11g's Statistics History Retention by default is set to 31 days. Customers have had issues where the SYSAUX tablespace has run into tablespace issue due to statistics retention. |
| Resolution | -- 1) As AVUSER run the following query to determine how many days worth of statistics Oracle is retaining which by default will be 31 days: select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual; -- 2) As AVUSER run the following queries to confirm the oldest date of : select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY, (systimestamp - DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY) as NUM_DAYS_RET from dual; 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; -- 3) To change the default statistics retention of 31 days to 15 days execute the following command as "/ as sysdba" or as the SYS USER: sqlplus / as sysdba -- 4) Copy and paste these commands into the SQLPLUS session: exec dbms_stats.alter_stats_history_retention(15); -- 5) Again 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; |