Removing OPTSTATS Data

SQL> select dbms_stats.get_stats_history_availability from dual;

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in (‘WRI$_OPTSTAT_HISTHEAD_HISTORY’,’WRI$_OPTSTAT_HISTGRM_HISTORY’);

WRI$_OPTSTAT_HISTHEAD_HISTORY               4
WRI$_OPTSTAT_HISTGRM_HISTORY              15

DECLARE
ts  TIMESTAMP WITH TIME ZONE;
BEGIN
SELECT SYSTIMESTAMP INTO ts FROM dual;
dbms_stats.purge_stats(ts);
END;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.alter_stats_history_retention(0);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_availability from dual;

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

SQL> select count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY;
0

SQL> select count(1) from WRI$_OPTSTAT_HISTGRM_HISTORY;
0

SQL> truncate table WRI$_OPTSTAT_HISTHEAD_HISTORY;

Table truncated.

SQL> truncate table WRI$_OPTSTAT_HISTGRM_HISTORY;

Table truncated.

SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in (‘WRI$_OPTSTAT_HISTHEAD_HISTORY’,’WRI$_OPTSTAT_HISTGRM_HISTORY’);

WRI$_OPTSTAT_HISTHEAD_HISTORY           .0625
WRI$_OPTSTAT_HISTGRM_HISTORY           .0625

SQL>