修改数据库字符集

Keyword：

修改数据库字符集

NLS_CHARACTERSET

NLS_NCHAR_CHARACTERSET

正文：

通常来讲，数据库建成后，字符集不允许修改，但是由于测试的目的可能需要修改数据库的字符集，具体修改方法可参考如下：

1.确认修改前的字符集：

命令：

conn /as sysdba

col PARAMETER format a30

col VALUE format a30

set pagesize 200

select * from NLS_DATABASE_PARAMETERS;

SELECT * FROM NLS_SESSION_PARAMETERS;

SELECT USERENV ('language') FROM DUAL;

执行例：

SQL> select * from NLS_DATABASE_PARAMETERS;

PARAMETER VALUE

------------------------------ ------------------------------

NLS_RDBMS_VERSION 12.1.0.2.0

NLS_NCHAR_CONV_EXCP FALSE

NLS_LENGTH_SEMANTICS BYTE

NLS_COMP BINARY

NLS_DUAL_CURRENCY $

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

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

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

NLS_TIME_FORMAT HH.MI.SSXFF AM

NLS_SORT BINARY

NLS_DATE_LANGUAGE AMERICAN

NLS_DATE_FORMAT DD-MON-RR

NLS_CALENDAR GREGORIAN

NLS_NUMERIC_CHARACTERS .,

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_CHARACTERSET AL32UTF8

NLS_ISO_CURRENCY AMERICA

NLS_CURRENCY $

NLS_TERRITORY AMERICA

NLS_LANGUAGE AMERICAN

20 rows selected.

SQL> SELECT USERENV ('language') FROM DUAL;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.AL32UTF8

2.进行数据库的字符集修改：

shu immediate

startup restrict

alter database character set INTERNAL_USE JA16SJISTILDE;

alter database national character set INTERNAL_USE AL16UTF16;

EXECUTE SYS.DBMS_METADATA_UTIL.LOAD_STYLESHEETS;

shu immediate

startup

执行例：

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup restrict

ORACLE instance started.

Total System Global Area 1660944384 bytes

Fixed Size 2925072 bytes

Variable Size 1040190960 bytes

Database Buffers 603979776 bytes

Redo Buffers 13848576 bytes

Database mounted.

Database opened.

SQL> alter database character set INTERNAL_USE JA16SJISTILDE;

Database altered.

SQL> alter database national character set INTERNAL_USE AL16UTF16;

Database altered.

SQL> EXECUTE SYS.DBMS_METADATA_UTIL.LOAD_STYLESHEETS;

PL/SQL procedure successfully completed.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1660944384 bytes

Fixed Size 2925072 bytes

Variable Size 1040190960 bytes

Database Buffers 603979776 bytes

Redo Buffers 13848576 bytes

Database mounted.

Database opened.

3.确认后修改后的字符集：

select * from NLS_DATABASE_PARAMETERS;

SELECT * FROM NLS_SESSION_PARAMETERS;

SELECT USERENV ('language') FROM DUAL;

执行例：

SQL> select * from NLS_DATABASE_PARAMETERS;

PARAMETER VALUE

------------------------------ ------------------------------

NLS_RDBMS_VERSION 12.1.0.2.0

NLS_NCHAR_CONV_EXCP FALSE

NLS_LENGTH_SEMANTICS BYTE

NLS_COMP BINARY

NLS_DUAL_CURRENCY $

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

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR

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

NLS_TIME_FORMAT HH.MI.SSXFF AM

NLS_SORT BINARY

NLS_DATE_LANGUAGE AMERICAN

NLS_DATE_FORMAT DD-MON-RR

NLS_CALENDAR GREGORIAN

NLS_NUMERIC_CHARACTERS .,

NLS_NCHAR_CHARACTERSET AL16UTF16

NLS_CHARACTERSET JA16SJISTILDE★

NLS_ISO_CURRENCY AMERICA

NLS_CURRENCY $

NLS_TERRITORY AMERICA

NLS_LANGUAGE AMERICAN

20 rows selected.

SQL> SELECT USERENV ('language') FROM DUAL;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.JA16SJISTILDE

注意：

修改数据库字符集，可能导致数据库中存储的数据发生乱码现象，所以请不要在生产库上进行测试。