字符集例子-同一字元不同字符集編碼不同及匯入匯出的亂碼

1.演示相同的字元，在不同的字符集下，對應的編碼不同的例子。

會話1：使用ZHS16GBK字符集
[oracle@bys001 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@bys001 ~]$ echo $NLS_LANG                      
AMERICAN_AMERICA.ZHS16GBK
[oracle@bys001 ~]$ sqlplus bys/bys
BYS@bys1>insert into test values(1,'白');
1 row created.
BYS@bys1>commit;
Commit complete.
BYS@bys1>select * from test;
        ID NAME
---------- --------------------
         1 白
會話2：會話2的如果使用ZHS32GB18030、ZHS16CGB231280字符集插入後編碼和ZHS16GBK字符集的相同。
這裡使用US7ASCII來測試。
[oracle@bys001 ~]$ export NLS_LANG=AMERICAN_AMERICA.US7ASCII
[oracle@bys001 ~]$ echo $NLS_LANG                      
AMERICAN_AMERICA.US7ASCII
[oracle@bys001 ~]$ sqlplus bys/bys
BYS@bys1>insert into test values(2,'白');
1 row created.
BYS@bys1>commit;
Commit complete.
在會話2查詢：已經顯示為亂碼了。
BYS@bys1>select * from test;
        ID NAME
---------- ----------
         1 ?
         2 ??
在會話1查詢：會話2插入的漢字成了亂碼，因為會話2是US7ASCII，
BYS@bys1>select * from test;

        ID NAME
---------- --------------------
         1 白
         2 ？？

查詢字元編碼，第一次插入和第二次的不同：
BYS@bys1>col aa for a60
BYS@bys1>select id,dump(name,1016) as aa from test;

        ID AA
---------- ------------------------------------------------------------
         1 Typ=1 Len=3 CharacterSet=AL32UTF8: e7,99,bd
         2 Typ=1 Len=6 CharacterSet=AL32UTF8: ef,bf,bd,ef,bf,bd
######################################################################

3.將客戶端設定為不同的字符集，演示匯入/匯出可能產生的亂碼問題。

方法一：使用ZHS16GBK字符集，可以正常匯出匯入，此過程不產生亂碼。
[oracle@bys001 ~]$ echo $NLS_LANG                                
AMERICAN_AMERICA.ZHS16GBK
[oracle@bys001 ~]$ sqlplus bys/bys

BYS@bys1>select * from test;
        ID NAME
---------- --------------------
         1 白
         2 ？？
BYS@bys1>
BYS@bys1>exit
[oracle@bys001 ~]$ echo $NLS_LANG                                
AMERICAN_AMERICA.ZHS16GBK
[oracle@bys001 ~]$ exp bys/bys file='/home/oracle/test.dmp' tables=test rows=y
Export: Release 11.2.0.1.0 - Production on Sun Aug 11 00:49:34 2013
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                           TEST          2 rows exported
Export terminated successfully without warnings.
[oracle@bys001 ~]$ sqlplus bys/bys

BYS@bys1>select * from test;

        ID NAME
---------- --------------------
         1 白
         2 ？？
BYS@bys1>truncate table test;

Table truncated.
BYS@bys1>exit

[oracle@bys001 ~]$ imp bys/bys file='/home/oracle/test.dmp' full=y ignore=y

Import: Release 11.2.0.1.0 - Production on Sun Aug 11 00:50:22 2013

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing BYS's objects into BYS
. importing BYS's objects into BYS
. . importing table                         "TEST"          2 rows imported
Import terminated successfully without warnings.
[oracle@bys001 ~]$ sqlplus bys/bys

BYS@bys1>select * from test;

        ID NAME
---------- --------------------
         1 白
         2 ？？

方法二：
[oracle@bys001 ~]$ export NLS_LANG=AMERICAN_AMERICA.US7ASCII
[oracle@bys001 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.US7ASCII
[oracle@bys001 ~]$ rm -rf test.dmp
[oracle@bys001 ~]$ exp bys/bys file='/home/oracle/test.dmp' tables=test rows=y
Export: Release 11.2.0.1.0 - Production on Sun Aug 11 00:58:27 2013
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                           TEST          2 rows exported
Export terminated successfully without warnings.
[oracle@bys001 ~]$ imp bys/bys file='/home/oracle/test.dmp' full=y ignore=y

Import: Release 11.2.0.1.0 - Production on Sun Aug 11 01:00:36 2013

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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing BYS's objects into BYS
. importing BYS's objects into BYS
. . importing table                         "TEST"          2 rows imported
Import terminated successfully without warnings.

[oracle@bys001 ~]$ sqlplus bys/bys
BYS@bys1>select * from test;

        ID NAME
---------- ----------
         1 ?
         2 ??