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