修改数据库字符集

最新推荐文章于 2024-11-13 09:48:05 发布

原创最新推荐文章于 2024-11-13 09:48:05 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #character #database #system #date #parameters

Oracle 专栏收录该内容

90 篇文章

订阅专栏

本文介绍如何在Oracle数据库中更改字符集。通过一系列SQL命令演示了从AL32UTF8到ZHS16GBK的转换过程，并解释了在不同操作模式下进行字符集转换的要求。

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

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>