Changing National Character Set AL16UTF16 to UTF8

Changing National Character Set AL16UTF16 to UTF8 on 12c PDB

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,


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.

Connect PDB as sysdba

[oracle@CDB ~]$ sqlplus sys/password@SID 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';

SQL> select * from v$nls_parameters where parameter like '%CHARACTERSET%';

PARAMETER
----------------------------------------------------------------
VALUE                                                                CON_ID
---------------------------------------------------------------- ----------
NLS_CHARACTERSET
AL32UTF8                                                                  0
NLS_NCHAR_CHARACTERSET
AL16UTF16                                                                 0

SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

PARAMETER                                                VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET                   AL16UTF16



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> STARTUP RESTRICT;
Pluggable Database opened.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> update props$
set value$='UTF8'
where name in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET'); 

2 rows updated.

SQL> commit;

Commit complete.

SQL> SHUTDOWN IMMEDIATE;
Pluggable Database closed.

SQL> ALTER DATABASE OPEN ;
Database altered.

SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';

PARAMETER                                VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET                    UTF8