Change The Database Character Set

In some cases, you may need to change the character set for an existing database.
For example, you may find that the number of languages that need to be supported in your database have increased.

Before,we had to do a full export/import to convert all data to the new character set. And now, if and only if, the new character set is a strict superset of the current character set, it is possible to use the ALTER DATABASE CHARACTER SET to change the database character set.

The target character set is a strict superset if and only if each and every codepoint in the source character set is available in the target character set, with the same corresponding codepoint value. For instance the following migration scenarios can take advantage of the ALTER DATABASE CHARACTER SET command since US7ASCII is astrict subset of WE8ISO8859P1,AL24UTFFSS, and UTF8:

WARNING: Attempting to change the database character set to a character set that is not a strict superset can result in data loss and data corruption. To ensure data integrity, whenever migrating to a new character set that is not a strict superset, you must use export/import. It is essential to do a full backup of the database before using the ALTER DATABASE [NATIONAL] CHARACTER SET statement, since the command cannot be rolled back. The syntax is:

ALTER DATABASE [] CHARACTER SET ;
ALTER DATABASE [] NATIONAL CHARACTER SET
;

The database name is optional. The character set name should be specified without quotes, for example:

ALTER DATABASE CHARACTER SET WE8ISO8859P1;

To change the database character set, perform the following steps. Not all
of them are absolutely necessary, but they are highly recommended:

SQL> CONN / AS SYSDBA
SQL> SHUTDOWN IMMEDIATE;
Take offline backup from your database
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET ;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

To change the national character set, replace the ALTER DATABASE CHARACTER
SET statement with ALTER
DATABASE NATIONAL CHARACTER SET. You can issue both commands together if
desired.