Nls_lang format:
Nls_lang=language_territory. Client CHARACTERSET
1, Nls_lang parameter composition
The Nls_lang parameter consists of the following sections:
Nls_lang=<language>_<territory>.<clients characterset>
The meaning of each part of the Nls_lang is as follows:
Language specify:
Language used by Oracle messages
Month and day display in date
Territory specified
Currency and number formats
Area and the habit of calculating week and date
CHARACTERSET:
Controlling the character set used by client applications
Typically set or equal to client (such as Windows) code page in Oracle Nls_lang settings
or set to UTF8 for Unicode applications
To view the Characherset currently used by Oracle:
SELECT * from v$nls_parameters where parameter= ' nls_characterset ';
View the instance character set language and territory:
SELECT * from Nls_instance_parameters;
Nls_language
Nls_territory
View these two items
2, view Nls_lang settings:
Env|grep Nls_lang
One: Introduce a concept:
The NLS ' National Language Support (NLS) ' From the concept we see that this parameter actually defines the locale in which the database holds the data, When we set a NLS, we actually specify some form of expression for Oracle when storing the data, such as we choose Chinese, how its Chinese characters are stored, what rules are sorted, how the currency is represented, and the date format is set.
Two: How to query our database in the end what is used in the character set
Sql> select * from V$nls_parameters;
Nls_language Simplified Chinese
Nls_territory
Nls_currency RMB
Nls_iso_currency
Nls_numeric_characters.,
Nls_calendar Gregorian
Nls_date_format DD-MON-RR
Nls_date_language Simplified Chinese
Nls_characterset ZHS16GBK
Nls_sort BINARY
Nls_time_format HH.MI. Ssxff AM
Nls_timestamp_format DD-MON-RR HH.MI. Ssxff AM
Nls_time_tz_format HH.MI. Ssxff AMTZR
Nls_timestamp_tz_format DD-MON-RR HH.MI. Ssxff AM TZR
Nls_dual_currency RMB
Nls_nchar_characterset AL16UTF16
Nls_comp BINARY
Nls_length_semantics BYTE
NLS_NCHAR_CONV_EXCP FALSE
Where Nls_language represents the Chinese display mode, Nls_characterset is the character set set, the other date,time, such as Date time format, currency is the currency format.
Three: Existing Problems and solutions
We have two main problems in the use of character set process.
One is that we display the Chinese portion of the query when the chaos character.
This is generally because the server-side character set settings differ from the client's character set settings. As long as the two changes are consistent, you can solve the problem.
In Windows you need to go to the registry in local MACHINE-SOFTWARE-ORACLE-HOME0 to change Nls_lang,unix under. Profile or this. Bash_profile (according to your shell) Change Nls_lang.
The second is that we prompt for character set mismatch when importing data. (Some can be compatible without the tube)
Solution now I see 3 kinds of
1: Exp/imp is a client-side product like Sql*plus, so his character set is set by the Nls_lang of the server. When exporting the backed up export file with exp, the set of character sets is also stored in export file. This is the principle that some people change the export file header when the character cannot be converted.
2:) <=8.1
Execute SQL > CREATE database character set US7ASCII before Imp
* CREATE DATABASE Character Set Us7ascii
ERROR at line 1:
Ora-01031:insufficient Privileges
----You will find this error message in the execution of the statement, no matter what, in fact, the character set of the Oracle database has been forcibly modified to US7ASCII, then the data is loaded with imp command. After data loading is complete, shutdown database, then startup database, log into Oracle database with legitimate users, run SELECT * from v$nls_parameters at sql> command prompt, You can see that the Oracle database character set has been restored, and then when you look at the table with the Kanji character data, the Chinese characters are displayed correctly.
3: Data Pipeline Import data
In this way I used the PB6 data pipeline earlier, connect the data pipeline to the two databases that need to import the export, and the Chinese display is no problem after transmission.
4: Further the data dictionary props$ >8.1
1.) Use Sql*plus connect to database as user SYS
2.) Issue this below command
sql> UPDATE props$ SET value$= ' xxx ' WHERE name= ' nls_characterset ';
3.) Shutdown database and restart it
Finally add a reference to the automatic conversion of the character set (when imp):
Warning can ' t change character set of current database that have number of bits greater than the new one. For example,
I understand it's 7-8 can,8-8ican,8-8cannot,8-7cannot.
Us7ascii-> we8iso8859p1 => Can
Th8tisascii-> Us7ascii => cannot
WE8ISO8859P1-> Th8tisascii => cannot
Th8tisascii-> we8iso8859p1 => Can
Us7ascii-> th8tisascii => Can
