String encoding is not uniform, which will lead to import exceptions, display garbled code and other problems. Here is the solution
1. Query oracle server character code
select userenv('language') from dual ; select * from V$NLS_PARAMETERS; select * from sys.nls_database_parameters;
2. Query oracle client character encoding
select * from sys.nls_session_parameters;
- 2.1 in the window environment, modify the environment variable NLS Lang in the Oracle home directory of the registry.
- 2.2 in the UNIX / Linux environment, it is the environment variable $NLS < Lang [oracle@wang ~]$ echo $NLS_LANG AMERICAN_AMERICA.ZHS16GBK The server side and the client side should have the same coding, otherwise, there will be garbled code when retrieving or importing the exported data.
3. Modify Oracle server character encoding
SQL> sqlplus / as sysdba SQL> select userenv('language') from dual; SQL> UPDATE PROPS$ SET VALUE$='ZHS16GBK' WHERE NAME='NLS_CHARACTERSET'; SQL> commit; SQL> shutdown immediate; SQL> startup;
4. Force modification of character encoding
The above modification method is likely to be incomplete, resulting in the following error
- First query to confirm whether the character set modification is incomplete
SELECT DISTINCT (NLS_CHARSET_NAME(CHARSETID)) CHARACTERSET, DECODE(TYPE#, 1, DECODE(CHARSETFORM, 1, 'VARCHAR2', 2, 'NVARCHAR2', 'UNKOWN'),9, DECODE(CHARSETFORM, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),96, DECODE(CHARSETFORM, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),112, DECODE(CHARSETFORM, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN FROM SYS.COL$ WHERE CHARSETFORM IN (1, 2) AND TYPE# IN (1, 9, 96, 112);
- If the above query does show the setting of multiple character sets, the following processing (sentence by sentence) will be performed:
SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; ALTER SYSTEM SET AQ_TM_PROCESSES=0; ALTER DATABASE OPEN; COL VALUE NEW_VALUE CHARSET SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET'; COL VALUE NEW_VALUE NCHARSET SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET'; --INTERNAL_USE Is a parameter not written in the document to force character set consistency ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET; ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET; SHUTDOWN IMMEDIATE; STARTUP; -- Start the database again SHUTDOWN IMMEDIATE; STARTUP;
Note: modifying character set only modifies data dictionary, and does not convert character set to data!