How to configure UTF8 character set in Oracle?

Question

How to configure UTF8 character set in Oracle? I've tried to update as follow :

UPDATE NLS_DATABASE_PARAMETERS SET VALUE = 'UTF8' WHERE PARAMETER='NLS_CHARACTERSET';

But the error show like that

ERROR at line 1:
ORA-01031: insufficient privileges

Answer 1

Yeah, good thing that failed too...that's not a good idea... :-)

First off, for UTF-8 support in Oracle, you'll want to use the 'AL32UTF8' character set.

Is this a brand new database? Or an existing database with data already in it?

If we're talking new database, you should just drop the database, and create it again, specifying the AL32UTF8 character set in DBCA, when you create the database.

If it's an existing database, with data already in it, it's a bit more complicated. What character set does it currently use? Is AL32UTF8 a direct superset of that character set?

This is really a non-trivial subject. In short, you need to know the current character set, and the character set you're moving to, and you'll need to run the 'CSSCAN' utility to check for compatibility and data conversion problems.

For the full story, you should check the following document on MOS:

Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) [ID 260192.1]

Hope that helps.