Changing National Character Set AL16UTF16 to UTF8

Changing National Character Set AL16UTF16 to UTF8

The national character set is used for data that is stored in table columns of the types NCHAR, NVARCHAR2, and NCLOB. 

In contrast, the database character set is used for data stored in table columns of the types CHAR, VARCHAR2 and CLOB.

Like the database character set, the national character set is defined when the database is initially created and can usually no longer be changed, at least not easily or without involving quite a lot of work (export, recreate database, import).

Except when creating the database, where the national character set is defined explicitly, it can change implicitly even when upgrading the database from Oracle8i to Oracle9i (or Oracle10g).

You require SYSDBA authorization to change the national character set. Changing the national character set means changing an Oracle Dictionary entry, but no data is changed. 

$sqlplus /nolog

SQL> CONNECT / AS SYSDBA

SQL> Select property_value from database_properties

     where upper(property_name) = 'NLS_NCHAR_CHARACTERSET';

SQL> Select owner, table_name, column_name

     from dba_tab_columns

     where (data_type = 'NCHAR' or data_type = 'NVARCHAR2' or data_type = 'NCLOB') and

 owner != 'SYS' and owner != 'SYSTEM';

Note: If there are no table columns of the types NCHAR, NVARCHAR2 or NCLOB on the database, you can change the national character set without encountering any problems. However, if the database contains tables with NCHAR data type columns, you should perform a check to see whether these columns also contain data.

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 SCOPE = MEMORY;

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0 SCOPE = MEMORY;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE NATIONAL CHARACTER SET UTF8;

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP 

On the other hand, oracle recommendation is that you create your database with the AL32UTF8 database character set, and move all your national character set data to normal VARCHAR2 and CHAR columns.

It is always not better choice to use 'ALTER DATABASE' command to change the character set. Check the Documentation for better solution:

CAUTION: We recommend that you perform a full database backup before changing the national character set.