Oracle 12c 数据库字符集ZHS16GBK改为AL32UTF8
一、查看字符集
- SQL> select * from v$version;
- BANNER CON_ID
- -------------------------------------------------------------------------------- ----------
- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
- PL/SQL Release 12.1.0.2.0 - Production 0
- CORE 12.1.0.2.0 Production 0
- TNS for Linux: Version 12.1.0.2.0 - Production 0
- NLSRTL Version 12.1.0.2.0 - Production 0
- SQL> select userenv('language') from dual;
- USERENV('LANGUAGE')
- ----------------------------------------------------
- AMERICAN_AMERICA.ZHS16GBK
- SQL>
- SQL> select * from v$nls_parameters;
- PARAMETER VALUE CON_ID
- ------------------------------ ------------------------------ ----------
- NLS_LANGUAGE AMERICAN 0
- NLS_TERRITORY AMERICA 0
- NLS_CURRENCY $ 0
- NLS_ISO_CURRENCY AMERICA 0
- NLS_NUMERIC_CHARACTERS ., 0
- NLS_CALENDAR GREGORIAN 0
- NLS_DATE_FORMAT DD-MON-YYYY HH24:MI:SS 0
- NLS_DATE_LANGUAGE AMERICAN 0
- NLS_CHARACTERSET ZHS16GBK 0
- NLS_SORT BINARY 0
- NLS_TIME_FORMAT HH.MI.SSXFF AM 0
- PARAMETER VALUE CON_ID
- ------------------------------ ------------------------------ ----------
- NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 0
- NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 0
- NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 0
- NLS_DUAL_CURRENCY $ 0
- NLS_NCHAR_CHARACTERSET AL16UTF16 0
- NLS_COMP BINARY 0
- NLS_LENGTH_SEMANTICS BYTE 0
- NLS_NCHAR_CONV_EXCP FALSE 0
- 19 rows selected.
- SQL>
- 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> shutdown immediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> startup mount;
- ORACLE instance started.
- Total System Global Area 1778384896 bytes
- Fixed Size 2925408 bytes
- Variable Size 1627393184 bytes
- Database Buffers 134217728 bytes
- Redo Buffers 13848576 bytes
- Database mounted.
- SQL> alter session set sql_trace=true;
- Session altered.
- SQL> alter system enable restricted session;
- System altered.
- SQL> alter system set job_queue_processes=0;
- System altered.
- SQL> alter system set aq_tm_processes=0;
- System altered.
- SQL> alter database open;
- Database altered.
- SQL> ALTER DATABASE character set INTERNAL_USE AL32UTF8;
- Database altered.
- SQL> shutdown immediate;
- Database closed.
- Database dismounted.
- ORACLE instance shut down.
- SQL> startup
- ORACLE instance started.
- Total System Global Area 1778384896 bytes
- Fixed Size 2925408 bytes
- Variable Size 1627393184 bytes
- Database Buffers 134217728 bytes
- Redo Buffers 13848576 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 AL16UTF16
- NLS_CHARACTERSET AL32UTF8
- NLS_ISO_CURRENCY AMERICA
- NLS_CURRENCY $
- NLS_TERRITORY AMERICA
- NLS_LANGUAGE AMERICAN
- 20 rows selected.
来源: http://www.linuxidc.com/Linux/2017-12/149264.htm