A Short Note on Pesky CSSCAN Results Before a Characterset Change

A client of mine was busy correcting one of his development streets and therefore the NLS charactersets of about 10 11.2.0.4 EE databases had to be changed.
Most of the databases were originally configured with WE8ISO8859P15, others with WE8ISO8859P1 and all of them had to get WE8MSWIN1252. Some of them were single instances (export, development and test), some RAC (acceptance) and some of them were RAC plus Dataguard (production, for the procedure see Doc ID 1124165.1).

The initial results of the csscan’s always showed table SYS.REG$ with a couple of LOSSY entries and all of them indicated 1 to 4 user tables containing LOSSY (or Convertible) data. The user tables were truncated and imported again after the conversion. And there where the (RAC) instances showing troublesome entries in some WRI$_%, WRH$_% and/or WRR$_%-tables including table SYS.WRI$_ADV_MESSAGE_GROUPS.

For most of this tables we could figure out quite quickly how to remedy these entries, but table SYS.WRI$_ADV_MESSAGE_GROUPS eluded us for a while. All we found on the internet, in the Oracle documentation and on MySupport were about other specific SYS.WRI$- or WRH$ tables, but SYS.WRI$_ADV_MESSAGE_GROUPS was never mentioned in connection with csscans. So, I tried to figure out what this table actually is used for and found that…

… WRM$-tables (10 tables) contain the metadata information for the Automatic Workload Repository (AWR).
… WRH$ tables (125 tables) store historical data or snapshots of the Workload History collected by the MMON process. They can be purged by first reducing the retention time for the history followed by the purging of the statistics:

exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(0);
exec DBMS_STATS.PURGE_STATS(sysdate);

These two types of tables are, according to Oracle Doc ID 258904.1, not to be truncated, but have to be purged with DBMS_STATS package to prevent them from any harm.

… WRR$-tables (31 tables) support the workload replay functionality.
… WRI$-tables (96 tables) are related to the advisory functions.

But my pesky little SYS.WRI$_ADV_MESSAGE_GROUPS-table turned out to belong to the Task Scheduler and that the Database Migration Utility (DMU) sometimes needs this table to be cleared as well (Doc ID 2018250.1). Luckyly, tasks were not used in these databases and finding out which task entry was to blame was senseless and too time consuming. So, we decided to remove all the tasks and found this command to do so:

exec DBMS_ADVISOR.DELETE_TASK('%');

… and really, it worked!

Table SYS.WRI$_ADV_MESSAGE_GROUPS was clean and we could finally execute CSALTER.PLB without any further problems.