al32utf8的日期格式_docker 下Oracle 12C 数据库字符集ZHS16GBK改为AL32UTF8

Oracle 数据库字符修改

1.查询 oracle 镜像名称

查看镜像名称

2. 在 docker 下 进入 Oracle 数据库容器里

# 进入oracle 容器

[root@localhost ~]# docker exec -it oracl /bin/bash

# 切换到 oracle 用户

root@e7647ff4db28:/# su oracle

3.使用 管理员身份登录 SQLplus

# 进入 oracle bin 目录下

oracle@e7647ff4db28:/$ cd u01/app/oracle/product/12.1.0/xe/bin

# 使用管理员身份登录SQLplus

oracle@e7647ff4db28:/u01/app/oracle/product/12.1.0/xe/bin$ ./sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jul 24 06:05:05 2020

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL>

4.修改字符集

查看字符集

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.

5.其他

​ 以上字符集修改就已经完成了。如果使用客户端工具连接出现中文乱码问题，可能是操作系统字符集和 Oracle 服务器字符集不同，导致。

解决方案：

查询服务器字符集

SQL>

select userenv('language') from dual;

USERENV('LANGUAGE')

----------------------------------------------------

AMERICAN_AMERICA.AL32UTF8

修改操作系统环境变量，步骤如下

进入高级设置

点击环境变量

新建环境变量，点击确认即可，变量值与你刚才查出的服务器字符集一样。

新建环境变量

一个在码农道路上孤独行走的人

微信搜索【Java猿记】，关注并回复【面试】即可领取283页PDF 面试资料一份。