ORA-39346: data loss in character set conversion for object SCHEMA_EXPORT/TABLE/COMMENT

When importing a schema exported from one 12cR1 AL32UTF8 database to another database  with same AL32UTF8 characterset, this ORA -error was generated :

ORA-39346: data loss in character set conversion for object SCHEMA_EXPORT/TABLE/COMMENT

The error message clearly shows the problem data is from “SCHEMA_EXPORT/TABLE/COMMENT”.

Using MDU tool ( Database  Migration Assistant for Unicode ) to scan database Dictionary Objects, we can see the problem data is from SYS.COM$.

Get the columns and tables for those problem comments:

SQL> desc sys.com$
 Name           Null?     Type
 -------------- -------- ----------------------------
 OBJ#           NOT NULL NUMBER
 COL#                    NUMBER
 COMMENT$                VARCHAR2(4000)

SQL> select count(*) from sys.com$;

COUNT(*)
----------
 151

SQL> select OBJ# , COL#, substr(COMMENT$,1,80) from sys.com$ where comment$ like 'The Accounting Date of the Transaction%same as the incoming transaction date';

OBJ# COL#
---------- ----------
SUBSTR(COMMENT$,1,80)
--------------------------------------------------------------------------------
 143947 6
The Accounting Date of the Transaction ?.. same as the incoming transaction date

143982 6
The Accounting Date of the Transaction ?.. same as the incoming transaction date


SQL> select owner, OBJECT_ID,OBJECT_NAME from dba_objects where OBJECT_ID in (143947,143982);

OWNER            OBJECT_ID   OBJECT_NAME
---------------- ---------- ------------------------------
JAMESH           143947     CM_GL_REC_DWN_STG
JAMESH1          143982     CM_GL_REC_DWN_STG

SQL> select OWNER,TABLE_NAME,COLUMN_NAME from dba_tab_columns where TABLE_NAME='CM_GL_REC_DWN_STG' and COLUMN_ID=6;

OWNER            TABLE_NAME            COLUMN_NAME
---------------- --------------------- --------------------
JAMESH           CM_GL_REC_DWN_STG     ACCOUNTING_DT
JAMESH1          CM_GL_REC_DWN_STG     ACCOUNTING_DT


SQL>select OWNER,TABLE_NAME,COLUMN_NAME,COMMENTS from dba_COL_COMMENTs where comments like 'The Accounting Date of the Transaction%same as the incoming transaction date'

OWNER            TABLE_NAME            COLUMN_NAME COMMENTS
---------------- --------------------- -------------------- --------------------------------------------------------------------------------
JAMESH           CM_GL_REC_DWN_STG     ACCOUNTING_DT The Accounting Date of the Transaction ?.. same as the incoming transaction date
JAMESH1          CM_GL_REC_DWN_STG     ACCOUNTING_DT The Accounting Date of the Transaction ?.. same as the incoming transaction date

SQL> comment on column JAMESH.CM_GL_REC_DWN_STG.ACCOUNTING_DT is 'The Accounting Date of the Transaction,same as the incoming transaction date';

Comment created.

SQL> comment on column JAMESH1.CM_GL_REC_DWN_STG.ACCOUNTING_DT is 'The Accounting Date of the Transaction,same as the incoming transaction date';

Comment created.

SQL>select OWNER,TABLE_NAME,COLUMN_NAME,COMMENTS from dba_COL_COMMENTs where comments like 'The Accounting Date of the Transaction%same as the incoming transaction date'

OWNER            TABLE_NAME            COLUMN_NAME COMMENTS
---------------- --------------------- -------------------- --------------------------------------------------------------------------------
JAMESH           CM_GL_REC_DWN_STG     ACCOUNTING_DT The Accounting Date of the Transaction,same as the incoming transaction date
JAMESH1          CM_GL_REC_DWN_STG     ACCOUNTING_DT The Accounting Date of the Transaction,same as the incoming transaction date

Using MDU tool ( Database  Migration Assistant for Unicode ) to scan database Dictionary Objects again, we can see all green including SYS.COM$.

Do an another export and import again, everything will be fine.

Advertisements
This entry was posted in DataPump, ORA- ERRORS and tagged , , , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s