"Insert in SQL * Plus is Chinese, why is it to save it after entering the server, then SELECT is?"
"Sometimes, server data is exported, reinstall the server, then import data, result, data query?"
...
These issues, general, because the character set setting is not compained.
For a long time, the character set has always been a problem that many Oracle enthusiasts have been invested in Oracle database management and applications. It has been receiving the "urgency" and "rescue" and "rescue" and "rescue" similar to those mentioned above. Today, I plan to do some analysis and discussions on this issue.
First, we must clear what is a character set? The character set is a symbol collection of one byte data. There is a size of the size. There are mutual including relationships. If US7ASCII is the subset of zhs16GBK, from US7ASCII to zhs16GBK without data interpretation, there will be no data loss Oracle also requires support from subset to supercharge, which is not possible. UTF8 should be the largest in all character sets because it is based on Unicode, double byte saves characters (and therefore takes more on storage space).
Second, once the database is created, the character set of the database cannot be changed. Therefore, it is important to consider which character set to use at the beginning of design and installation. The database character set should be a supercoming of the operating system local character set. The character set used by the client to access the database will decide which supercharp in choosing, that is, the database character set should be superchard for all customer character sets.
In practical applications, the most relevant to the character set problem is exp / IMP. In doing EXP / IMP, if Client and Server NLS_LANG settings are the same, there is no problem. However, there will often be such problems between the two different character set systems, such as, when exporting, the database display is normal, is Chinese, when importing into other systems, it is garbled, this is also A type of common problems. For this problem, there is a commonly used conversion method. First, use a binary editor (such as UltraEdit) to see the second and third bytes of the file (DMP file), the content of these two bytes is the character of the server. Set, such as 0001, then find out the character set representative in the database:
BEPONE> SQLPLUS INTERNAL
SQL> SELECT NLS_CHARSET_NAME (TO_NUMBER ('0001', 'XXXX')) from Dual;
NLS_CHARSET_NAME (to_number ('0001', 'XXXX')
----------------------------------------
US7ASCII
SQL>
Then, if you need to modify to zHS16GBK when importing data, we need to know how to modify these two bytes to allow them to correspond to zHS16GBK:
BEPONE> SQLPLUS INTERNAL
SQL> SQL> SELECT TO_CHAR (NLS_CHARSET_ID ('zhs16GBK'), 'xxxx') from dual;
TO_CH
-----
354
SQL>
Therefore, the two bytes can be modified to 0354 (when less than 4 digits, it will be added to the data, and then the data can be imported.
Now, in order to further engage in the secret of the character set, some NLS_LANG parameters related to the character set,
NLS_LANG format:
NLS_LANG = Language_Territory.Charset has three components (language, geographical and character sets), each constituent component controls the characteristics of NLS subset. Where: Language Specifies the language of the server message.
Territory specifies the date and number format of the server.
Charset specifies the character set
E.g:
American_america.us7scii
American_
America
ZHS16GBK
There are also some subsets that can be more clearly defined NLS_LANG parameters:
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 Component Spaced
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
. . . . . .
Through the PROPS $ dynamic performance view, we can view the character set information of the database:
$> SQLPlus Internal
SQL> DESC PROPS $
Name Type Nullable Default Comments
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Name varchar2 (30)
Value $ VARCHAR2 (4000) Y
Comment $ varchar2 (4000) Y
SQL> SET Arraysize 1
SQL> COL Value $ FORMAT A40
SQL> SELECT NAME, VALUE $ from Props $ Where name = 'nls_characterset';
Name Value $
------------------------------------------------------------------------------------------------------------------------------------------------------------------ --------------------
NLS_CharacterSet ZHS16GBK
SQL> SELECT * from sys.props $;
Name Value $
------------------------------------------------- ---------
Dict.base 2
DBTIMEZONE 0:00
NLS_LANGUAGE AMERICAN
NLS_TERRITORY
America
NLS_CURRENCY $
NLS_ISO_CURRENCY
America
NLS_NUMERIC_CHARACTERS.,
NLS_CharacterSet ZHS16GBK
NLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
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 AM TZH: TZM
NLS_TimeSTAMP_TZ_FORMAT DD-MON-RR HH.MI.ssxff AM TZH: TZM
NLS_DUAL_CURRENCY $
NLS_Comp Binary
NLS_NCHAR_CHARACTERSET ENS16GBK
NLS_RDBMS_VERSION 8.1.6.0.0
Name Value $
------------------------------------------------- ---------
Global_db_name scpdb1
EXPORT_VIEWS_VERSION 8
22 rows selected
SQL>
As can be seen from the pass:
NLS_LANG = American _
America
ZHS16GBK
Although the database's character set is specified when Create Database is specified, it is not allowed to change, but on a well-established database, we can modify the display of sys.props $ mainly corresponding to the client's display, unrelated to the storage .
Such as:
SQL> conn / as sysdba
Connected.
SQL> SQL> Select * from sys.props $
2 where name = 'nls_language';
Name Value $
------------------------------------------------------------------------------------------------------------------------------------------------------------------ -
NLS_LANGUAGE AMERICAN
SQL>
SQL> Update sys.props $ set value $ = 'simplified chinese'
2 where name = 'nls_language';
1 Row Updated
SQL>
SQL> SELECT * from sys.props $
2 where name = 'nls_language';
Name Value $
------------------------------------------------------------------------------------------------------------------------------------------------------------------ -
NLS_LANGUAGE SIMPLIFIED CHINESE
SQL>
Usually we are all common to the cause, it can basically be divided into three:
1. The server specifies that the character set is different from the customer character set and the load data character set is consistent.
Workaround: For this situation, you only need to set the client character set and the server-side character set can be consistent, the specific operation is as follows:
§ See the current character set:
SQL> SELECT * from sys.props $
2 where name = 'nls_characterset'; Name Value $
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_CharacterSet ZHS16GBK
SQL>
It can be seen that the character set of the server Oracle database is now 'zhs16GBK'
§ Specify when the client is sitting on the corresponding configuration or installing Oracle client software:
If you haven't installed the client yet, you specify the character set with the server when you install the client; if you have already installed the client, and the client is SQL * NET 2.0 or the following version, enter Windows system directory Edit the Oracle.ini file, replace the original character set with US7ASCII, restart the computer, set effective; Select Software, third step to select Oracle, step in step, select NLS_LANG, type the same character set as the server
(This example is: HKEY_LOCAL_MACHINE / SOFTWARE / ORACLE / NLS_LANG: American _ America. Zhs16GBK).
If it is a UNIX client, then:
SQL> conn / as sysdba
Connected.
SQL> SQL> Update sys.props $ set value $ = 'simplified chinese'
2 where name = 'nls_language';
1 Row Updated
SQL> commit;
Commit completion
SQL>
2. The server specifies that the character set is the same as the customer character set, which is inconsistent with the load data character set.
Workaround: Forced loading data character sets consistent with server-side character sets. To do this, you can recreate the database and select the character set consistent with the original unloading data, then IMP data, this situation is only applicable to the empty space and data with the same character set.
Solve such problems, you can also load data on the server with the same character set, then use the conversion tool to remove the FoxBase format or the Access format database, and then use the conversion tool to the Oracle database of different character sets, so Avoid the trouble of Oracle character set. 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.
3. The server specifies that the character set is different from the customer character set, which is inconsistent with the input data character set.
In this case, there is no good solution to this so far.
Through the above understanding, we know that in the later use of the database, it will have a variety of characters, mostly due to the design of the database, the installation points out that there is no good consideration, so we can completely pass and The client uses the same character set to avoid the troubles that have such problems.
