Oracle Database字符集(1)--案例分析

OracleDatabase字符集（1）--案例分析案例分析：一次数据库导出（exp）案例分析1）数据库字符集12:40:37SYS@prodshowparameternlsNAME&n..

oracle database字符集（1）--案例分析

案例分析：

一次数据库导出（exp）案例分析

1）数据库字符集

12:40:37 SYS@ prod>show parameter nls

NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ nls_language                         string      AMERICAN nls_territory                        string      AMERICA

12:40:49 SYS@ prod>select userenv('language') from dual;

USERENV('LANGUAGE') ---------------------------------------------------- AMERICAN_AMERICA.ZHS16GBK

12:41:15 SYS@ prod>select to_char(nls_charset_id('ZHS16GBK'), 'xxxx') from dual;

TO_CH -----   354

2、导出table（EXP）

[oracle@rh6 exp]$ exp scott/tiger file=emp.dmp indexes=n log=emp.log tables=emp

Export: Release 11.2.0.1.0 - Production on Tue Aug 12 12:43:18 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses ZHS16GBK character set (possible charset conversion)

Note: indexes on tables will not be exported

About to export specified tables via Conventional Path ...

. . exporting table                            EMP         14 rows exported

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

@导出错误

[oracle@rh6 ~]$ oerr exp 00091

00091, 00000, "Exporting questionable statistics." // *Cause:  Export was able export statistics, but the statistics may not be //          usuable. The statistics are questionable because one or more of //          the following happened during export: a row error occurred, client //          character set or NCHARSET does not match with the server, a query //          clause was specified on export, only certain partitions or //          subpartitions were exported, or a fatal error occurred while //          processing a table. // *Action: To export non-questionable statistics, change the client character //          set or NCHARSET to match the server, export with no query clause, //          export complete tables. If desired, import parameters can be //          supplied so that only non-questionable statistics will be imported, //          and all questionable statistics will be recalculated.

3、查看Client字符集

[oracle@rh6 ~]$ echo $LANG

en_US.UTF-8

4、查看导出文件字符集

[oracle@rh6 exp]$ cat emp.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6

0345

5、通过ultraedit查看导出文件字符集

@从2、3字节来看，，字符集16进制代码为‘0001’

13:36:27 SYS@ prod>select nls_charset_name(to_number('0001','xxxx')) from dual;

NLS_CHARSET_NAME(TO_NUMBER('0001','XXXX' ---------------------------------------- US7ASCII

@‘0001’的字符集为‘US7ASCII',在导出时发生了转换

6、修改客户端字符集

[oracle@rh6 ~]$ cat .bash_profile

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK 

[oracle@rh6 ~]$ echo $NLS_LANG

AMERICAN_AMERICA.ZHS16GBK

[oracle@rh6 ~]$ echo $LANG

en_US.UTF-8

7、重新导出table（EXP)