By chance, right after my “ODC Appreciation Day” post, I’ve been asked to convert a database from character set WE8ISO8859P1 to AL32UTF8 with DMU. Apart from a few well-known issues described in MOS note 2018250.1, I got a “Need conversion” row on table WRI$_SQLSET_DEFINITIONS in data dictionary.

Section D.11 of MOS note 2018250.1 states that you can remove “Invalid Binary Representation” in AWR tables (WRI$_%, WRH$_%, WRR$_%) by following MOS note 782974.1 to drop and recreate AWR. I tried this solution as a last resort. Unfortunately, after using catnoawr.sql, most of WRI$_% tables are still there, only 3 of them are dropped. And of course, WRI$_SQLSET_DEFINITIONS remains intact.

What is this table ? What does it contain ?

It seems to hold information about SQL Tuning Sets, and in my case, it contains only one row :

SQL> select id,name,description from WRI$_SQLSET_DEFINITIONS;
ID   NAME                    DESCRIPTION
---------------------------------------------------------------------------------------------------------------
1    TOP_SQL_1540556857809    Généré automatiquement par l'instruction SQL la plus consommatrice de ressources

And, oh, what do we have in DESCRIPTION column ? A french sentence !

I am French, I speak French (most of the time) and I live in the French-speaking part of Switzerland. But I only use Enterprise Manager in English, despite the fact that the french translations of “blackouts” and “advisors” are quite funny 🙂

Unluckily, Enterprise Manager is guilty of my conversion problem. Let’s prove it with tests run both in Oracle Database 11.2.0.4 and 12.1.0.2.

Using Enterprise Manager 12.1.0.5 in french (for fun), let’s create an SQL Tuning Set from the Top Activity page :

You do not have to understand French but just consider that “Créer un ensemble de réglages SQL” means “Create SQL Tuning Set” …

Once the SQL Tuning Set is created, the description is in french by default. And it is the one we have in WRI$_SQLSET_DEFINITIONS.

Now, what should I do ?

I opened an SR and kept working on this issue in the meantime. I could have dropped this SQL Tuning Set of course, but I am not satisfied with this workaround. And anyway, I was not able to drop it because of another problem I could also blog about 🙂

So I lazily replaced the problematic characters in the row :

update wri$_sqlset_definitions set description='Genere automatiquement par l''instruction SQL la plus consommatrice de ressources' where id=1;
1 row updated.
 

SQL> select id,name,description from WRI$_SQLSET_DEFINITIONS;

ID   NAME                     DESCRIPTION 
---------------------------------------------------------------------------------------------------------------
1    TOP_SQL_1540556857809    Genere automatiquement par l'instruction SQL la plus consommatrice de ressources

… and finally, DMU Scan Report was all green !

I was afraid I would break something by directly updating an internal object. But no side-effects were observed, AWR snaphots and reports were working as expected. I detailed my workaround in the SR and it was approved by DMU team and AWR team.

I hope this can help any one using Enterprise Manager in an “exotic” way 🙂