【常用命令】修改数据库字符集(仅供测试使用)

修改数据库字符集

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 注意：

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