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…

Release Content Documents for Oracle Applications 12.2

Oracle has released these documents. My Oracle Support document “Oracle E-Business Suite Releases 12.1 and 12.2 Release Content Documents [ID 1302189.1] “ has link to the necessary documents. There is quite a bit of interesting information that any Apps DBA would be excited about in those documents.

As updated in some popular blogs here and here, R12.2 will be available pretty soon. There are quite a few interesting features that one can try in 12.2 version. One should consider upgrading to 11gR2 version at the earliest to get a glimpse of those interesting features.

More details soon.