Oracle character set:
The database character set should be a supercoming of the local Operation System. The maximum character set is the same as Imp / Exp. If Server and Client are the same, then the problem is not big.
US7ASCII is a subset of ZHS16GBK, and all character concentrations are the largest because it is based on Unicode characters, double-byte saving characters.
NLS_LANG format:
NLS_LANG = Language_Territory.Charset
Composed of three parts: language geographical character set.
Dict.Base Data Dictionary Basic Table Version
DBTIMEZONE Database Time Zone
NLS_LANGUAGE language
NLS_TERRITORY Region
NLS_CURRENCY local currency characters
NLS_ISO_CURRENCY ISO currency characters
NLS_NUMERIC_CHARACTERS Digital Character and Group Division
NLS_CharacTerset character set
NLS_CALENDAR calendar system
NLS_DATE_FORMAT Default Date Format
NLS_DATE_LANGUAGE Default Date Language
NLS_SORT Character Sorting Sequence
NLS_TIME_FORMAT Time Format
NLS_TimeStamp_Format Timestamp format
NLS_LANG's value should be NLS_LANGUAGE _NLS_TERRITORY.NLS_CHARACSET
For example NLS_LANGUAGE = american
NLS_TERRITORY = America
NLS_CHARACSET = zhs16GBK
The character set of the database is specified at CREATE DATABASE, which will not be modified in the future, but in a CREATE DATABASE, we can modify Sys.props $, mainly the corresponding client's display, unrelated to the storage.
The character set is Oracle to accommodate different language text display. The character set used for Chinese characters mainly has zHS16CGB231280, US7ASCII, WE8ISO8859P1, etc.
The server-side character set is specified at the installation, the character set is stored in the Oracle Data Dictionary V $ NLS_Parameters, the client character set registration is divided into two cases:
1) SQL * NET 2.0 The following version, the character set is registered in the Oracle.ini file in the Windows system directory;
2) SQL * NET 2.0 or more (ie 32) versions, the character set is registered in the system registry of Windows.
To properly display the Oracle Database Chinese character information correctly, you must first match the character set of the server side to the client's character set; followed by the data character set loaded to the Oracle database, must be consistent with the server specified character set. So the cause of garbled is roughly:
The ü server specifies that the character set is different from the customer character set, and the load data character set is consistent.
Solution:
Suppose the current server side is a US7ASCII character set;
Step 1: View server-side character sets
Log in to Oracle via SQL * Plus.
SQL> SELECT * FROM V $ NLS_PARAMETERS
NLS_CharacterSet US7ASCII
Step 2: Configure the client according to the server.
When installing Oracle client software, specify that when you install Oracle's client product software, select the character set that is consistent with the Oracle server (this example is US7ASCII).
Ø Modify the registration information The version of the SQL * NET selected according to the Oracle client is divided into the following two situations:
a. Client is SQL * NET 2.0 below version
Enter the system directory of Windows, edit the Oracle.ini file, replace the original character set with US7ASCII, restart your computer, set into effect. b. Client is SQL * NET 2.0 or higher
Row regedit under Win98, first step in HKEY_LOCAL_MACHINE, the second step Select Software, step in the third step Select Oracle, select NLS_LANG, type the character set with the server side (this example is: American_american.usususus 7ASCII).
ü Server The specified character set is the same as the customer character set, which is inconsistent with the load data character set.
Such problems typically occur in Oracle Version upgrade or reinstall the system, select the character set with the original server, while the restored backup data is still unloaded by the original character set, and loads from other uses different character sets. The data of the Oracle database is detached. In both cases, Chinese characters cannot be displayed regardless of whether the server side and client character sets are consistent.
Solution:
(1) The server is reinstalled Oracle
Select the character set that is consistent with the original unloading data when reinstalling Oracle (this example is US7ASCII).
Load the original unloaded data.
This situation is only used in empty spaces and data with the same character set.
(2) Forced to modify the server-side Oracle Current Character Set
Before loading the data with the Imp command, first log in to the system DBA user with SQL * Plus, perform the following SQL statement for the current Oracle database character set modification:
SQL> Create Database Character Set US7ASCII * Create Database Character Set US7ASCII Error At Line 1: ORA-01031: Insufficient Privileges
You will find the above error message during the execution of the statement, and this time does not use it. In fact, the character set of the Oracle database has been forced to modify to US7ASCII, and then load data with the IMP command. After the data load is complete, the shutdown database, then the Startup database, use the legal user to log in to the Oracle database, under the SQL> command prompt, run SELECT * from V $ nls_parameters, you can see that the Oracle database character set has been restored, then view again When there is a table with Chinese character data, Chinese characters can be displayed correctly.
(3) Use data format dump to avoid character set limitations
This approach is mainly used to load different character set data for foreign Oracle databases. The method is as follows:
First load the data on the server with the same character set, then use the conversion tool to foxBase format or Access format database, and then use the conversion tool to the Oracle database of different character sets, so that the Oracle character set is avoided. Harass. There are many tools for current database format conversion, and the data import / export feature provided by the PIPELINE, the Microsoft Access database provided by Power Builder5.0, and the Microsoft Access database. See the relevant information instructions for the conversion method. .
ü Server specifies that the character set is different from the customer character set, which is inconsistent with the input data character set.
This is the case where the client is entered from the client when the client is inconsistent with the server-side character set. This information is entered even if the client character set is changed correctly, and the Chinese characters cannot be displayed.
There is no good way to match the client with the server-side character set, and replace the Chinese character part according to the characterization of the original input Chinese characters.
Changing character sets c: /> svrmgrl performed in svrmgrl in: shutdownstartup mountALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0; ALTER SYSTEM SET AQ_TM_PROCESSES = 0; ALTER DATABASE OPEN; update props $ set value = 'ZHS16GB' WHERE NAME = 'Nls_characterset'; Update Props $ set value = 'zHS16GB' where name = 'nls_nchar_characterset'; first stated that this law is very dangerous, if it is changed, the value may cause the database to not open. ×××××××××××××× modified steps: c: /> SVRMGRL executing in SVRMGRL: Connect InterNal / Oracle [@dbname]; shutdown immediate; startup restrict; alter databae [dbname] Character SET [. SIMPLIFIED CHINESE_CHINA] ZHS16GBK; ALTER dATABAE [dbname] NATIONAL CHARACTER SET [SIMPLIFIED CHINESE_CHINA.] ZHS16GBK; alter database [dbname] open; - shutdown immediate; - startup; ----------- -
Oracle Manager Server:
Default username: SYSMAN
Userpassword: OEM_TEMP
SQLPlus:
System / Manager @ database
Conn System / Manager @ Mecon As Sysbda
Check the character set:
SQL> CONN INTERNAL system / manager @ Mecon as sysdba
SQL> SELECT Userenv ('Language') from Dual;
