Statistics space used by SM/OPTSTAT in the SYSAUX tablespace is not reclaimed after purging

Applies to:

Oracle Server - Enterprise Edition - Version: to
This problem can occur on any platform.
Oracle Server Enterprise Edition - Version:

Space used by SM/OPTSTAT in the SYSAUX tablespace can not be reclaimed after purge.
Space is not reclaimed immediately after purge is completed.  This is normal behaviour because purge_stats simply delete data from wri$_optstat* tables with a "delete from ..." statement. Free space will be used for new inserts into OPTSTAT tables.
Move these tables from the SYSAUX tablespace then return them back again.
-- To implement the solution, please execute the following steps::
1- Take a full backup of the database
2- Move the tables:
sql> alter table WRI$_OPTSTAT_TAB_HISTORY move;
sql> alter table WRI$_OPTSTAT_OPR move;
sql> alter table WRI$_OPTSTAT_IND_HISTORY move;
sql> alter table WRI$_OPTSTAT_HISTHEAD_HISTORY move;
sql> alter table WRI$_OPTSTAT_HISTGRM_HISTORY move;
sql> alter table WRI$_OPTSTAT_AUX_HISTORY move;

3- For indexes, find the indexes for the above tables and rebuild them. In case an index is unusable, please see the following example:
SQL> select status from dba_indexes where index_name='I_WRI$_OPTSTAT_IND_OBJ#_ST';
SQL> select status from dba_indexes where index_name='I_WRI$_OPTSTAT_TAB_ST';

Assuming that indexes: I_WRI$_OPTSTAT_IND_OBJ#_ST & I_WRI$_OPTSTAT_TAB_ST are unusable, then, we have to do the following:

a. Determine the DDL's for the indexes using dbms_metadata package as shown in the example below
 SQL> set long 4000
 SQL> select dbms_metadata.get_ddl('INDEX','I_WRI$_OPTSTAT_IND_OBJ#_ST','SYS') from dual;
 SQL> select dbms_metadata.get_ddl('INDEX','I_WRI$_OPTSTAT_TAB_ST','SYS') from dual;
b. Then drop and recreate the indexes using the obtained DDL's.
c. Once done you can confirm the status by running the following query for example :
SQL> select status from dba_indexes where index_name='I_WRI$_OPTSTAT_IND_OBJ#_ST';
SQL> select status from dba_indexes where index_name='I_WRI$_OPTSTAT_TAB_ST';

4- sql> exec dbms_stats.alter_stats_history_retention(8); 
--> This will ensure that statistics history will be retained for at least 8 days.

No comments:

Post a Comment