Change NLS Character set parameters on Oracle 11g

Question

I am using Oracle 11g Express Edition. Currently when I check NLS character set parameter using SELECT * FROM nls_database_parameters; it gives the default values:

NLS_CHARACTERSET: AL32UTF8
NLS_NCHAR_CHARACTERSET: AL16UTF16

I want to set both these parameters to UTF8. How can I do so? I have just installed Oracle 11g XE, so there is not data except those required by Oracle itself.

Answer 1

SQL> shutdown immediate;

SQL> startup restrict

SQL> select name from v$database;

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252 ;

SQL> select value from NLS_DATABASE_PARAMETERS where parameter=’NLS_CHARACTERSET’;

SQL> shutdown immediate;

SQL> startup

SQL> select value from NLS_DATABASE_PARAMETERS where parameter=’NLS_CHARACTERSET’;

Answer 2

This worked for me where an application was checking for UTF8 rather than AL32UTF8 .

SQL> shutdown immediate;

SQL> startup restrict

SQL> select name from v$database;

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8 ;

SQL> select value from NLS_DATABASE_PARAMETERS 
     where parameter=’NLS_CHARACTERSET’;

SQL> shutdown immediate;

SQL> startup

Answer 3

Maybe this works: alter database character set UTF8;

However, are you really sure to change it? AL32UTF8 is UTF-8, actually.

On Oracle you have also a character set called UTF8, this is a kind of misnomer and means Character set CESU-8. As far as I know this is like "UTF-8 as of Unicode version 3 from 1999". As long as you use Unicode characters less than 65535 (Basic Multilingual Plane) AL32UTF8 and UTF8 are identical.

Note, this command works only if the new character set is a strict superset of old character set. I.e. you can change from US7ASCII to AL32UTF8 or WE8ISO8859P1 but you cannot change from WE8ISO8859P1 to AL32UTF8.

Answer 4

You can't change the character set once you set installation. If you want to change then there is long procedure for which you need to raise SR with Oracle.