Navicat error connecting ORACLE: ora-12737: Instant Client light: unsupport server charater set zhs16gbk solution. (Limit L

This is because the character string of the Oracle database server is zhs16gbk, which is not supported by navicat. Solution: Modify the character set of the Oracle server, log on to the Oracle database as sysdba, and modify the nls_characterset and nls_nchar_characterset of the props $ system table (table maintained by Oracle). The SQL statement is as follows:
Update props $ set value $ = 'al32utf8' where name = 'nls _ characterset 'or name = 'nls _ nchar_characterset ';
 
SQL> Update props $ set value $ = 'al32utf8' where name = 'nls _ characterset 'or name = 'nls _ nchar_characterset ';
 
2 rows updated

Restart the computer.

If you start the instance manually, the following error occurs:
After the oracleoradb10g_home1tnslistener service on the local computer is started, it stops... solution:
Http://blog.csdn.net/centre10/archive/2010/12/07/6060828.aspx
Server Character Set: nls_characterset and nls_nchar_characterset
Client Character Set: Windows: in the registry, modify the profile in nls_lang Linux or. bash_profile.
Backup File Character Set: First back up a database table and find 2nd and 3 pieces of data, for example, 0369. Use related commands to obtain the character set.

The following article will be useful.
I. Oracle character set:
The Oracle character set is a collection of symbols for the interpretation of byte data. It can be divided into different sizes and have an inclusive relationship. Oracle supports the national language architecture, allowing you to store, process, and retrieve data in a localized language. It makes database tools, error messages, sorting order, date, time, currency, numbers, and calendar automatically adapt to localization languages and platforms.

The most important parameter that affects the character set of Oracle databases is the nls_lang parameter.

The format is as follows: nls_lang = language_territory.charset

It has three components (language, region, and Character Set), each of which controls the NLS subset features.

Where:

Language specifies the language of the server message, territory specifies the date and number format of the server, and charset specifies the character set. For example: American _ America. zhs16gbk; American _ America. al32utf8

From the composition of nls_lang, we can see that the real impact on the database character set is actually the third part.

Therefore, if the character set between the two databases is the same as that in the third part, data can be imported and exported to each other. The preceding information is only prompted in Chinese or English.

2. view database character sets

This involves three character sets,

The first is the character set of the kernel El server;

The second is the character set of the Oracle client;

The third is the DMP file character set.

During data import, the three character sets must be consistent before the data can be correctly imported.

1. query character sets of ORACLE Server

There are many ways to find the character set of the Oracle server. The intuitive query method is as follows:

SQL> select userenv ('language') from dual;

The results are as follows: American _ America. zhs16gbk

2. How to query the DMP file Character Set
Exp command backup database
To the bin directory of Oracle: cd c:/Oracle/bin (Oracle Installation Directory)
Exp userid = Scott/center @ orcl tables = (EMP) file = D:/EMP. dmp (back up a single table)
Exp userid = Scott/center @ orcl tables = (EMP, Dept) file = D:/emp1.dmp (back up multiple tables)
Exp userid = Scott/center @ orcl tables = (EMP) file = D:/emp3.dmp rows = N (Backup table structure)
Exp userid = Scott/center @ orcl tables = (EMP) file = D:/emp2.dmp direct = Y (direct export)
Exp userid = Scott/center @ orcl owner = Scott file = D:/Scott. dmp (back up your own solution)
Exp userid = system/center @ orcl owner = (system, Scott) file = D:/system. dmp (back up any solution)
IMP command Restore database
IMP userid = Scott/center @ orcl tables = (EMP) file = D:/XX. dmp (import your own table)
IMP userid = system/center @ orcl tables = (EMP) file = D:/XX. dmp touser = Scott (imported to another table)
IMP userid = Scott/center @ orcl tables = (EMP) file = D:/emp3.dmp rows = N (import table structure without importing data)
........

The DMP file exported using Oracle's exp tool also contains character set information. The 2nd and 3rd bytes of the DMP file record the character set of the DMP file. If the DMP file is not large, for example, only a few MB or dozens of MB, you can use ultraedit to open it (in hexadecimal mode) and view the content of 2nd 3rd bytes, such as 0354, then, use the following SQL statement to find the corresponding character set:

SQL> select nls_charset_name (to_number ('20140901', 'xxxxx') from dual;

Al32utf8

If the DMP file is large, for example, 2 GB or above (this is also the most common case), you can use the following command (on a unix host) to open it slowly or completely ):

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

Then, you can use the preceding SQL statement to obtain its character set.

3. query the character set of the Oracle client

This is relatively simple.

On Windows, it is the nls_lang of oraclehome in the registry. You can also set it in the DOS window, for example:
Set nls_lang = american_america.zhs16gbk

In this way, only the environment variables in this window are affected.

On UNIX platforms, the environment variable nls_lang is used.

$ Echo $ nls_lang

American_america.zhs16gbk

If the check result shows that the character sets on the server and client are inconsistent, change them to the same character set on the server.

Supplement:

(1). Database Server Character Set Environment

Select * From nls_database_parameters;

The source is props $, which indicates the character set of the database.

(2). Client Character Set Environment

Select * From nls_instance_parameters;

It is derived from V $ parameter, which indicates the character set setting of the client, which may be a parameter file, environment variable, or registry.

(3). Session Character Set Environment

Select * From nls_session_parameters;

The source is V $ nls_parameters, which indicates the session's own settings. It may be the session's environment variable or the alter session is completed. If the session has no special settings, it will be consistent with nls_instance_parameters.

(4). Only when the character set of the client must be the same as that of the server can the non-ASCII characters of the database be correctly displayed. If multiple settings exist, alter session> environment variable> registry> parameter file

The character set must be consistent, but the language settings can be different. We recommend that you use English for language settings. If the character set is zhs16gbk, The nls_lang can be american_america.zhs16gbk.

3. Modify the character set of Oracle

As mentioned above, Oracle character sets have an inclusive relationship. For example, us7ascii is a subset of zhs16gbk. From us7ascii to zhs16gbk, there will be no data interpretation problems or data loss. Utf8 should be the largest among all character sets because it is based on Unicode and stores double-byte characters (so it occupies more space ).

Once a database is created, the character set of the database cannot be changed theoretically. Therefore, it is important to consider which character set to use at the beginning of design and installation. According to the official instructions of Oracle, Character Set conversion is from a subset to a superset, but not vice versa. If there is no relationship between Subsets and supersets between the two character sets, Character Set conversion is not supported by Oracle. For database servers, incorrect Character Set modification may lead to many unpredictable consequences, which may seriously affect the normal operation of the database, therefore, before modification, check whether the two character sets have the relationship between Subsets and supersets. Generally, we do not recommend that you modify the character set of the Oracle database server unless you have. In particular, the two most commonly used character sets zhs16gbk and zhs16cgb231280 do not have a subset or superset relationship. Therefore, in theory, mutual conversion between these two character sets is not supported.

1. Modify the server character set (not recommended)

Before Oracle 8, you can directly modify the data dictionary table props $ to change the character set of the database. However, after oracle8, at least three system tables record the information of the database character set. modifying only the props $ table is incomplete and may cause serious consequences. The correct modification method is as follows:

$ Sqlplus/nolog

SQL> Conn/As sysdba; if the database server has been started, run the shutdown immediate command to shut down the database server, and then run 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 (remember that the character set must be supported by Oracle, or cannot be started, however, a message like 'ora-12717: cannot alter database national character set when nclob data exists' may appear.

There are two ways to solve this problem

One is to use the internal_use keyword to modify the region settings,

Another method is to use re-create, but re-create is a little 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 steps, the National charset region settings will be fine.

2. Modify the DMP file Character Set

As mentioned above, the 2nd 3rd bytes of the DMP file records the character set information. Therefore, you can directly modify the 2nd 3rd bytes of the DMP file to 'Cheat 'the Oracle check. In theory, this can be modified only from the subset to the superset, but in many cases it can be modified without the subset and superset relationships. Some of our commonly used character sets, such as us7ascii, we8iso8859p1, zhs16cgb231280, and zhs16gbk can be modified. Because only the DMP file is changed, it has little impact.

There are many specific modification methods. The simplest is to directly use ultraedit to modify the 1 and 2 bytes of the DMP file.

For example, if you want to change the DMP file's character set to zhs16gbk, you can use the following SQL statement to find the hexadecimal code corresponding to this character set:
SQL> select to_char (nls_charset_id ('zhs16gbk'), 'xxx') from dual;

0354

Modify the 2 and 3 bytes of the DMP file to 0354.

If the DMP file is large and cannot be opened with ue, you need to use the program method.

Modifying character sets in Oracle
2.3oracle database Character Set Change

A. Oracle Server Character Set Query

Select userenv ('language') from dual

The nls_characterset is the server-side character set.

Nls_language is the server-side character display form

B. query the character set of the Oracle client

$ Echo $ nls_lang

If the data you select is garbled, configure the character set of the client to be the same as that of the Linux operating system. If garbled characters exist, the data in the database may be faulty, or the configuration of the Oracle server may be faulty.

C. server-side character set Modification

**************************************** *************************

* How to change the character set (we8iso8859p1 --> zhs16gbk )*

**************************************** *************************

SQL>

Start the database to restricted mode and make character set changes:

SQL> Conn/As sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

Oracle instance shut down.

SQL> startup Mount

Oracle instance started.

Total system global area 236000356 bytes

Fixed size 451684 bytes

Variable Size 201326592 bytes

Database buffers 33554432 bytes

Redo buffers 667648 bytes

Database mounted.

SQL> alter system enable restricted session;

System altered.

SQL> alter system set job_queue_processes = 0;

System altered.

SQL> alter system set aq_tm_processes = 0;

System altered.

SQL> alter database open;

Database altered.

SQL> alter database character set zhs16gbk;

Alter database character set zhs16gbk

*

Error at line 1:

ORA-12712: new character set must be a superset of old Character Set

Note: The new character set must be a superset of the old one. In this case, we can skip the superset check and make changes:

SQL> alter database character set internal_use zhs16gbk;

Database altered.

SQL> select * from V $ nls_parameters;

Omitted

19 rows selected.

Restart check whether the change is complete:

SQL> shutdown immediate;

Database closed.

Database dismounted.

Oracle instance shut down.

SQL> startup

Oracle instance started.

Total system global area 236000356 bytes

Fixed size 451684 bytes

Variable Size 201326592 bytes

Database buffers 33554432 bytes

Redo buffers 667648 bytes

Database mounted.

Database opened.

SQL> select * from V $ nls_parameters;

Omitted

19 rows selected.

We can see that this process is exactly the same as the internal process of the alter database character set operation, that is, the help provided by internal_use is

Oracle databases bypass the validation of subsets and supersets.

This method is useful in some aspects, such as testing. when applied to the product environment, everyone should be very careful. No one except you will be responsible for the consequences:

Conclusion ):

For dBA, there is a very important principle: Do not put your database in danger!

This requires us to back up the database structure before performing any operations that may change the database structure. Many DBAs do not have backup operations and have learned a painful lesson.

++ ++

D. client-side character set Modification

In. bash_profile under the/home/Oracle and/Root User Directories

Add or modify the export nls_lang = "american_america.utf8" Statement

Close the current SSH window.

Note that the nls_lang variable must be correctly configured. Otherwise, sqlplus will become invalid.