Oracle Character set字符集

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

警告：如果修改后的数据库字符集不是当前字符集的一个超集，后果可能是数据丢失和数据错误。为了确保数据的完整性，修改字符集前一定要进行export/import导出/导入工作。在使用ALTER DATABASE [NATIONAL] CHARACTER SET命令修改数据库字符集前，对数据库进行完整的备份是十分必要的，因为这个命令导致的结果是无法通过回滚来恢复的。命令格式如下：

ALTER DATABASE [<db_name>] CHARACTER SET <new_character_set>;

ALTER DATABASE [<db_name>] NATIONAL CHARACTER SET <new_NCHAR_character_set>;

db_name是可选参数，new_character_set的值不能为空。例如

ALTER DATABASE CHARACTER SET WE8ISO8859P1;

在改变数据库字符集之前进行一下操作，其中并不是决定必须的，但是强烈推荐进行如下操作：

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;

修改国家字符集使用命令ALTER DATABASE NATIONAL CHARACTER SET替换

ALTER DATABASE CHARACTER SET，需要的时候可以一起执行。

http://blog.donews.com/zyqin/archive/2005/04/02/321036.aspx

http://oracle.ittoolbox.com/groups/technical-functional/oracle-db-l/changing-character-set-of-oracle-database-87376#