SYSAUX tablespace is growing erratically due to growth of WRI$_OPTSTAT_HISTHEAD_HISTORY table

This is a bug of oracle. If gather schema takes much time it doesn't complete the full operation and  WRI$_OPTSTAT_HISTHEAD_HISTORY keeps up growing. It exhausts the SYSAUX tablespace. Oracle recommends a patch to be applied to resolve the above problem. But, in our case there is some
problem to apply the patch. So, we have taken following procedure to keep the statistics for last 30 days and purge rest.
This is a oracle apps R12.1.3 database. Another symptom of these problem is that "Gather Schema Statistics" takes lots of time. 

You may follow this step without applying patch, but, test it in a TEST instance first.

Cause
Run the following command in sql.
select dbms_stats.get_stats_history_availability from dual;
If your date that is coming is more than your stat history retention period [say 30 days] then your system is sufferring with this bug.


Solution

First create three backup tables which keeps  last 30 days data from following statistics tables.
create table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY_BACKUP as (select * from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY where savtime > (SYSDATE - 30));
create table SYS.wri$_optstat_tab_history_BACKUP as (select * from sys.wri$_optstat_tab_history where savtime > (SYSDATE - 30));
create table SYS.wri$_optstat_ind_history_BACKUP as (select * from sys.wri$_optstat_ind_history where savtime > (SYSDATE - 30));

Truncate the above tables to release the space.
truncate table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;
truncate table SYS.WRI$_OPTSTAT_TAB_HISTORY;
truncate table SYS.WRI$_OPTSTAT_IND_HISTORY;

Insert last 30 days data in the mentioned three tables.
insert into SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY_BACKUP);
insert into SYS.WRI$_OPTSTAT_TAB_HISTORY (select * from SYS.wri$_optstat_tab_history_BACKUP);
insert into SYS.WRI$_OPTSTAT_IND_HISTORY (select * from SYS.wri$_optstat_ind_history_BACKUP);
commit;


Drop the above three temporary backup tables. 
drop table SYS.WRI$_OPTSTAT_BAK;
drop table SYS.WRI$_OPTSTAT_TAB_BAK;
drop table SYS.WRI$_OPTSTAT_IND_BAK;

Recreate the indexes on table. Drop the related indexes on those tables.
drop index I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST;
drop index I_WRI$_OPTSTAT_HH_ST;


Recreate the index.
CREATE UNIQUE INDEX "SYS"."I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST" ON "SYS"."WRI$_OPTSTAT_HISTHEAD_HISTORY" ("OBJ#", "INTCOL#", SYS_EXTRACT_UTC("SAVTIME"), "COLNAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  tablespace "SYSAUX";

  CREATE INDEX "SYS"."I_WRI$_OPTSTAT_HH_ST" ON "SYS"."WRI$_OPTSTAT_HISTHEAD_HISTORY" (SYS_EXTRACT_UTC("SAVTIME"))
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSAUX";


Make sure indexes are in usable state using the following statement.
select index_name from dba_indexes where status='UNUSABLE';

Then purge the stats using following command.
SQL> exec dbms_stats.purge_stats(SYSDATE-30);

If you are using Oracle Apps R12, run Gather Schema Statistics concurrent program.

Now whether the stats are available for only last 30 days or not.
select dbms_stats.get_stats_history_availability from dual;