Hi Experts,
I'm facing below issue in our EBS environment with 11.2.0.3 DB and 12.1.3 EBS on Solaris 64 bit platform.
Steps we performed to purge optimizer stats from SYSAUX
1. Create intermediate tables to backup stats WHERE savtime > SYSDATE - 15
2 Truncate below tables with drop storage
SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY
SYS.WRI$_OPTSTAT_TAB_HISTORY
SYS.WRI$_OPTSTAT_IND_HISTORY
SYS.WRI$_OPTSTAT_histgrm_HISTORY
3. Insert records from backup tables to source tables.
4. Drop the intermediate tables used to store backup.
5. Verified the free space in SYSAUX, it appears 65GB is free space from 66 G total space.
6. Re-org the tables and rebuild indexes to reclaim the free space available from SYSAUX.
select 'alter table '||segment_name||' move tablespace SYSAUX;' from dba_segments where tablespace_name = 'SYSAUX' and segment_name like '%OPT%' and segment_type='TABLE';
select 'alter index '||segment_name||' rebuild online parallel (degree 14);' from dba_segments where tablespace_name = 'SYSAUX' and segment_name like '%OPT%' and segment_type='INDEX';
7. verified the free space in SYSAUX but still unable to reclaim the free space. Can you please let me know, what is missing.
TABLESPACE_NAME ALLOCATED USED FREE PERCTFREE CONTIGUOUS
------------------------------ ---------- ---------- ---------- ---------- ----------
SYSAUX........................ 68478.98 1621.73 66857.25 97.63 744.00
---------- ---------- ---------- ----------
sum 68478.98 1621.73 66857.25 744.00
Please let me know, if you need any additional information.
Thanks,
-Ankur