Benjamin Zhang

目前就职于中科院计算机网络信息中心,主要兴趣集中在数据库,操作系统,中间件。

【Oracle】Oracle字符集修改

01 Mar 2019 » Oracle

Oracle字符集修改

  1. 先决条件

    思路:通过csscan工具,来验证所有的数据是可转的,再做字符集修改。

    具体细节参照:

  2. 查看Oracle server端字符集

    SQL> select userenv('language') from dual;
       
    USERENV('LANGUAGE')
    ----------------------------------------------------
    AMERICAN_AMERICA.ZHS16GBK
    
  3. 数据库启动至mount状态

    [oracle@db ~]$ sqlplus "/as sysdba"
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount                                 
    ORACLE instance started.
       
    Total System Global Area 1.7103E+10 bytes
    Fixed Size                  2270360 bytes
    Variable Size            2415922024 bytes
    Database Buffers         1.4663E+10 bytes
    Redo Buffers               21684224 bytes
    Database mounted.
    
  4. 修改server端字符集

    SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; 
    SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
    SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0; 
    SQL> alter database open; 
    SQL> alter database character set AL32UTF8;
    alter database character set AL32UTF8
    *
    ERROR at line 1:
    ORA-12712: new character set must be a superset of old character set
    SQL> alter database character set  internal_use AL32UTF8;
    Database altered.
    
    • 修改为UTF-8—这是为了兼容8i。

      SQL> alter database character set internal_use utf8;
      
    • 修改为AL32UTF8—(如果数据库版本都在9i及其以上,不需要考虑ORACLE8的数据库,建议使用AL32UTF8字符集,它采用的Unicode标准要比UTF8采用的Unicode标准更新,支持的字符也更多一些。)

      SQL> alter database character set  internal_use AL32UTF8;
      
  5. 查看修改后的字符集

    SQL> select value from v$nls_parameters where parameter='NLS_CHARACTERSET';
       
    VALUE
    ----------------------------------------------------------------
    AL32UTF8