当前位置:Gxlcms > mysql > 更改Oracle字符集:把字符集ZHS16GBK换成UTF8

更改Oracle字符集:把字符集ZHS16GBK换成UTF8

时间:2021-07-01 10:21:17 帮助过:33人阅读

更改Oracle字符集:把字符集ZHS16GBK换成UTF8

SQL> select name,value$ from props$ where name like '%NLS%';

NAME VALUE$
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

NAME VALUE$
------------------------------ ------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.1.0

20 rows selected.

NLS_CHARACTERSET是数据库字符集,NLS_NCHAR_CHARACTERSET是国家字符集
Oracle中有两大类字符型数据,,VARCHAR2是按照数据库字符集来存储数据。
而NVARCHAR2是按照国家字符集存储数据的。同样,CHAR和NCHAR也一样,一是数据库字符符,一是国家字符集。


转换字符集,数据库应该在RESTRICTED模式下
首先要确定修改后的字符集是不是修改前的超集,如果不是可能出现相同的代码点对应不同的字符,出现乱码的问题。

出现这个错误是 oracle 只支持从子集到超集的转变
那有什么方法可进行强制转换呢?
该指令会跳过子集与超集的检验,当然强制转换可能会造成数据的损坏,要谨慎使用!!
下面借助eygle的帖子执行。

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1845493760 bytes
Fixed Size 2021568 bytes
Variable Size 452986688 bytes
Database Buffers 1375731712 bytes
Redo Buffers 14753792 bytes
Database mounted.
SQL> alter session set sql_trace=true;

Session altered.

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 INTERNAL_USE UTF8;

Database altered.

SQL>update props$ set VALUE$='UTF8' where

linux

人气教程排行