Viewing and modifying the Oracle server and client character sets

Article Source: http://www.cnblogs.com/chenyan296076785/p/3533065.html

I. What is the Oracle character set

The Oracle character set is a collection of symbols that interpret a byte of data, have a size, and have a mutual containment relationship.

ORACLE's support for national language architectures allows you to store, process, and retrieve data using localized languages. It makes database Tools, error messages, sort order, date, when

, currency, numbers, and calendars automatically adapt 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, geography, and character set), each of which controls the characteristics of the NLS subset.

which
Language Specifies the language of the server message, 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 can import and export data to each other as long as the third part, the only thing that affects the message is the Chinese or English.

Second, view the database character set

This involves three aspects of the character set,
One is the Oracel server-side character set;
The second is the character set of the Oracle client side;
The third is the character set of the DMP file.

When doing data import, these three character sets are required to be imported in a correct way.
1. Querying the character set of the Oracle server side
There are many ways to identify the Oracle server-side character set, and the more intuitive query method is the following:
Sql>select userenv (' language ') from dual;
The results are similar to the following: AMERICAN _ AMERICA. Zhs16gbk

2. How to query the DMP file character set
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 binary), look at the 2nd 3rd byte of content, such as 0354, and then use the following SQL to isolate 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 also the most common case), with a text editor opened very slowly or completely open, you can use the following command (on the UNIX host):
Cat Exp.dmp |od-x|head-1|awk ' {print $ |cut-c} ' 3-6

The corresponding character set can then be obtained using the SQL above.

3. Querying the character set of Oracle client side
This is relatively simple.
Under the Windows platform, it is the Nls_lang of the corresponding oraclehome in the registry. You can also set it in the DOS window itself,

For example: Set Nls_lang=american_america. ZHS16GBK
This affects only the environment variables in this window.
under the UNIX platform, 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, you should uniformly modify the same character set as the server side.  

Supplement:

(1). Database server Character Set
Select * from nls_database_parameters 
originates from props$, which is the character set representing the database.

(2). The client Character Set environment
SELECT * from Nls_instance_parameters
is derived from V$parameter, which represents the setting of the client's character set, which may be a parameter file, an environment variable, or a registry

(3). The session Character Set environment
SELECT * from Nls_session_parameters
is derived from V$nls_parameters, which represents the session's own settings, which may be the session's environment variable or alter Session is complete, and if there are no special settings for the conversation,

will be consistent with nls_instance_parameters.

(4). The client's character set requires consistency with the server to correctly display non-ASCII characters for the database. If multiple settings exist, ALTER session> environment variable > registry > Parameter File
Character set requirements are consistent, but language settings can be different, language settings are recommended in English. If the character set is ZHS16GBK, then Nls_lang can be AMERICAN_AMERICA.ZHS16GBK.

Third, modify the character set of Oracle

As mentioned above, Oracle's character set has an inclusive relationship with each other. such as Us7ascii is a subset of ZHS16GBK, from Us7ascii to ZHS16GBK there is no data interpretation of the problem, there will be no data loss. UTF8 should be the largest in all character sets because it is Unicode-based, double-byte-saving characters (and therefore more storage space).

Once the database is created, the character set of the database is theoretically immutable. Therefore, it is important to consider which character set to use at the beginning of design and installation. According to Oracle's official instructions, the conversion of character sets is supported from subsets to superset, not vice versa. If there are no subsets and superset relationships between the two character sets, then the conversion of the character set is not supported by Oracle. For database server, the incorrect modification of the character set will result in a lot of unpredictable consequences that can seriously affect the normal functioning of the database, so be sure to verify that there are subsets and superset relationships between the two character sets before you modify them. In general, we do not recommend modifying the character set of the Oracle database server side unless it is a last resort. In particular, there is no subset and superset relationship between the two character sets ZHS16GBK and zhs16cgb231280 that we use most often, so it is theoretically not supported to convert between the two character sets.

1. Modify the server-side character set (not recommended)
Prior to Oracle 8, you could change the character set of a database by directly modifying the data dictionary table props$.

But after Oracle8, at least three system tables recorded the database character set information, only the props$ table is not complete, can cause serious consequences. The correct method of modification is as follows:

$sqlplus/nolog

Sql>conn/as Sysdba; If the database server is now started, execute the shutdown immediate command to shut down the database server, and then execute the following command:

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;
Sql>alter DATABASE CHARACTER SET ZHS16GBK;
Sql>alter DATABASE National CHARACTER SET ZHS16GBK;
Sql>shutdown IMMEDIATE;
Sql>startup

Note: If there is no large object, there is no effect on language conversion during use (remember that the set must be Oracle supported, or start) as above, but may appear ' Ora-12717:cannot ALTER DATABASE National CHARACTER SET when NCLOB data exists ' such information.
There are two ways to solve this problem

One is to use the Internal_use keyword to modify the regional settings,

There is also the use of 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_use UTF8;
Sql>shutdown immediate;
sql>startup;

If you do this, the national charset locale is fine.

2. Modifying the DMP file character set
As mentioned above, the 2nd 3rd byte of the DMP file records the character set information, so the content of the 2nd 3rd byte of the DMP file can be ' deceived ' by the Oracle check.

In theory, it is only possible to modify from subset to superset, but in many cases it can be modified without subsets and superset relationships, and some of the character sets we use are

such as US7ASCII,WE8ISO8859P1,ZHS16CGB231280,ZHS16GBK basic can change. Because the change is only the DMP file, so the impact is not small.
The specific modification method is more, the simplest is to modify the DMP file's 2nd and 3rd bytes directly with UltraEdit.

For example, to change the character set of the DMP file to ZHS16GBK, you can use the following SQL to isolate the 16 code corresponding to that character set:

Sql> Select To_char (nls_charset_id (' ZHS16GBK '), ' XXXX ') from dual;

0354

Then change the DMP file 2, 3 bytes to 0354.

If the DMP file is large, you cannot open it with your UE, you need to use the method of the program.

Viewing and modifying the Oracle server and client character sets