Below query will return the name of character set in the database:
select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';
We can't simply use "ALTER DATABASE CHARACTER SET" to go from WE8ISO8859P1 or
WE8ISO8859P15 or WE8MSWIN1252 to Unicode AL32UTF8 (or UTF8)
because AL32UTF8 is not a binary superset of any of these character
sets.
For 10g database, the conversion is easier because the
scan tool (csscan) was installed for you during the 10g installation. I
followed Metalink Doc ID 260192.1 to do the conversion on a 10g
database.
1. Run $ORACLE_HOME/rdbms/admin/csminst.sql by 'sys as sysdba' to create database objects and a user for the objects.
2. Check if you have no invalid code points in the current character set. Run below line by 'sys as sysdba':
csscan FULL=Y FROMCHAR=WE8ISO8859P1 TOCHAR=WE8ISO8859P1 LOG=WE8check CAPTURE=Y ARRAY=1000000 PROCESS=2
The Scan Summary Report WE8check.txt shows only "Changeless data".
3. Check which rows contain data for which the code point will change. Run csscan:
csscan FULL=Y FROMCHAR=WE8ISO8859P1 TOCHAR=AL32UTF8 LOG=WE8TOUTF8 CAPTURE=Y ARRAY=1000000 PROCESS=2
Part of the WE8TOUTF8.txt is
[Data Dictionary Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- -----------
VARCHAR2 6,067,118 1 0 0
CHAR 448 0 0 0
LONG 151,405 0 0 0
CLOB 63,023 7,385 0 0
VARRAY 20,839 0 0 0
--------------------- ---------------- ---------------- ---------------- -----------
Total 6,302,833 7,386 0 0
Total in percentage 99.883% 0.117% 0.000% 0.000%
The data dictionary can not be safely migrated using the CSALTER script
[Application Data Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- -----------
VARCHAR2 348,264,895 84,466 102 0
CHAR 339,986,561 114 195 0
LONG 1,270 0 0 0
CLOB 0 0 0 0
VARRAY 1,525 0 0 0
--------------------- ---------------- ---------------- ---------------- -----------
Total 688,254,251 84,580 297 0
Total in percentage 99.988% 0.012% 0.000% 0.000%
- Drop snapshot
SQL> select min(snap_id) min_snapid ,max(snap_id) max_snapid from dba_hist_snapshot
SQL> execute dbms_workload_repository.drop_snapshot_range(3737, 3905);
- Need to handle foreign keys (Doc ID 1039297.6)
