This discussion is archived
2 Replies Latest reply: Oct 22, 2012 10:31 AM by user8100138 RSS

CSSCAN in 11g - Characterset not changing from WE8IMSWIN1252 to AL32UTF8

ramarun Newbie
Currently Being Moderated
All,
We have installed a 11g database in Linux box and once after that we wanted to change the character set to AL32UTF8 from default WE8MSWIN1252.

We took the cs-alter approach and ran cs-scan utility, upon going through csscan.txt files generated by csscan utility we found that there are no lossy data but convertible data was found in data dictionary. Below is the output from csscan.txt

This is the Scan Summary

*[Scan Summary]*

All character type data in the data dictionary are convertible to the new character set
All character type application data are convertible to the new character set


Database Scan Summary Report

Time Started  : 2012-10-17 21:42:17
Time Completed: 2012-10-17 21:42:47

Process ID         Time Started       Time Completed
---------- -------------------- --------------------
         1  2012-10-17 21:42:18  2012-10-17 21:42:46
         2  2012-10-17 21:42:18  2012-10-17 21:42:46
         3  2012-10-17 21:42:18  2012-10-17 21:42:46
---------- -------------------- --------------------

[Database Size]

Tablespace                           Used            Free           Total       Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM                            709.75M         256.00K         710.00M           2.42M
SYSAUX                            645.63M          34.38M         680.00M          12.52M
UNDOTBS1                           13.13M          16.88M          30.00M            .00K
TEMP                                 .00K            .00K            .00K            .00K
USERS                               1.31M           3.69M           5.00M            .00K
HYPE_DATA                       1,024.00K      19,999.00M      20,000.00M            .00K
HYPE_INDX                       1,024.00K      19,999.00M      20,000.00M            .00K
------------------------- --------------- --------------- --------------- ---------------
Total                           1,371.81M      40,053.19M      41,425.00M          14.94M

The size of the largest CLOB is 1625114 bytes

[Database Scan Parameters]

Parameter                      Value
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1
Instance Name                  dvhp081
Database Version               11.2.0.3.0
Scan type                      Full database
Scan CHAR data?                YES
Database character set         WE8MSWIN1252
FROMCHAR                       WE8MSWIN1252
TOCHAR                         al32utf8
Scan NCHAR data?               NO
Array fetch buffer size        10240
Number of processes            3
Capture convertible data?      NO
------------------------------ ------------------------------------------------

[Scan Summary]

All character type data in the data dictionary are convertible to the new character set
All character type application data are convertible to the new character set
[Data Dictionary Conversion Summary]

Data Dictionary Tables:

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     5,408,302                0                0                0
CHAR                             4,261                0                0                0
LONG                           249,018                0                0                0
CLOB                            67,652            3,794                0                0
VARRAY                          49,807                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        5,779,040            3,794                0                0
Total in percentage             99.934%           0.066%           0.000%           0.000%

The data dictionary can be safely migrated using the CSALTER script

XML CSX Dictionary Tables:

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                           702                0                0                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
CLOB                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                              702                0                0                0
Total in percentage            100.000%           0.000%           0.000%           0.000%

[Application Data Conversion Summary]

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     2,550,581                0                0                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
CLOB                            22,187            8,287                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        2,572,768            8,287                0                0
Total in percentage             99.679%           0.321%           0.000%           0.000%

[Distribution of Convertible, Truncated and Lossy Data by Table]

Data Dictionary Tables:

USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
MDSYS.SDO_COORD_OP_PARAM_VALS                                   200                0                0
MDSYS.SDO_GEOR_XMLSCHEMA_TABLE                                    1                0                0
MDSYS.SDO_STYLES_TABLE                                           78                0                0
MDSYS.SDO_XML_SCHEMAS                                             5                0                0
SYS.METASTYLESHEET                                              179                0                0
SYS.RULE$                                                         1                0                0
SYS.SCHEDULER$_EVENT_LOG                                        356                0                0
SYS.WRH$_SQLTEXT                                                537                0                0
SYS.WRH$_SQL_PLAN                                               514                0                0
SYS.WRI$_ADV_DIRECTIVE_META                                       5                0                0
SYS.WRI$_ADV_OBJECTS                                             28                0                0
SYS.WRI$_ADV_SQLT_PLANS                                           2                0                0
SYS.WRI$_ADV_SQLT_PLAN_STATS                                      2                0                0
SYS.WRI$_DBU_FEATURE_METADATA                                   193                0                0
SYS.WRI$_DBU_FEATURE_USAGE                                        9                0                0
SYS.WRI$_DBU_HWM_METADATA                                        21                0                0
SYS.WRI$_REPT_FILES                                              27                0                0
SYSMAN.MGMT_IP_ELEM_DEFAULT_PARAMS                              130                0                0
SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS                             1,475                0                0
SYSMAN.MGMT_IP_SQL_STATEMENTS                                    31                0                0
-------------------------------------------------- ---------------- ---------------- ----------------

XML CSX Dictionary Tables:

USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

Application Data:

USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
APEX_030200.WWV_FLOW_BANNER                                      10                0                0
APEX_030200.WWV_FLOW_BUTTON_TEMPLATES                            12                0                0
APEX_030200.WWV_FLOW_CUSTOM_AUTH_SETUPS                          19                0                0
APEX_030200.WWV_FLOW_FLASH_CHART_SERIES                           5                0                0
APEX_030200.WWV_FLOW_LIST_TEMPLATES                             298                0                0
APEX_030200.WWV_FLOW_PAGE_GENERIC_ATTR                           44                0                0
APEX_030200.WWV_FLOW_PAGE_PLUGS                               3,240                0                0
APEX_030200.WWV_FLOW_PAGE_PLUG_TEMPLATES                        254                0                0
APEX_030200.WWV_FLOW_PROCESSING                                  45                0                0
APEX_030200.WWV_FLOW_ROW_TEMPLATES                               66                0                0
APEX_030200.WWV_FLOW_SHORTCUTS                                   39                0                0
APEX_030200.WWV_FLOW_STEPS                                    1,795                0                0
APEX_030200.WWV_FLOW_STEP_PROCESSING                          2,238                0                0
APEX_030200.WWV_FLOW_TEMPLATES                                  192                0                0
APEX_030200.WWV_FLOW_WORKSHEETS                                  30                0                0
-------------------------------------------------- ---------------- ---------------- ----------------

[Distribution of Convertible, Truncated and Lossy Data by Column]

Data Dictionary Tables:

USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
MDSYS.SDO_COORD_OP_PARAM_VALS|PARAM_VALUE_FILE                  200                0                0
MDSYS.SDO_GEOR_XMLSCHEMA_TABLE|XMLSCHEMA                          1                0                0
MDSYS.SDO_STYLES_TABLE|DEFINITION                                78                0                0
MDSYS.SDO_XML_SCHEMAS|XMLSCHEMA                                   5                0                0
SYS.METASTYLESHEET|STYLESHEET                                   179                0                0
SYS.RULE$|CONDITION                                               1                0                0
SYS.SCHEDULER$_EVENT_LOG|ADDITIONAL_INFO                        356                0                0
SYS.WRH$_SQLTEXT|SQL_TEXT                                       537                0                0
SYS.WRH$_SQL_PLAN|OTHER_XML                                     514                0                0
SYS.WRI$_ADV_DIRECTIVE_META|DATA                                  5                0                0
SYS.WRI$_ADV_OBJECTS|ATTR4                                       28                0                0
SYS.WRI$_ADV_SQLT_PLANS|OTHER_XML                                 2                0                0
SYS.WRI$_ADV_SQLT_PLAN_STATS|OTHER                                2                0                0
SYS.WRI$_DBU_FEATURE_METADATA|INST_CHK_LOGIC                     22                0                0
SYS.WRI$_DBU_FEATURE_METADATA|USG_DET_LOGIC                     171                0                0
SYS.WRI$_DBU_FEATURE_USAGE|FEATURE_INFO                           9                0                0
SYS.WRI$_DBU_HWM_METADATA|LOGIC                                  21                0                0
SYS.WRI$_REPT_FILES|SYS_NC00005$                                 27                0                0
SYSMAN.MGMT_IP_ELEM_DEFAULT_PARAMS|VALUE                        130                0                0
SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS|VALUE                       1,475                0                0
SYSMAN.MGMT_IP_SQL_STATEMENTS|SQL_STATEMENT                      31                0                0
-------------------------------------------------- ---------------- ---------------- ----------------

XML CSX Dictionary Tables:

USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

Application Data:

USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
APEX_030200.WWV_FLOW_BANNER|BANNER                               10                0                0
APEX_030200.WWV_FLOW_BUTTON_TEMPLATES|TEMPLATE                   12                0                0
APEX_030200.WWV_FLOW_CUSTOM_AUTH_SETUPS|AUTH_FUNC                 8                0                0
APEX_030200.WWV_FLOW_CUSTOM_AUTH_SETUPS|PAGE_SENT                10                0                0
APEX_030200.WWV_FLOW_CUSTOM_AUTH_SETUPS|POST_AUTH                 1                0                0
APEX_030200.WWV_FLOW_FLASH_CHART_SERIES|SERIES_QU                 5                0                0
APEX_030200.WWV_FLOW_LIST_TEMPLATES|ITEM_TEMPLATE                20                0                0
APEX_030200.WWV_FLOW_LIST_TEMPLATES|ITEM_TEMPLATE                20                0                0
APEX_030200.WWV_FLOW_LIST_TEMPLATES|LIST_TEMPLATE               105                0                0
APEX_030200.WWV_FLOW_LIST_TEMPLATES|LIST_TEMPLATE               105                0                0
APEX_030200.WWV_FLOW_LIST_TEMPLATES|SUB_LIST_ITEM                12                0                0
APEX_030200.WWV_FLOW_LIST_TEMPLATES|SUB_LIST_ITEM                12                0                0
APEX_030200.WWV_FLOW_LIST_TEMPLATES|SUB_TEMPLATE_                12                0                0
APEX_030200.WWV_FLOW_LIST_TEMPLATES|SUB_TEMPLATE_                12                0                0
APEX_030200.WWV_FLOW_PAGE_GENERIC_ATTR|ATTRIBUTE_                44                0                0
APEX_030200.WWV_FLOW_PAGE_PLUGS|PLUG_SOURCE                   3,240                0                0
APEX_030200.WWV_FLOW_PAGE_PLUG_TEMPLATES|TEMPLATE               166                0                0
APEX_030200.WWV_FLOW_PAGE_PLUG_TEMPLATES|TEMPLATE                88                0                0
APEX_030200.WWV_FLOW_PROCESSING|PROCESS_SQL_CLOB                 45                0                0
APEX_030200.WWV_FLOW_ROW_TEMPLATES|ROW_TEMPLATE1                 54                0                0
APEX_030200.WWV_FLOW_ROW_TEMPLATES|ROW_TEMPLATE2                 10                0                0
APEX_030200.WWV_FLOW_ROW_TEMPLATES|ROW_TEMPLATE3                  2                0                0
APEX_030200.WWV_FLOW_SHORTCUTS|SHORTCUT                          39                0                0
APEX_030200.WWV_FLOW_STEPS|HELP_TEXT                          1,513                0                0
APEX_030200.WWV_FLOW_STEPS|HTML_PAGE_HEADER                     282                0                0
APEX_030200.WWV_FLOW_STEP_PROCESSING|PROCESS_SQL_             2,238                0                0
APEX_030200.WWV_FLOW_TEMPLATES|BOX                               64                0                0
APEX_030200.WWV_FLOW_TEMPLATES|FOOTER_TEMPLATE                   64                0                0
APEX_030200.WWV_FLOW_TEMPLATES|HEADER_TEMPLATE                   64                0                0
APEX_030200.WWV_FLOW_WORKSHEETS|SQL_QUERY                        30                0                0
-------------------------------------------------- ---------------- ---------------- ----------------

[Indexes to be Rebuilt]

USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
APEX_030200.WWV_FLOW_WORKSHEETS_UNQ_IDX on APEX_030200.WWV_FLOW_WORKSHEETS(SYS_NC00078$)
APEX_030200.WWV_FLOW_WORKSHEETS_UNQ_IDX on APEX_030200.WWV_FLOW_WORKSHEETS(SYS_NC00079$)
APEX_030200.WWV_FLOW_WORKSHEETS_UNQ_IDX on APEX_030200.WWV_FLOW_WORKSHEETS(SYS_NC00080$)
APEX_030200.WWV_FLOW_WORKSHEETS_UNQ_IDX on APEX_030200.WWV_FLOW_WORKSHEETS(SYS_NC00081$)
APEX_030200.WWV_FLOW_WS_UNQ_ALIAS_IDX on APEX_030200.WWV_FLOW_WORKSHEETS(SYS_NC00082$)
APEX_030200.WWV_FLOW_WS_UNQ_ALIAS_IDX on APEX_030200.WWV_FLOW_WORKSHEETS(ALIAS)
----------------------------------------------------------------------------------
We followed few metalink documents *Solving Convertible or Lossy data in Data Dictionary objects reported by Csscan when changing the NLS_CHARACTERSET [ID 258904.1]* and found that we are good to go as convertible was found only in data dictionary and that too CLOB data. But while running csalter.plb csalter came out without changing the characterset. We ran the following query given the said document and it returned no rows which again confirms there is no problem and go ahead with running csalter.
SELECT DISTINCT z.owner_name
|| '.'
|| z.table_name
|| '('
|| z.column_name
|| ') - '
|| z.column_type
|| ' - '
|| z.error_type
|| ' ' NotHandledDataDictColumns
FROM csmig.csmv$errors z
WHERE z.owner_name IN
(SELECT DISTINCT username FROM csmig.csm$dictusers
) minus
SELECT DISTINCT z.owner_name
|| '.'
|| z.table_name
|| '('
|| z.column_name
|| ') - '
|| z.column_type
|| ' - '
|| z.error_type
|| ' ' DataDictConvCLob
FROM csmig.csmv$errors z
WHERE z.error_type ='CONVERTIBLE'
AND z.column_type = 'CLOB'
AND z.owner_name IN
(SELECT DISTINCT username FROM csmig.csm$dictusers
)
ORDER BY NotHandledDataDictColumns
/
Sorry to have made the thread so big but to make sure and give a complete picture of the issue pasted the csscan contents. Request the PRO's to help us in this issue.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points