View and modify Oracle character set ____oracle

One, what is the Oracle character Set

An Oracle character set is a collection of symbols that are interpreted as a byte of data, having a size and a mutual containment relationship. ORACLE's support for the national language architecture allows you to use localized languages to store, process, and retrieve data. It makes database tools, error messages, sort orders, dates, times, currencies, numbers, and calendars automatically adapted to localized languages and platforms.

The most important parameter that affects the Oracle database character set is the Nls_lang parameter.

Its format is as follows: Nls_lang = Language_territory.charset

It has three components (language, region, and character set), and each component controls the characteristics of the NLS subset.

which

Language: Specifies the language of the server message, which indicates whether the message is Chinese or English

Territory: Specifies the date and number format of the server.

Charset: Specifies the character set.

such as: American _ AMERICA. Zhs16gbk

From the composition of Nls_lang we can see that the real impact of the database character set is actually the third part.

So the character set between the two databases as long as the third part of the same can be imported to export data, before the impact of only the hint information is Chinese or English.

Second, view the database character set

involves three aspects of the character set,

1. Oracel Server -side character set;

2. Oracle client-side character set;

3. DMP the character set of the file.

When you do the data import, you need all three character sets to be consistent to import correctly.

2.1 Querying Oracle Server-side character sets

There are a number of ways to detect the character set on the Oracle server side, and the more intuitive query method is the following:

Sql> Select Userenv (' language ') from dual;

USERENV (' LANGUAGE ')

----------------------------------------------------

Simplified Chinese_china. Zhs16gbk

Sql>select userenv (' language ') from dual;

American _ AMERICA. Zhs16gbk

2.2 How to query the character set of a DMP file

The DMP file exported with the Oracle Exp tool also contains character set information, and the 2nd and 3rd bytes of the DMP file record the character set of the DMP file. If the DMP file is small, such as only a few m or dozens of m, you can open it with UltraEdit (16), Look at 2nd 3rd byte, such as 0354, and then use the following SQL to identify its corresponding character set:

Sql> Select Nls_charset_name (To_number (' 0354 ', ' xxxx ')) from dual;

Zhs16gbk

If the DMP file is large, such as more than 2G (which is the most common case), it is slow to open with a text editor, and can be opened completely, using the following command (on a UNIX host):

Cat Exp.dmp |od-x|head-1|awk ' {print $ $} ' |cut-c 3-6

You can then use the above SQL to get its corresponding character set.

2.3 Querying Oracle Client-side character sets

Under the Windows platform, is the corresponding oraclehome in the registry Nls_lang. You can also set your own in the DOS window,

For example: Set Nls_lang=american_america. Zhs16gbk

This only affects the environment variables within the window.

Under the UNIX platform, it is the environment variable Nls_lang.

$echo $NLS _lang

American_america. Zhs16gbk

If the result of the check finds that the server side is inconsistent with the client-side character set, unify the modifications to the same set of characters as the server side.

Add:

(1). Database server Character Set

SELECT * FROM Nls_database_parameters

From Props$, is the character set that represents the database.

(2). Client Character Set Environment

SELECT * FROM Nls_instance_parameters

It comes from V$parameter, which indicates the setting of the client's character set, possibly a parameter file, an environment variable, or a registry

(3). Session Character Set Environment

SELECT * FROM Nls_session_parameters

From V$nls_parameters, which represents the session's own settings, may be the environment variable for the session or the ALTER session is complete, and will be consistent with nls_instance_parameters if there are no special settings.

(4). The client's character set requires consistency with the server in order to correctly display non-ASCII characters of the database.

NLS Priority level If multiple settings exist: SQL function > Alter SESSION > Environment variable or Registry > parameter file > database default parameters

Character sets require consistency, but language settings can be different, and language settings are recommended in English. If the character set is ZHS16GBK, then Nls_lang can be AMERICAN_AMERICA.ZHS16GBK.

Iii. modifying the character set of Oracle

As stated above, the database character set cannot be changed in principle after it is created. Therefore, it is important to consider which character set to use at the beginning of design and installation. In the case of database server, the wrong modification of the character set will result in many unpredictable consequences and may seriously affect the normal operation of the database, so make sure to confirm that the two character sets have a subset and a superset before modifying them. In general, we do not recommend modifying the character set on the server side of the Oracle database unless last resort. Specifically, there is no subset and superset relationship between the two most commonly used character sets ZHS16GBK and zhs16cgb231280, so the mutual conversion between the two character sets is theoretically not supported.

However, there are 2 ways to modify the character set.

1. It is often necessary to export the database data, rebuild the database, and then import the database data in the form of conversion.

2. Modifying the character set through the ALTER DATABASE CHARACTER SET statement, but modifying the character set after the database is created is limited and the database character set can be modified only if the new character set is a superset of the current character set, for example, UTF8 is a us7ascii superset. Modify the database character set to use ALTER DB CHARACTER set UTF8.

3.1 Modifying the server-side character set (not recommended)

1. Close the database

Sql>shutdown IMMEDIATE

2. Boot to mount

Sql>startup MOUNT;

Sql>alter SYSTEM ENABLE restricted session;

Sql>alter SYSTEM SET job_queue_processes=0;

Sql>alter SYSTEM SET aq_tm_processes=0;

Sql>alter DATABASE OPEN;

--This can be from the parent set to the subset

Sql>alter DATABASE CHARACTER SET ZHS16GBK;

Sql>alter DATABASE National CHARACTER SET al16utf16;

--if it's from subset to parent set, you need to use the Internal_use parameter, skip the super subset detection

Sql>alter DATABASE CHARACTER SET internal_use Al32utf8;

Sql>alter DATABASE National CHARACTER SET internal_use al16utf16;

Sql>shutdown IMMEDIATE;

Sql>startup

Note: If you do not have a large object, there is no effect in the use of Language conversion, (remember that the set must be Oracle support, or you cannot start), just follow the above procedure.

In the event of a hint such as ' Ora-12717:cannot ALTER DATABASE national CHARACTER SET when NCLOB data exists ',

There are two ways to solve this problem

1. Use the Internal_use keyword to modify the locale,

2. Using re-create, but re-create a bit complicated, so please use Internal_use

Sql>shutdown IMMEDIATE;

Sql>startup MOUNT EXCLUSIVE;

Sql>alter SYSTEM ENABLE restricted session;

Sql>alter SYSTEM SET job_queue_processes=0;

Sql>alter SYSTEM SET aq_tm_processes=0;

Sql>alter DATABASE OPEN;

Sql>alter DATABASE National CHARACTER SET internal_useUTF8;

Sql>shutdown immediate;

sql>startup;

If you follow the above procedure, national CharSet has no problem with regional settings

3.2 Modifying the DMP file character set

As stated above, the 2nd 3rd byte of the DMP file records the character set information, so that directly modifying the contents of the 2nd 3rd byte of the dmp file can ' cheat ' the Oracle's check. This is theoretically only from the subset to the superset can be modified, but in many cases without subsets and superset of the case can also be modified, we often use some character sets, such as US7ASCII,WE8ISO8859P1,ZHS16CGB231280,ZHS16GBK basic can be changed. Because the change is only DMP file, so the impact is not big.

The specific modification method is more, the simplest is to modify the 2nd and 3rd bytes of the DMP file directly with UltraEdit.

For example, to change the character set of the DMP file to ZHS16GBK, you can use the following SQL to find out the 16 code corresponding to the character set: Sql> select To_char (nls_charset_id (' ZHS16GBK '), ' xxxx ') from Dual

0354

Then modify the DMP file's 2, 3 bytes to 0354.

If the DMP file is large and cannot be opened with a UE, it needs to be programmed.

3.3 Client Character Set setting method
1) UNIX Environment
$NLS _lang= "Simplified Chinese" _china.zhs16gbk
$export Nls_lang
Edit profile files for Oracle users
2) Windows environment
Edit the Registration form
Regedit.exe---"HKEY_LOCAL_MACHINE---" oracle--"SOFTWARE---"

Or in the window settings:

Set Nls_lang=american_america. Zhs16gbk

Sorting from Network

========================================================

Oracle expert QQ Group: 60632593 (Full), 60618621, 23145225

Blog: blog.csdn.net/newhappy2008

========================================================