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 🙂