You are currently browsing the tag archive for the ‘Remove OPTSTATS’ tag.
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>