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.