[Oracle] about database character coding modification

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!

Tags: Programming SQL Oracle encoding Database

Posted on Mon, 18 May 2020 10:54:23 -0400 by curb