Export / Importing and Character Sets to understand Oracle multi-language settings, Oracle multi-language settings are to support world-wide language and character sets, generally for language tips, monetary forms, sorting methods, and data for char, varchar2, clob, long fields. / Display is effective. Oracle Multi-language Settings The main two features are national language settings and character set settings. National language settings determine the language type of interface or prompt; the character set determines the database saving and character set about data (such as text) coding rules. As a small example above, the environment variable NLS_LANG is different, resulting in a change in EXP help, which is the role of multi-language settings (NLS_LANG contains national language settings and character set settings, which works here is national language settings, not character sets ). Oracle character set setting, divided into database character sets and client character set environment settings. In the database end, the character set is set when the database is created, and saved in the database PROPS Table, for 8i products, you can use the "ALTER DATABASE Character Set" to modify the character set of the database, but it is only From subset to supercharge. Don't modify the character set through the Update Props $, if you don't support conversion, you may lose all the data related to the character set, which is supported, or it may cause the database's abnormal operation. The character set is divided into single-byte character set and multi-byte character set, US7ASCII is a typical single-byte character set, in this character set Length = Lengthb, and zHS16GBK is a common double-word character set, here Lengthb = 2 * Length. The client's character set environment is relatively simple, mainly environment variables or registry entries NLS_LANG, note NLS_LANG's priority: Parameter file à Rub-registry à environment variable àalter sessionà function. The group of NLS_LANG is "national language settings. Character set", such as NLS_LANG = Simplified Chinese_China.zHS16GBK. The client's character set is preferably the same as the database end (country language settings can be different, such as the zhs16GBK character set, the client can be NLS_LANG = Simplified Chinese_China.zhs16GBK or AMEIRCAN_AMERICA.ZHS16GBK, do not affect the normal display of the database character), if The character set is different, and the conversion of the character set is not compatible, then the client's data storage / display and export / imported data related to the character set will be garbled. With a little bit of skill, you can convert data on the database of different character sets. Here you need a 2 credit editing tool, such as uedit32.
Open the exported DMP file with edit mode, get 2, 3-byte content, such as 00 01, first convert it to 10-carry number, 1, use the function NLS_CHARSET_NAME to get the character set: SQL> SELECT NLS_CHARSET_NAME 1) from Dual; NLS_CHARSET_NAME (1) ----------------- US7ASCII can know the DMP file character set is US7ASCII, if you need to turn the DMP file Enabled to get the number of the character set with nls_charset_id: SQL> SELECT NLS_CHARSET_ID ('zhs16GBK') from dual; nls_charset_id ('zhs16gbk') -------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- 852 Convert 852 into a 16-based number, 354, convert the 2,3-byte 00 01 to 03 54, complete the conversion of the DMP file character set from US7ASCII to ZHS16GBK In this way, it is possible to import the DMP file into the zhs16GBK character set. (Note that the conversion between the decimal and hexadecimal, I want to understand the truth) How to view the database character set [A] Database server character set Select * from nls_database_parameters, from props $, is the character set of the database . Client character set environment Select * from nls_instance_parameter, comes from V $ Parameter, indicating the settings of the client's character set, may be the parameter file, environment variable, or registry session character set environment Select * from NLS_Session_Parameter, come from V $ NLS_Parameters, indicating that the session is your own setting, which may be the environment variable of the session or the ALTER Session completion. If the session is not special setting, it will be consistent with NLS_INSTANCE_PARAMETER. The client's character set requires the same as the server to correctly display the non-ASCII characters of the database. If multiple settings exist, the Alter Session> Environment Variable> Registry> Parameter file character set is consistent, but the language settings can be different, and the language settings are recommended in English. If the character set is ZHS16GBK, the nls_lang can be American_america.zHS16GBK. 1. Query the character set configured by the server side. Perform DBA users in SQLPLUS: select tab1.aa || '_' || Tab2.bb || '.' || Tab3.cc from (SELECT VALUE $ AA from sys.props $ Where name = 'nls_language') Tab1, (Select Value $ BB from Sys.props $ Where Name = 'NLS_ISO_CURRENCY') Tab2, (Select Value $ CC from Sys.props $ Where Name = 'NLS_CHARACTERSET') TAB3;
2. In the client's registry HKEY_LOCAL_MACHINE-> Software-> Oracle-> Home0, if there is no system variable NLS_LANG, "New" -> "String", rename "NLS_LANG", and assign the value of the above query variable Value (note that characters in quotation should be capitalized), and no need to restart Oracle when viewed. 1. Check the character set of the Oracle database on the server
SQL> conn sys / change_on_install connection is successful.
SQL> DESC PROPS $ 列 Name Free Type ----------------------------------- - ---- Name Not Null Varchar2 (30) Value $ VARCHAR2 (2000) Comment $ VARCHAR2 (2000)
SQL> Col Value $ FORMAT A40SQL> SELECT NAME, VALUE $ from Props $
Name Value $ ----------------------------------------------------------------------- -------- DICT.BASE 2NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $ NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS., NLS_DATE_FORMAT DD-MON-YYNLS_DATE_LANGUAGE AMERICANNLS_CHARACTERSET ZHS16GBKNLS_SORT BINARYNLS_CALENDAR GREGORIANNLS_RDBMS_VERSION 7.3.4.0.0GLOBAL_DB_NAME ORACLE.WORLDEXPORT_VIEWS_VERSION 3
NLS_CHARACTERSET and NLS_CHAR_CTERSET This parameter should be zhs16GBK, if not, change to it.
SQL * Plus modification method: SQL> Update ProPs $ set value $ = 'new character set' where name = 'nls_characterset';
solaris8, oracle8.1.7 environment: ORACLE_SID = ora8i ORACLE_BASE = $ HOME ORACLE_HOME = $ ORACLE_BASE / products / 8.1.7 NLS_LANG = "SIMPLIFIED CHINESE_CHINA.ZHS16GBK" LD_LIBRARY_PATH = / usr / lib: / usr / dt / lib: / usr / openwin / lib: $ ORACLE_HOME / lib PATH = $ PATH: / usr / dt / bin: / usr / openwin / bin: $ ORACLE_HOME / content LD_LIBRARY_PATH PATH v $ nls_parameters the bin export ORACLE_BASE ORACLE_HOME ORACLE_SID NLS_LANG: 'PARAMETER', 'VALUE' 'NLS_LANGUAGE', 'SIMPLIFIED CHINESE' 'NLS_TERRITORY', 'CHINA' 'NLS_CURRENCY', 'RMB' 'NLS_ISO_CURRENCY', 'CHINA' 'NLS_NUMERIC_CHARACTERS', '.,' 'NLS_CALENDAR', 'GREGORIAN' 'NLS_DATE_FORMAT', 'DD -MON-RR '' NLS_DATE_LANGUAGE ',' SIMPLIFIED CHINESE '' NLS_CHARACTERSET ',' US7ASCII '' 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 ',' RMB '' NLS_NCHAR_CHARACTERSET ',' US7ASCII '' NLS_COMP ',' binary 'character set is U S7ASCII, how to set it to zHS16GBK (Whether to write this)? Please help me, thank you. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- C: /> SVRMGRL executes in SVRMGRL: Shutdown Startup Mount ALTER System Enable Restricted Session; ALTER SYSTEM SET JOB_QUE_PROCESS = 0; Alter System Set AQ_TM_PROCESS = 0; ALTER DATABASE OPEN; UPDATE PROPS $ SET VALUE $ = '
ZHS16GB 'Where name =' nls_characterset '; Update ProPs $ set value $ =' zHS16GB 'where name =' nls_nchar_characterset '; first stipulating this law is very dangerous, if the value 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; how to view the database character set [A] database server The character set Select * from NLS_DATABASE_PARAMETERS, which is derived from PROPS $, which is a character set that represents the database. Client character set environment Select * from nls_instance_parameter, comes from V $ Parameter, indicating the settings of the client's character set, may be the parameter file, environment variable, or registry session character set environment Select * from NLS_Session_Parameter, come from V $ NLS_Parameters, indicating that the session is your own setting, which may be the environment variable of the session or the ALTER Session completion. If the session is not special setting, it will be consistent with NLS_INSTANCE_PARAMETER. The client's character set requires the same as the server to correctly display the non-ASCII characters of the database. If multiple settings exist, the Alter Session> Environment Variable> Registry> Parameter file character set is consistent, but the language settings can be different, and the language settings are recommended in English. If the character set is ZHS16GBK, the nls_lang can be American_america.zHS16GBK.
