#########################
BEFORE TEST
#########################
SQL> select * from NLS_DATABASE_PARAMETERS;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1 <-------
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.1.0.7.0
20 rows selected.
################################
----INSTALL CSSCAN UTILITY
################################
SQL> @?/rdbms/admin/csminst.sql
################################################
RUN CSSCAN TO PREPARE CHARACTERSET CONVERSATION ---OUTAGE WILL BE NEEDED FROM THIS POINT.
################################################
ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=SPFILE; -- RAC Only
SHUTDOWN IMMEDIATE;
STARTUP;
TESTGG1@:/u02/data/VISDB1/RMAN:VISDB1$$ORACLE_HOME/bin/csscan \"sys/system as sysdba\" FULL=Y PROCESS=10
Character Set Scanner v2.2 : Release 11.1.0.7.0 - Production on Wed Nov 30 10:31:57 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Current database character set is WE8ISO8859P1.
Enter new database character set name: > AL32UTF8
AL32UTF8
Enter array fetch buffer size: 1024000 >
Enumerating tables to scan...
. process 2 scanning SYS.SOURCE$[AAAADYAABAAAAWgAAA]
THERE WILL 10 PROCESS SCANNING IN PARALLEL,AS WE HAVE GIVEN PROCESS=10
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1312752 bytes
Variable Size 234883088 bytes
Database Buffers 25165824 bytes
Redo Buffers 2277376 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('Y') <> 'Y') then
Checking data validity...
Unrecognized convertible date found in scanner result
PL/SQL procedure successfully completed.
Checking or Converting phrase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
##########################################################################
AT THIS POINT WE CAN DECIDE. IF WE CAN CONVERT CHARACTERSET OR NOT ,
SEE "LOSSY" COLUMN TO DETERMINE HOW MUCH DATA WILL NOT BE MIGRATED .REPORT WILL BE CREATED IN LOCAL DIRECTORY.
##########################################################################
Database Scan Summary Report
Time Started : 2011-11-30 10:32:21
Time Completed: 2011-11-30 10:34:26
Process ID Time Started Time Completed
---------- -------------------- --------------------
1 2011-11-30 10:32:44 2011-11-30 10:34:06
2 2011-11-30 10:32:44 2011-11-30 10:34:06
3 2011-11-30 10:32:44 2011-11-30 10:34:07
4 2011-11-30 10:32:44 2011-11-30 10:34:25
5 2011-11-30 10:32:44 2011-11-30 10:34:07
6 2011-11-30 10:32:48 2011-11-30 10:34:06
7 2011-11-30 10:32:48 2011-11-30 10:34:24
8 2011-11-30 10:32:59 2011-11-30 10:34:06
9 2011-11-30 10:33:03 2011-11-30 10:34:06
10 2011-11-30 10:33:03 2011-11-30 10:34:06
---------- -------------------- --------------------
[Database Size]
Tablespace Used Free Total Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM 695.38M 4.63M 700.00M 1.74M
SYSAUX 544.88M 27.69M 572.56M 11.14M
UNDOTBS1 22.50M 2.50M 25.00M .00K
TEMP .00K .00K .00K .00K
USERS 1.38M 3.63M 5.00M .00K
DRSYS 1,024.00K 9.00M 10.00M .00K
------------------------- --------------- --------------- --------------- ---------------
Total 1,265.13M 47.44M 1,312.56M 12.88M
The size of the largest CLOB is 1625114 bytes
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name VISDB1
Database Version 11.1.0.7.0
Scan type Full database
Scan CHAR data? YES
Database character set WE8ISO8859P1
FROMCHAR WE8ISO8859P1
TOCHAR AL32UTF8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 10
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 3,025,461 0 0 0
CHAR 2,535 0 0 0
LONG 217,463 0 0 0
CLOB 51,922 932 0 0
VARRAY 54,938 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 3,352,319 932 0 0
Total in percentage 99.972% 0.028% 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 439 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 439 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,653,685 0 0 0
CHAR 101 0 0 0
LONG 10 0 0 0
CLOB 20,754 8,859 0 0
VARRAY 6,758 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 2,681,308 8,859 0 0
Total in percentage 99.671% 0.329% 0.000% 0.000%
[Distribution of Convertible, Truncated and Lossy Data by Table]
Data Dictionary Tables:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
MDSYS.OPENLS_NODES 17 0 0
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 3 0 0
ORDSYS.ORDDCM_CT_PRED_OPRD 51 0 0
ORDSYS.ORDDCM_DOCS 9 0 0
ORDSYS.ORDDCM_MAPPING_DOCS 1 0 0
SYS.METASTYLESHEET 158 0 0
SYS.RULE$ 1 0 0
SYS.WRH$_SQLTEXT 124 0 0
SYS.WRH$_SQL_PLAN 83 0 0
SYS.WRI$_ADV_DIRECTIVE_META 5 0 0
SYS.WRI$_ADV_OBJECTS 1 0 0
SYS.WRI$_DBU_FEATURE_METADATA 149 0 0
SYS.WRI$_DBU_FEATURE_USAGE 13 0 0
SYS.WRI$_DBU_HWM_METADATA 19 0 0
SYS.WRI$_REPT_FILES 19 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
FLOWS_030000.WWV_FLOW_BUTTON_TEMPLATES 35 0 0
FLOWS_030000.WWV_FLOW_CUSTOM_AUTH_SETUPS 18 0 0
FLOWS_030000.WWV_FLOW_FLASH_CHART_SERIES 6 0 0
FLOWS_030000.WWV_FLOW_LIST_TEMPLATES 246 0 0
FLOWS_030000.WWV_FLOW_PAGE_GENERIC_ATTR 44 0 0
FLOWS_030000.WWV_FLOW_PAGE_PLUGS 3,126 0 0
FLOWS_030000.WWV_FLOW_PAGE_PLUG_TEMPLATES 219 0 0
FLOWS_030000.WWV_FLOW_PROCESSING 43 0 0
FLOWS_030000.WWV_FLOW_ROW_TEMPLATES 54 0 0
FLOWS_030000.WWV_FLOW_SHORTCUTS 38 0 0
FLOWS_030000.WWV_FLOW_STEPS 1,631 0 0
FLOWS_030000.WWV_FLOW_STEP_PROCESSING 1,927 0 0
FLOWS_030000.WWV_FLOW_TEMPLATES 174 0 0
SYSMAN.MGMT_IP_ELEM_DEFAULT_PARAMS 130 0 0
SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS 1,137 0 0
SYSMAN.MGMT_IP_SQL_STATEMENTS 31 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Distribution of Convertible, Truncated and Lossy Data by Column]
Data Dictionary Tables:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
MDSYS.OPENLS_NODES|SYS_NC00004$ 17 0 0
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 3 0 0
ORDSYS.ORDDCM_CT_PRED_OPRD|SYS_NC00004$ 51 0 0
ORDSYS.ORDDCM_DOCS|SYS_NC00005$ 9 0 0
ORDSYS.ORDDCM_MAPPING_DOCS|SYS_NC00007$ 1 0 0
SYS.METASTYLESHEET|STYLESHEET 158 0 0
SYS.RULE$|CONDITION 1 0 0
SYS.WRH$_SQLTEXT|SQL_TEXT 124 0 0
SYS.WRH$_SQL_PLAN|OTHER_XML 83 0 0
SYS.WRI$_ADV_DIRECTIVE_META|DATA 5 0 0
SYS.WRI$_ADV_OBJECTS|ATTR4 1 0 0
SYS.WRI$_DBU_FEATURE_METADATA|INST_CHK_LOGIC 17 0 0
SYS.WRI$_DBU_FEATURE_METADATA|USG_DET_LOGIC 132 0 0
SYS.WRI$_DBU_FEATURE_USAGE|FEATURE_INFO 13 0 0
SYS.WRI$_DBU_HWM_METADATA|LOGIC 19 0 0
SYS.WRI$_REPT_FILES|SYS_NC00005$ 19 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
FLOWS_030000.WWV_FLOW_BUTTON_TEMPLATES|TEMPLATE 35 0 0
FLOWS_030000.WWV_FLOW_CUSTOM_AUTH_SETUPS|AUTH_FUN 8 0 0
FLOWS_030000.WWV_FLOW_CUSTOM_AUTH_SETUPS|PAGE_SEN 9 0 0
FLOWS_030000.WWV_FLOW_CUSTOM_AUTH_SETUPS|POST_AUT 1 0 0
FLOWS_030000.WWV_FLOW_FLASH_CHART_SERIES|SERIES_Q 6 0 0
FLOWS_030000.WWV_FLOW_LIST_TEMPLATES|ITEM_TEMPLAT 16 0 0
FLOWS_030000.WWV_FLOW_LIST_TEMPLATES|ITEM_TEMPLAT 16 0 0
FLOWS_030000.WWV_FLOW_LIST_TEMPLATES|LIST_TEMPLAT 91 0 0
FLOWS_030000.WWV_FLOW_LIST_TEMPLATES|LIST_TEMPLAT 91 0 0
FLOWS_030000.WWV_FLOW_LIST_TEMPLATES|SUB_LIST_ITE 8 0 0
FLOWS_030000.WWV_FLOW_LIST_TEMPLATES|SUB_LIST_ITE 8 0 0
FLOWS_030000.WWV_FLOW_LIST_TEMPLATES|SUB_TEMPLATE 8 0 0
FLOWS_030000.WWV_FLOW_LIST_TEMPLATES|SUB_TEMPLATE 8 0 0
FLOWS_030000.WWV_FLOW_PAGE_GENERIC_ATTR|ATTRIBUTE 44 0 0
FLOWS_030000.WWV_FLOW_PAGE_PLUGS|PLUG_SOURCE 3,126 0 0
FLOWS_030000.WWV_FLOW_PAGE_PLUG_TEMPLATES|TEMPLAT 139 0 0
FLOWS_030000.WWV_FLOW_PAGE_PLUG_TEMPLATES|TEMPLAT 80 0 0
FLOWS_030000.WWV_FLOW_PROCESSING|PROCESS_SQL_CLOB 43 0 0
FLOWS_030000.WWV_FLOW_ROW_TEMPLATES|ROW_TEMPLATE1 44 0 0
FLOWS_030000.WWV_FLOW_ROW_TEMPLATES|ROW_TEMPLATE2 8 0 0
FLOWS_030000.WWV_FLOW_ROW_TEMPLATES|ROW_TEMPLATE3 2 0 0
FLOWS_030000.WWV_FLOW_SHORTCUTS|SHORTCUT 38 0 0
FLOWS_030000.WWV_FLOW_STEPS|HELP_TEXT 1,404 0 0
FLOWS_030000.WWV_FLOW_STEPS|HTML_PAGE_HEADER 227 0 0
FLOWS_030000.WWV_FLOW_STEP_PROCESSING|PROCESS_SQL 1,927 0 0
FLOWS_030000.WWV_FLOW_TEMPLATES|BOX 58 0 0
FLOWS_030000.WWV_FLOW_TEMPLATES|FOOTER_TEMPLATE 58 0 0
FLOWS_030000.WWV_FLOW_TEMPLATES|HEADER_TEMPLATE 58 0 0
SYSMAN.MGMT_IP_ELEM_DEFAULT_PARAMS|VALUE 130 0 0
SYSMAN.MGMT_IP_REPORT_ELEM_PARAMS|VALUE 1,137 0 0
SYSMAN.MGMT_IP_SQL_STATEMENTS|SQL_STATEMENT 31 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Indexes to be Rebuilt]
USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
############################################################
CHANGE CHARACTERSET
############################################################
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CSMIG_SCHEMA_VERSION 5
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8 <-----
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.1.0.7.0
