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
注意:
修改数据库字符集,可能导致数据库中存储的数据发生乱码现象,所以请不要在生产库上进行测试。
领取专属 10元无门槛券
私享最新 技术干货