Introduction
In some cases, you may wish to change the existing database character set. For instance, you may find that the number of languages that need to be supported in your database have increased. In most cases, you will need to do a full export/import to properly convert all data to the new character set. However, 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 expedite the change in 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, by firing ALTER DATABASE CHARACTER SET command US7ASCII can be converted to UTF8, WE8ISO8859P1 and AL24UTFFSS. This is because US7ASCII is a strict subset of WE8ISO8859P1, AL24UTFFSS, and UTF8.
Note: 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.
To change the database character set, perform the following steps:
1 Shutdown 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 STARTUP MOUNT;
4 ALTER SYSTEM ENABLE RESTRICTED SESSION;
5 ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
6 ALTER SYSTEM SET AQ_TM_PROCESSES=0;
7 ALTER DATABASE OPEN;
8 ALTER DATABASE CHARACTER SET new_character_set;
9 SHUTDOWN IMMEDIATE;
10 STARTUP;
Thanks,
Vijay Kumar (Win)
|
No responses found. Be the first to respond and make money from revenue sharing program.
|