修改数据库字符集-优快云博客

本文详细介绍了在数据库操作过程中遇到字符集不一致导致的问题，并提供了通过修改数据库字符集来解决该问题的方法。具体步骤包括使用SQL命令调整字符集，确保数据库处于特定模式下，以及在调整字符集前后的关键参数检查和设置。

数据库导入导出的时候经常会提示字符集不同，可以通过下面方法修改数据库字符集：

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


SQL> ALTER DATABASE character set INTERNAL_USE zhs16gbk;
ALTER DATABASE character set INTERNAL_USE zhs16gbk
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode


SQL> select value from nls_database_parameters where parameter='NLS_CHARACTERSE
';

VALUE
-------------------------------------------------------------------------------

AL32UTF8

SQL> conn sys/system as sysdba
Connected.
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> select name,value$ from props$ where name like '%NLS%';

NAME
------------------------------
VALUE$
-------------------------------------------------------------------------------

NLS_LANGUAGE
AMERICAN

NLS_TERRITORY
AMERICA

NLS_CURRENCY
$


NAME
------------------------------
VALUE$
-------------------------------------------------------------------------------

NLS_ISO_CURRENCY
AMERICA

NLS_NUMERIC_CHARACTERS
.,

NLS_CHARACTERSET
AL32UTF8


NAME
------------------------------
VALUE$
-------------------------------------------------------------------------------

NLS_CALENDAR
GREGORIAN

NLS_DATE_FORMAT
DD-MON-RR

NLS_DATE_LANGUAGE
AMERICAN


NAME
------------------------------
VALUE$
-------------------------------------------------------------------------------

NLS_SORT
BINARY

NLS_TIME_FORMAT
HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM


NAME
------------------------------
VALUE$
-------------------------------------------------------------------------------

NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY
$


NAME
------------------------------
VALUE$
-------------------------------------------------------------------------------

NLS_COMP
BINARY

NLS_LENGTH_SEMANTICS
BYTE

NLS_NCHAR_CONV_EXCP
FALSE


NAME
------------------------------
VALUE$
-------------------------------------------------------------------------------

NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_RDBMS_VERSION
10.2.0.4.0


20 rows selected.

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

Total System Global Area 1610612736 bytes
Fixed Size                  2066080 bytes
Variable Size             385878368 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14708736 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> set linesize 120;
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


SQL> ALTER DATABASE character set INTERNAL_USE zhs16gbk;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> shutdown immediate;
ORA-01012: not logged on
SQL> STARTUP
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2066080 bytes
Variable Size             385878368 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14708736 bytes
Database mounted.
Database opened.
SQL> select name,value$ from props$ where name like '%NLS%';

NAME
------------------------------
VALUE$
-------------------------------------------------------------------------------
----------------------------------------
NLS_LANGUAGE
AMERICAN

NLS_TERRITORY
AMERICA

NLS_CURRENCY
$


NAME
------------------------------
VALUE$
-------------------------------------------------------------------------------
----------------------------------------
NLS_ISO_CURRENCY
AMERICA

NLS_NUMERIC_CHARACTERS
.,

NLS_CHARACTERSET
ZHS16GBK


NAME
------------------------------
VALUE$
-------------------------------------------------------------------------------
----------------------------------------
NLS_CALENDAR
GREGORIAN

NLS_DATE_FORMAT
DD-MON-RR

NLS_DATE_LANGUAGE
AMERICAN


NAME
------------------------------
VALUE$
-------------------------------------------------------------------------------
----------------------------------------
NLS_SORT
BINARY

NLS_TIME_FORMAT
HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM


NAME
------------------------------
VALUE$
-------------------------------------------------------------------------------
----------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY
$


NAME
------------------------------
VALUE$
-------------------------------------------------------------------------------
----------------------------------------
NLS_COMP
BINARY

NLS_LENGTH_SEMANTICS
BYTE

NLS_NCHAR_CONV_EXCP
FALSE


NAME
------------------------------
VALUE$
-------------------------------------------------------------------------------
----------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_RDBMS_VERSION
10.2.0.4.0


20 rows selected.

SQL>