My Oracle Support Banner

Solving Convertible or Lossy data in Data Dictionary objects reported by Csscan when changing the NLS_CHARACTERSET (Doc ID 258904.1)

Last updated on APRIL 24, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 8.0.3.0 to 11.2.0.4 [Release 8.0.3 to 11.2]
Oracle Database - Standard Edition - Version 8.0.3.0 to 11.2.0.4 [Release 8.0.3 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Information in this document applies to any platform.

Purpose

You are planning to convert the database character set from one character set to another set (for example to AL32UTF8) .

And you used Csscan and found some "Convertible" or "Lossy" in Data Dictionary objects and plan on using using ALTER DATABASE CHARACTER SET or Csalter. This note tries to give a overview of the most commonly seen "Convertible" and "Lossy" in Data Dictionary objects and how to handle these when using ALTER DATABASE CHARACTER SET / Csalter.

Note that "Convertible" in Data Dictionary objects is NOT a problem when using export/import into a new / other database.

A list of notes in changing the NLS_CHARACTERSET is provided in <Note 225912.1> Changing the Database Character Set ( NLS_CHARACTERSET )

Please do note that:
* Before changing the NLS_CHARACTERSET using Csalter or Alter Database..." it's mandatory to use Csscan. We strongly suggest to read <Note 444701.1> Csscan output explained
* some actions in this note use the csscan "exclude" option, the list of table NEEDS to be between ( ) and on most Unix shells it need  ' ' around the list of tables.
Example: on windows use EXCLUDE=(owner.table,owner.table) , on unix platforms use EXCLUDE='(owner.table,owner.table)'.
to avoid this one can use a parameter file for csscan, see <Note 444701.1> Csscan output explained , section " E) Do I need to always run a full database scan? / The Csscan EXCLUDE , USER or TABLE parameters do not appear to work."
* some selects in this note use the rowid 's stored by csscan in "data_rowid" column of the csmig.csmv$errors table, if such a select give "ORA-01410: invalid ROWID" then this simply means that since the csscan some rows where removed in the object scanned. If this happens simply re-scan the table in question.

 

Scope

Any DBA who has "Convertible" or "Lossy" in Data Dictionary objects while using ALTER DATABASE CHARACTER SET or Csalter.

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details
 A) Useful Selects when using Csscan:
 A1) 8i and 9i only: Data Dictionary objects that need action when using Alter database character set.
 A2) 10g and up: Data Dictionary objects that need action when using Csalter
 A3) 10g and up: To have a list of User / Application Data that need action before using Csalter
 B) 9i ONLY: Handling convertible in SYS.JOB$, SYS.METASTYLESHEET and SYS.RULE$ in 9i :
 C)10g and up: Handling "Convertible" or "Lossy" in the NLSENV column of SYS.JOB$ or NLS_ENV of SYS.SCHEDULER$_JOB or SYS.SCHEDULER$_PROGRAM.
 D) Handling tables like SYS.WRI$_* and SYS.WRH$_*
 D1) To clear EPVALUE of SYS.WRI$_OPTSTAT_HISTGRM_HISTORY execute:
 D2) To clear SYS.WRH$_SQLTEXT execute following steps:
 D3) To clear SYS.WRI$_ADV_OBJECTS / SYS.WRI$_ADV_SQLT_PLANS execute:
 E) "Convertible" or "Lossy" in SYS.SOURCE$
 F) "Convertible" or "Lossy" in SYS.COM$
 G) "Convertible" or "Lossy" in SYS.HISTGRM$
 H) Orphaned DataPump tables
 I) MDSYS (Oracle Spatial) objects (mainly 9i).
 J) "Convertible" or "Lossy" in SYS.COL$
 K) "Convertible" or "Lossy" in SYS.TRIGGER$
 L) "Convertible" or "Lossy" in SYS.VIEW$
 M) "Convertible" or "Lossy" in SYSTEM.HELP
 N) "Convertible" or "Lossy" in TEXT column of SYS.ERROR$
 O) "Convertible" or "Lossy" in AUDIT column of SYS.SEQ$
 P) "Convertible" or "Lossy" in SYS.FGA_LOG$
 Q) "Convertible" or "Lossy" in SYS.REGISTRY$ERROR
 R) "Convertible" or "Lossy" in QUERY_TXT Column of SYS.REG_SNAP$
 S) "Convertible" or "Lossy" in the "DESCRIPTION " column of SYS.RESOURCE* tables
 T) "Convertible" or "Lossy" in PIECE Column of SYS.IDL_CHAR$
 U) "Lossy" in SESSION_KEY column of SYS.REG$ or SYS.REGZ$ :
 V) "Convertible" or "Lossy" in AUD$ 
 W) "Convertible" or "Lossy" in SCHEDULER$ table like SYS.SCHEDULER$_EVENT_LOG
 X) "Lossy" Data In OLAP_DESCRIPTIONS$ Table
 z) "Lossy" for XDB.XDB$COMPLEX_TYPE when doing  FROMCHAR=TOCHAR scan
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.