Shulou(Shulou.com)06/01 Report--

1. Query the character set of oracle server

SQL > select userenv ('language') from dual USERENV ('LANGUAGE')-AMERICAN_AMERICA.ZHS16GBKSQL > or SQL > set line 150SQL > col parameter for a30SQL > col value for a30SQL > select * from nls_database_parametersPARAMETER VALUE -NLS_RDBMS_VERSION 12.1.0.2.0NLS_NCHAR_CONV_EXCP FALSENLS_LENGTH_SEMANTICS BYTENLS_COMP BINARYNLS_DUAL_CURRENCY $NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH. MI.SSXFF AM TZRNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZRNLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AMNLS_TIME_FORMAT HH.MI.SSXFF AMNLS_SORT BINARYNLS_DATE_LANGUAGE AMERICANPARAMETER VALUE- -NLS_DATE_FORMAT DD-MON-RRNLS_CALENDAR GREGORIANNLS_NUMERIC_CHARACTERS. NLS_NCHAR_CHARACTERSET AL16UTF16NLS_CHARACTERSET ZHS16GBKNLS_ISO_CURRENCY AMERICANLS_CURRENCY $NLS_TERRITORY AMERICANLS_LANGUAGE AMERICAN20 rows selected.SQL >

Derived from props$, is the character set that represents the database.

2. How to query the character set of dmp files

The dmp file exported with oracle's exp tool also contains the character set information, and the second and third bytes of the dmp file record the character set of the dmp file. If the dmp file is small, for example, only a few megabytes or dozens of megabytes, you can open it with UltraEdit (hexadecimal), look at the contents of the second and third bytes, such as 0354, and then use the following SQL to find its corresponding character set:

SQL > select nls_charset_name (to_number ('0354)) from dual;NLS_CHARSET_NAME (TO_NUMBER (' 0354) ZHS16GBKSQL >

If the dmp file is large, such as more than 2G (which is also the most common case), and it is slow or impossible to open it with a text editor, you can use the following command (on the unix host):

Cat exp.dmp | od-x | head-1 | awk'{print $2 $3}'| cut-c 3-6

Then you can also get its corresponding character set using the above SQL statement.

Note: you need to set the character set when using exp/imp to import and export, because it is a client-side tool. The generated dmp file is based on the character set of the client (if you haven't set it), so that if the character set of the source client and destination database is inconsistent, the imp import will be garbled; while the expdp/impdp tool does not rely on NLS_LANG, it is based on the source and destination NLS_CHARACTERSET.

3. Query the character set of oracle client

Under the windows platform, it is the NLS_LANG of the corresponding OracleHome in the registry. You can also set it yourself in the dos window, such as:

Set nls_lang=AMERICAN_AMERICA.ZHS16GBK

This only affects the environment variables in this window.

Under the unix platform, it is the environment variable NLS_LANG.

$echo $NLS_LANG AMERICAN_AMERICA.ZHS16GBK

If the result of the check finds that the character set on the server side is not consistent with the character set on the client side, please modify it to the same character set as the server side.

4. Modify the character set of oracle

Once the database is created, the character set of the database can not be changed in theory. Therefore, it is important to consider which character set to use at the beginning of your design and installation. According to Oracle's official statement, character set conversion is supported from subset to superset, and vice versa. If there is no subset and superset relationship between the two character sets, then character set conversion is not supported by oracle. As for the database server, incorrect modification of the character set will lead to many unpredictable consequences, which may seriously affect the normal operation of the database, so be sure to confirm whether there is a subset and superset relationship between the two character sets before modification. Generally speaking, we do not recommend modifying the character set on the server side of the oracle database unless you have to. In particular, there is no subset and superset relationship between the two most commonly used character sets, ZHS16GBK and ZHS16CGB231280, so theoretically, the conversion between the two character sets is not supported.

Modify the server character set (not recommended)

Before oracle 8, you could change the character set of the database by directly modifying the data dictionary table props$. However, after oracle8, at least three system tables record the information of the database character set, and only changing the props$ table is not complete, which may lead to serious consequences. The correct modification method is as follows:

$sqlplus / nolog SQL > conn / as sysdba; # if the database server is started at this time, first 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 are no large objects, there is no impact on language conversion during use. (keep in mind that the character set must be supported by ORACLE, otherwise you cannot startup) follow the above approach, but there may be a prompt such as' ORA-12717: Cannot ALTER DATABASE NATIONAL CHARACTER SET when NCLOB data exists'. There are two ways to solve this problem. One is to use the INTERNAL_USE keyword to modify the locale, and the other is to use re-create, but re-create is a bit complicated, so 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 follow the above approach, there will be no problem with the locale of National charset

Modify dmp file character set

As mentioned above, the second and third bytes of the dmp file record the character set information, so directly modifying the contents of the second and third bytes of the dmp file can 'deceive' the oracle check. In theory, it can only be modified from subset to superset, but in many cases, it can also be modified without the relationship between subset and superset. Some commonly used character sets, such as US7ASCII,WE8ISO8859P1,ZHS16CGB231280,ZHS16GBK, can be changed. Because the change is only the dmp file, so it has little impact.

There are many specific modification methods, the simplest of which is to modify the second and third bytes of the dmp file directly with UltraEdit.

For example, if you want to change the character set of a dmp file to ZHS16GBK, you can use the following SQL to find the hexadecimal code corresponding to that character set:

SQL > select to_char (nls_charset_id ('ZHS16GBK'),' xxxx') from dual; TO_CH- 354SQL >

Then change the 2 or 3 bytes of the dmp file to 0354.

If the dmp file is too large to be opened with ue, you need to use the program method.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.