docker上oracle数据库乱码解决方案

oracle数据库client端字符集修改

win新建环境变量

NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

oracle数据库server端字符集修改

1.docker进入oracle的命令行

docker exec -it XXXX bash

2.将数据库启动到RESTRICTED模式下做字符集更改：

alter system enable restricted session;

如果需要取消

alter system disable restricted session;

需要注意的是RESTRICTED模式以后 除了管理员都不能登录，如果需要非管理员登录，必须

GRANT restricted session to test；

3.用sys身份as sysdba连接并修改(注意每条语句都要以 ; 结尾)

SQL> conn sys/[email protected] as sysdba; 
Connected.

4.关闭数据库

SQL> shutdown immediate; 
Database closed. 
Database dismounted. 
ORACLE instance shut down. 

5.启动数据库到Mount状态下

SQL> startup mount 
RACLE instance started. 
Total System Global Area  236000356 bytes 
Fixed Size                   451684 bytes 
Variable Size             201326592 bytes 
Database Buffers           33554432 bytes 
Redo Buffers                 667648 bytes 
Database mounted. 
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. 

6.启动数据库

SQL> alter database open; 
Database altered. 

7.修改字符集

SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK; 
Database altered. 

注：
1. 如果数据库表中有CLOB类型的列，是不允许修改字符集的，解决方法为，先导出这个表的内容，然后删除这个表，修改完后，再导入这个表的内容就可以了。
2. 旧的字符集必须是新的字符集的子集，否则不能修改。

8.修改完后，重启查看一下修改是否成功。

SQL> shutdown immediate; 
Database closed. 
Database dismounted. 
ORACLE instance shut down. 
SQL> startup 
ORACLE instance started. 
Total System Global Area  236000356 bytes 
Fixed Size                   451684 bytes 
Variable Size             201326592 bytes 
Database Buffers           33554432 bytes 
Redo Buffers                 667648 bytes 
Database mounted. 
Database opened. 
SQL> select * from v$nls_parameters; 
略 
19 rows selected.