Character Set Character Set

Source: Internet
Author: User
Tags character set documentation modify sql

Character Set Character Set

Author: zyqin

Created: 2005-03-25


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 a invalid value, it'll not then being possible to restart the database once it has B Een shutdown.

If the database you have created uses the wrong character set, you can modify the character set using the following methods:

UPDATE SYS. props$

SET values$ = ' we8iso8859p1 '

WHERE NAME = ' nls_characterset ';

Note: It is important to ensure the accuracy of character set names. If the Nls_characterset is updated to an incorrect value, the database cannot be restarted after it is closed.



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

Quote from the (8.1.6) documentation reveals:

The Oracle8 database can modify the character set by using the ALTER DATABASE command. Referencing a word in a document: The new character set must be a superset of the original character set. Interestingly, I used to update the character set is a violation of this principle, the database is still working; Of course, any character code can cause the same problem, so the safest way is to follow the rules.


"Changing the Character Set after Database creation

In some cases, you could wish to the existing database character set. For instance, you could find this number of languages that need to is supported in your database have. In most cases, you'll 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 are a strict superset of the current character set, it are possible to use th E ALTER database CHARACTER set to expedite the change in the database CHARACTER set.

Sometimes, you may need to modify the character set of the current database. Then you will find the kind of reasons you need to increase the database support. In most cases, you may need to implement a full Export/import import/export. If, just if, the new character set is a strict superset of the current character set, using the Alter DATABASE CHARACTER SET command is a viable method.

The target character set is a strict superset if and only if each and every codepoint into the source character set is avail Able 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 US7ASC II is a strict subset of WE8ISO8859P1, AL24UTFFSS, and UTF8:

The current Character set new Character set new Character the set is strict superset?

Us7ascii WE8ISO8859P1 Yes

Us7ascii ALT24UTFFSS Yes

Us7ascii UTF8 Yes

If each of the codepoint in the a character set has the same valid value in the B character set, then the B character set can become a superset of the a character set. For example, the following migration scenario can use the ALTER DATABASE CHARACTER SET command because Us7ascii is a superset of WE8ISO8859P1, AL24UTFFSS, and UTF8.

Current character set new character set new character set is superset

Us7ascii WE8ISO8859P1 is

Us7ascii ALT24UTFFSS is

Us7ascii UTF8 is

Warning:attempting to alter the database character set to a character set, not a strict superset can result in da TA loss and data corruption. To ensure the data integrity, whenever migrating to a new character set, "not" a strict superset, you must use Export/imp Ort. It is essential to does a full backup of the database before using the ALTER database [national] CHARACTER SET statement, si NCE The command cannot is 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 is specified without quotes, for example:


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


<do a full backup>


sql> ALTER SYSTEM ENABLE restriced session;

sql> ALTER SYSTEM SET job_queue_processes=0;


sql> ALTER DATABASE CHARACTER SET <new_character_set_name>;



To the "national character set", replace the ALTER DATABASE character Set statement with ALTER database national CHAR Acter SET. You can issue both commands together if desired. "

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

Tags Index: