用plsql developer可以查询出中文,显示正常
用sqlplus执行命令,中文显示为乱码????
查找原因:
查看database的字符集:
sys>select * from v$nls_parameters ;
PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ¥
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY ¥
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
字符集为ZHS16GBK ,语言为SIMPLIFIED CHINESE
查看客户端字符集:
$ env |grep LANG
LANG=en_US.UTF-8
发现了问题,竟然只有语言,没有设置NLS_LANG
执行如下命令设置成和database一样的字符集
export NLS_LANG="simplified chinese_china.zhs16gbk"
取消LANG的环境变量
unset LANG
进入sqlplus后查询,查询的中文显示正常,但是如果直接输入的sql语句中的中文还是乱码
由此推断是SecureCRT的字符集设置问题
会话选项-终端-外观-字符编码,选择GB2312即可,选择UTF-8有时候可能会有问题,这很好理解,因为这些字符集是支持中文的,但
GB2312是ZHS16GBK的子集,而ZHS16GBK又是UTF-8的子集
设置为GB2312后,sqlplus输入中文显示正常
11:07:09 sys>select upper(f.tablespace_name) "表空间名",
11:10:52 2 d.tot_grootte_mb - f.total_bytes "已使用空间(g)",
11:10:52 3 d.tot_grootte_mb "表空间大小(g)",
11:10:52 4 to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,2),'990.99') || '%' "使用比",
11:10:52 5 f.total_bytes "空闲空间(g)",
11:10:52 6 f.max_bytes "最大块(g)"
11:10:52 7 from (select tablespace_name,
11:10:52 8 round(sum(bytes) / (1024 * 1024 * 1024), 2) total_bytes,
11:10:53 9 round(max(bytes) / (1024 * 1024 * 1024), 2) max_bytes
11:10:53 10 from dba_free_space
11:10:53 11 group by tablespace_name) f,
11:10:53 12 (select dd.tablespace_name,
11:10:53 13 round(sum(dd.bytes) / (1024 * 1024 * 1024 ), 2) tot_grootte_mb
11:10:53 14 from dba_data_files dd
11:10:53 15 group by dd.tablespace_name) d
11:10:53 16 where d.tablespace_name = f.tablespace_name
11:10:53 17 order by 4 desc
11:10:53 18 /
表空间名 已使用空间(g) 表空间大小(g) 使用比 空闲空间(g) 最大块(g)
------------------------------ ------------- ------------- -------- ----------- ----------
USERS 7.04 8.07 87.24% 1.03 .78
...