2

Hello how do I change or alter the character set of a database for it to support nihongo characters

vikingsteve
  • 38,481
  • 23
  • 112
  • 156
cedric
  • 3,107
  • 15
  • 54
  • 65

2 Answers2

4

You should be fine with any variety of UTF-8.

The character set is usually specified when creating the database. If you need to change it later, there is the ALTER DATABASE CHARACTER SET command, but that requires data migration:

To change the database character set, perform the following steps:

  1. Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
  2. Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
  3. Complete the following statements:
  STARTUP MOUNT;
  ALTER SYSTEM ENABLE RESTRICTED SESSION;
  ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
  ALTER SYSTEM SET AQ_TM_PROCESSES=0;
  ALTER DATABASE OPEN;
  ALTER DATABASE CHARACTER SET new_character_set;
  SHUTDOWN IMMEDIATE; -- or SHUTDOWN NORMAL; 
  STARTUP;

As you can see, you need to offline and convert the database.

Thilo
  • 257,207
  • 101
  • 511
  • 656
  • when altering the charset an error occurs. it says that a superset must be set. i tried altering it to AL32UTF8 – cedric Nov 16 '09 at 07:46
  • To change a character set in a way that may be destructive like this (your original data may no longer be usable), add internal_use: ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8; See http://www.kods.net/modify-the-oracle-character-set-character-set/ – David Fraser Aug 19 '10 at 00:56
  • For anyone interested, I assume the error received must have been [SQL Error: ORA-12712: new character set must be a superset of old character set](http://stackoverflow.com/q/7352304/320399) . Hopefully the answer given will help anyone else that comes across this. – blong Sep 14 '12 at 19:29
  • Command `ALTER DATABASE CHARACTER SET ...` is de-supported since Oracle 10R1, you may destroy your database (see example https://stackoverflow.com/questions/15224015/oracle-11g-xe-greek-character-set-not-displaying). However, if the old character set is `US7ASCII` then this approach may work exceptionally. – Wernfried Domscheit Sep 23 '19 at 12:00
2

And Unicode is your friend, use NVARCHAR or NTEXT.

Mihai Nita
  • 5,547
  • 27
  • 27
  • Funny.. hehe..Never thought of checking the column type.. Tnx – cedric Nov 16 '09 at 08:53
  • While NVARCHAR2 or NCHAR data types will work (assuming the National character set is AL16UTF16), be aware that working with National character set objects generally adds quite a bit of complexity to an application. Different tools often don't work correctly with National character set objects or require that additional flags or settings or made or that different APIs are called. In other words, it's not necessarily a drop-in replacement for VARCHAR2 columns. – Justin Cave Nov 16 '09 at 16:36
  • Partial agreement with Justin :-) Can add some complexity, but can also remove some. But in the last years the Unicode support got better and better across the various tools, and this is definitely the future. – Mihai Nita Nov 17 '09 at 08:35