Oracle DB NLS Characterset Conversion

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!!