How to Convert NLS Character set in Oracle:
In this article, I will be converting character set of a 19c oracle database from WE8MSWIN1252 to AL32UTF8
Steps to be followed:
Prerequisites:
Run the below query and keep record of the existing information.
select value from NLS_DATABASE_PARAMETERS where Parameter='NLS_CHARACTERSET';
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
select * from v$nls_parameters where parameter like '%CHARACTERSET';
select userenv('language') from dual;
For my example this is the output:
SQL> select value from NLS_DATABASE_PARAMETERS where Parameter='NLS_CHARACTERSET';
VALUE
----------------------------------------------------------------
WE8MSWIN1252
SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
VALUE
----------------------------------------------------------------
AL16UTF16
SQL> select * from v$nls_parameters where parameter like '%CHARACTERSET';
PARAMETER
----------------------------------------------------------------
VALUE CON_ID
---------------------------------------------------------------- ----------
NLS_CHARACTERSET
WE8MSWIN1252 0
NLS_NCHAR_CHARACTERSET
AL16UTF16 0
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8MSWIN1252
Now run the below query to see the datatype it is using:
select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$
where charsetform in (1,2)
and type# in (1, 9, 96, 112)
order by CHARACTERSET;
Example output:
SQL> select distinct(nls_charset_name(charsetid)) CHARACTERSET,
2 decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
3 9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
4 96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112) order by CHARACTERSET; 5 6
CHARACTERSET TYPES_USED_IN
---------------------------------------- -------------
AL16UTF16 NCHAR
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
WE8MSWIN1252 CHAR
WE8MSWIN1252 CLOB
WE8MSWIN1252 VARCHAR2
6 rows selected.
Now check the Database Size:
col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/
Backup:
Take a full backup of the database. Either through RMAN or Expdp, depending on the db size and your database setup.
For RMAN: follow this link
For EXPDP : follow this link
Once the backup is done, check there are ample archive space available.
If you would like, you can create a restore point for easy revert back of the change.
create restore point pre_charset guarantee flashback database;
Check the restore point is created or not.
set lines 400
set pages 300
col name for a20
col time for a40
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT
where GUARANTEE_FLASHBACK_DATABASE='YES';
check Invalid count:
select count(*) from dba_objects where status='INVALID';
Invalid object count schema wise:
set pages 300
set lines 300
col owner for a30
select owner, count(*) from dba_objects where status='INVALID' group by owner;
If there are numerous invalid count, run utlrp.sql to recompile the invalid objects:
SQL>@?/rdbms/admin/utlrp.sql
Change the character set:
Now follow the below steps to convert the characterset:
--Proceed to alter the database
sqlplus / as sysdba
shutdown immediate
startup Restrict
SQL> sho parameter job_queue_processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 160
SQL> show parameter aq_tm_processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
System altered.
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
System altered.
ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
-- After changing the character set bounce the database
shutdown immediate
startup
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=160;
ALTER SYSTEM SET AQ_TM_PROCESSES=1;
Now check for the character set again to validate the change is successful:
Query:
select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
from sys.col$
where charsetform in (1,2)
and type# in (1, 9, 96, 112)
order by CHARACTERSET;
Example:
SQL> select distinct(nls_charset_name(charsetid)) CHARACTERSET,
2 decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
3 9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
4 96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
5 6 from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112) order by CHARACTERSET;
CHARACTERSET TYPES_USED_IN
---------------------------------------- -------------
AL16UTF16 NCHAR
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
AL32UTF8 CHAR
AL32UTF8 CLOB
AL32UTF8 VARCHAR2
6 rows selected.
check invalid Count:
select count(*) from dba_objects where status='INVALID';
Invalid Count schema wise:
set pages 300
set lines 300
col owner for a30
select owner, count(*) from dba_objects where status='INVALID' group by owner;
And it completes the characterset changes. for few cases it might not work, in that case you can use Oracle Data Migration Assistant for Unicode (DMU), to learn more about how to use DMU follow the below link.
Rollback:
In case you want to revert back the change, you can revert to the restore point you just created before making the changes.
-- Flashback the database to the restore point
shutdown immediate;
startup mount;
flashback database to restore point pre_charset;
alter database open resetlogs;
Note: This is done in Development Instance, before running it in production, please make sure you validated all the data.
Best Wishes!!
