000029937 - IMG Database - Change Oracle Statistics Retention - dbms_stats.alter_stats_history_retention

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000029937
Applies ToRSA 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

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

Attachments

    Outcomes