• Oracle NLS_NCHAR_CHARACTERSET change


    注意:
    
    1)这样修改会影响数据库中定义了Nclob、NVARCHAR2等大字段的表,会出现乱码。需要重新导入表,当然在修改前要备份相关表。
    
    2)如果没有大对象,在使用过程中进行语言转换没有什么影响,(切记设定的字符集必须是ORACLE支持,不然不能start)
    
    若直接运行
    
    ALTER DATABASE NATIONAL CHARACTER SET AL16UTF16;
    
    也可以,但是可能会出现‘ORA-12717: Cannot ALTER DATABASE NATIONAL CHARACTER SET when
     NCLOB data exists’ 这样的提示信息
    
    
    Last login: Sat May  5 01:16:47 2018 from 114.84.253.79
    [root@db-test ~]# su - oracle
    Last login: Fri May  4 16:56:34 UTC 2018 on pts/2
    [oracle@db-test ~]$ export ORACLE_SID=auc
    [oracle@db-test ~]$ sqlplus / as sysdba
    
    SQL*Plus: Release 12.1.0.2.0 Production on Sat May 5 01:20:18 2018
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount exclusive
    ORACLE instance started.
    
    Total System Global Area 2.3689E+10 bytes
    Fixed Size                  3723728 bytes
    Variable Size            1.1744E+10 bytes
    Database Buffers         1.1878E+10 bytes
    Redo Buffers               63381504 bytes
    Database mounted.
    SQL> alter database open;
    
    Database altered.
    
    SQL> col parameter for a40
    SQL> col value for a30
    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
    
    PARAMETER                                VALUE
    ---------------------------------------- ------------------------------
    NLS_DATE_FORMAT                          DD-MON-RR
    NLS_CALENDAR                             GREGORIAN
    NLS_NUMERIC_CHARACTERS                   .,
    NLS_NCHAR_CHARACTERSET                   AL16UTF16
    NLS_CHARACTERSET                         ZHS16GBK
    NLS_ISO_CURRENCY                         AMERICA
    NLS_CURRENCY                             $
    NLS_TERRITORY                            AMERICA
    NLS_LANGUAGE                             AMERICAN
    
    20 rows selected.
    
    SQL> alter database national character set internal_use UTF8;
    alter database national character set internal_use UTF8
    *
    ERROR at line 1:
    ORA-12719: operation requires database is in RESTRICTED mode
    
    
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount exclusive;
    ORACLE instance started.
    
    Total System Global Area 2.3689E+10 bytes
    Fixed Size                  3723728 bytes
    Variable Size            1.1744E+10 bytes
    Database Buffers         1.1878E+10 bytes
    Redo Buffers               63381504 bytes
    Database mounted.
    SQL> alter system enable restricted session;
    
    System altered.
    
    SQL> alter system open;
    alter system open
                 *
    ERROR at line 1:
    ORA-02065: illegal option for ALTER SYSTEM
    
    
    SQL> alter database open;
    
    Database altered.
    
    SQL> alter database national character set internal_use UTF8;
    
    Database altered.
    
    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
    
    PARAMETER                                VALUE
    ---------------------------------------- ------------------------------
    NLS_DATE_FORMAT                          DD-MON-RR
    NLS_CALENDAR                             GREGORIAN
    NLS_NUMERIC_CHARACTERS                   .,
    NLS_NCHAR_CHARACTERSET                   UTF8
    NLS_CHARACTERSET                         ZHS16GBK
    NLS_ISO_CURRENCY                         AMERICA
    NLS_CURRENCY                             $
    NLS_TERRITORY                            AMERICA
    NLS_LANGUAGE                             AMERICAN
    
    20 rows selected.
    
    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area 2.3689E+10 bytes
    Fixed Size                  3723728 bytes
    Variable Size            1.1744E+10 bytes
    Database Buffers         1.1878E+10 bytes
    Redo Buffers               63381504 bytes
    Database mounted.
    Database opened.
    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
    
    PARAMETER                                VALUE
    ---------------------------------------- ------------------------------
    NLS_DATE_FORMAT                          DD-MON-RR
    NLS_CALENDAR                             GREGORIAN
    NLS_NUMERIC_CHARACTERS                   .,
    NLS_NCHAR_CHARACTERSET                   UTF8
    NLS_CHARACTERSET                         ZHS16GBK
    NLS_ISO_CURRENCY                         AMERICA
    NLS_CURRENCY                             $
    NLS_TERRITORY                            AMERICA
    NLS_LANGUAGE                             AMERICAN
    
    20 rows selected.
    
    SQL>
    
    All for u
  • 相关阅读:
    SpringBoot04-web
    springboot03-日志功能
    SpringBoot02-自动配置原理
    SpringBoot02
    SpringBoot01
    八大排序算法
    SpringSecurity04
    SpringSecurity03
    SpringSecurity02
    SpringSecurity01
  • 原文地址:https://www.cnblogs.com/ayumie/p/8993714.html
Copyright © 2020-2023  润新知