Character set字符集

Character setMILY: 宋體; mso-ascii-font-family: "Times New Roman"; mso-hansi-font-family: "Times New Roman"">字符集

作者：zyqin

創建：2005-03-25

英文：http://www.dbonline.cn

If the database has been created with the wrong character set, use the following to change the character set

UPDATE SYS.PROPS$

SET VALUES$ = 'WE8ISO8859P1'

WHERE NAME = 'NLS_CHARACTERSET';

It is VERY important to specify the character set name correctly. If the NLS_CHARACTERSET is updated to an invalid value, it will not then be possible to restart the database once it has been shutdown.

如果已經創建的數據庫使用了錯誤的字符集，你可以使用下面的方法修改字符集：

UPDATE SYS.PROPS$

SET VALUES$ = 'WE8ISO8859P1'

WHERE NAME = 'NLS_CHARACTERSET';

注意：確保字符集名稱的準確性是非常重要的。如果NLS_CHARACTERSET被更新成一個錯誤的值，數據庫關閉后就無法重新啟動了。

Update

――――――――――――――――――――――――――――――――――――

From version 8 you can now update the character set using the alter database command. An extract from the documentation is included below, the basic rule being that the new characterset must be a superset of the current set. Interestingly enough, I tried the above update with a characterset that violated that rule, it still works - of course, any character code differences will would inevitably cause some problems, so it would be safest to use the supported syntax.

Quote from the (8.1.6) documentation reveals:

Oracle8以后的數據庫可以通過使用alter database 命令修改字符集。引用文檔中的一句話：新的字符集必須是原來字符集的一個超集。有意思的是，我曾經更新字符集是違反了這一原則，數據庫照樣工作；當然了，任何字符碼都可能導致同樣的問題，所以最安全的方法還是遵照規則辦事。

――――――――――――――――――――――――――――――――――――

"Changing the Character Set After Database Creation

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.

有時候，你可能需要修改當前數據庫的字符集。這時你會發現你需要增加數據庫支持的原因的種類。多數情況下，你可能需要通過完全的export/import導入/導出來實現。如果，僅僅是如果，新的字符集是當前字符集的一個嚴格的超集，使用ALTER 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 a strict subset of WE8ISO8859P1, AL24UTFFSS, and UTF8:

Current Character Set New Character Set New Character Set is strict superset?

US7ASCII WE8ISO8859P1 yes

US7ASCII ALT24UTFFSS yes

US7ASCII UTF8 yes

如果A字符集中的每一個codepoint在B字符集中都有相同的有效值，那么B字符集就可以成為A字符集的一個超集。例如，下面的遷移方案可以使用ALTER DATABASE CHARACTER SET命令，因為US7ASCII是WE8ISO8859P1, AL24UTFFSS和 UTF8的超集。

當前字符集                  新字符集              新字符集是否為超集

US7ASCII                    WE8ISO8859P1       是

US7ASCII                    ALT24UTFFSS        是

US7ASCII                    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 [<db_name>] CHARACTER SET <new_character_set>;

ALTER DATABASE [<db_name>] NATIONAL CHARACTER SET <new_NCHAR_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> SHUTDOWN IMMEDIATE; -- or NORMAL

<do a full backup>

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 <new_character_set_name>;

SQL> SHUTDOWN IMMEDIATE; -- or NORMAL

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."