Automate SM/OPTSTAT component purging in SYSAUX tablespace

This post is a followup of my earlier post on handling SM/OPTSTAT component growth in SYSAUX tablespace.

Edit: Oracle has released patch 8553944  to fix this issue. Please check My Oracle Support for patch availability for your environment.

In our environment we have used a simple PL/SQL procedure to gradually purge this SM/OPTSTAT component data. As the purge process is resource intensive it was important for us to perform this purge activity in smaller chunks. Below is the PL/SQL procedure that was developed by one of my colleague (Naveen.KNS) . He gladly accepted to share this code with the user community. Thanks to Naveen!

CREATE OR REPLACE PROCEDURE "SYSTEM"."SYSAUX_PURGE_AUTO_P"
IS
HIST_DATE DATE;
TARG_DATE DATE;
RENT_DATE NUMBER;
LIMIT_DATE DATE;
BEGIN
SELECT TRUNC(DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY) INTO HIST_DATE FROM DUAL;
SELECT HIST_DATE + 2 INTO TARG_DATE FROM DUAL;
SELECT TRUNC(DBMS_STATS.GET_STATS_HISTORY_RETENTION) INTO RENT_DATE FROM DUAL;
SELECT SYSDATE - RENT_DATE INTO LIMIT_DATE FROM DUAL;
IF TARG_DATE < LIMIT_DATE THEN
DBMS_STATS.PURGE_STATS(TARG_DATE);
END IF;
END ;

This code will purge two days of old statistics in one run. One can schedule this procedure using any scheduling mechanism in off peak hours.

Do note that this process will ONLY purge the information but will not release the free space. You might have to perform re-org of necessary tables to reclaim the space.

Till next time…