字符集
1、char、varchar2、clob，這些型別的列涉及到資料庫字符集
2、nchar、nvarchar2、nclob，這些型別的列涉及到國家字符集（一定是Unicode字符集）
unicode字符集
將來你的字元列上要儲存什麼樣的字元，該怎麼選擇字符集？
比如：
1、要儲存中文：可以使用ZHS16GBK
2、要儲存很多國家的字元：可以使用al32utf8
字符集（一般涉及到三個地方的字符集）
1、資料庫字符集
2、客戶端字符集：
(windows中文客戶端，使用的是中文字符集（活動內碼表：936）；linux：預設utf8字符集）
Windows：chcp
linux：
[root@db11g ~]# cat /etc/sysconfig/i18n
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"
3、客戶端的NLS_LANG設定：
原則：是要設定的和客戶端是同一個字符集，這個引數反應客戶端的字符集情況
NLS_LANG=american_america.zhs16gbk
這個引數設定的時候，一定要從資料庫裡查，要設定的與資料庫的字符集一樣
查詢字符集：
select * from V$NLS_VALID_VALUES where value like '%UTF8%';
linux裡設定NLS_LANG：
編輯環境變數.bash_profile檔案：
[root@db11g ~]# su - oracle
[oracle@db11g ~]$ vi .bash_profile
新增內容：
export NLS_LANG = american_america.utf8
Windows裡設定NLS_LANG：
這樣只對當前視窗有效
永久生效的話：可以設定Windows的環境變數和登錄檔
資料庫字符集
查詢資料庫的字符集：
select * from nls_database_parameters
資料庫字符集：
1、zhs16gbk（存中文）
2、al32utf8（可以存中文）
客戶端os字符集：
zhs16gbk
客戶端NLS_LANG：
zhs16gbk
顯示正確，不一定存的正確：
存資料的時候，客戶端與NLS_LANG的字符集設定的不一樣，取資料的時候，客戶端與NLS_LANG的字符集設定的與存資料時候的一樣，這時候就會出現顯示正確的情況，但實際上儲存錯了
顯示不正確，不一定存的有問題：
儲存資料的時候，客戶端與NLS_LANG的字符集設定為一樣的，但是取的時候，客戶端與NLS_LANG的字符集設定的不一樣，就會出現顯示不正常的情況，但實際上儲存是對的
關鍵是：設定正確的體系後，查詢顯示正確的，才是真正的正確，表示儲存的也正確
儲存的是否正確，這是核心
查詢存的字元資料的真實編碼：
當前會話日期格式、語言等等的顯示只與當前會話的NLS有關，不同的會話有可能顯示是不一樣的：
select * from nls_session_parameters
查詢資料庫例項的字符集、語言：
select * from nls_instance_parameters
如果資料庫級別的字符集和例項級別的字符集不一致，以例項級別的為主；例項級別跟會話級別的不一樣，以會話級別的為主
資料庫的EXP工具（資料庫資料的匯入、匯出）
圖解：
可以將資料庫的資料匯出來，然後匯入到另外一個資料庫裡面去，匯出資料的時候，注意幾個地方：1、資料庫是什麼字符集，然後匯出來以後，是以什麼字符集儲存匯出來的資料（這個可以指定）；2、資料匯入的時候，資料庫會問，要匯入的資料使用的是什麼字符集；
假設資料庫1的字符集是：zhs16gbk，資料庫2的字符集是：al32utf8；這時候匯出來的資料的字符集是zhs16gbk，就不會發生字符集的轉換了，但是匯入的時候，資料的字符集是zhs16gbk的，資料庫的字符集是al32utf8的，然後會發生一次字符集的轉換；這裡，al32utf8是zhs16gbk的超集，原理上是不會有問題的
如果確實需要發生字符集的轉換，前提是：目標資料庫是源資料庫的超集
整個過程只是保證一次字符集轉換即可，或者在匯出的時候轉換，或者在匯入的時候轉換
如果不需要字符集的轉換，那就不要發生字符集轉換
exp（匯出）：
nls_lang = 源資料庫字符集，這時候字符集不轉換
這裡看是否有字符集轉換及使用何種字符集匯出的
匯出使用者u1的資料，指定匯出的字符集為al32utf8，資料庫字符集也是al32utf8：
[oracle@db11g ~]$ export NLS_LANG=american_america.al32utf8
[oracle@db11g ~]$ exp system/oracle@orcl owner=u1 file=al32.dmp
Export: Release 11.2.0.4.0 - Production on Sun Mar 26 13:52:33 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set -- 這裡資料以AL32UTF8匯出的，沒有提示字符集的轉換
About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user U1
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user U1
About to export U1's objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export U1's tables via Conventional Path …
. . exporting table T2 86262 rows exported
. . exporting table T21 1 rows exported
. . exporting table T3 86261 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@db11g ~]$ ll
total 19500
-rw-r--r-- 1 oracle oinstall 19963904 Mar 26 13:36 al32.dmp
匯出使用者u1的資料，指定匯出的字符集為zhs16gbk，資料庫字符集是al32utf8：
[oracle@db11g ~]$ export NLS_LANG=american_america.zhs16gbk
[oracle@db11g ~]$ exp system/oracle@orcl owner=u1 file=zhs16.dmp
Export: Release 11.2.0.4.0 - Production on Sun Mar 26 14:10:42 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set -- 提示資料以16gbk字符集匯出的
server uses AL32UTF8 character set (possible charset conversion) -- 提示伺服器的字符集為AL32UTF8，發生字符集的轉換
About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user U1
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user U1
About to export U1's objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export U1's tables via Conventional Path …
. . exporting table T2 86262 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table T21 1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table T3 86261 rows exported
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.
[oracle@db11g ~]$ ll
total 58492
-rw-r--r-- 1 oracle oinstall 19963904 Mar 26 14:02 al32.dmp
-rw-r--r-- 1 oracle oinstall 19963904 Mar 26 14:10 zhs16.dmp
如何檢視一個檔案是以什麼字符集匯出的：
1、
[oracle@db11g ~]$ cat al32.dmp |od -x|head -1|awk '{print $2 $3}'
03034569
select to_number('0369','xxxx') from dual;
select nls_charset_name(873) from dual;
select nls_charset_id('AL32UTF8') from dual;
2、
[oracle@db11g ~]$ cat zhs16.dmp |od -x|head -1|awk '{print $2 $3}'
03034554
select to_number('0354','xxxx') from dual;
select nls_charset_name(852) from dual;
select nls_charset_id('ZHS16GBK') from dual;
imp（匯入）：
nls_lang = 源資料庫字符集，也不發生字符集的轉換
匯入使用者u1的資料
1、首先得建立使用者u1，並授權（實驗：這裡之前已有使用者u1，刪除u1之後，然後再建立一個新的使用者u1）：
[oracle@db11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 26 17:52:47 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop user u1;
drop user u1
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'U1'
SQL> drop user u1 CASCADE;
User dropped.
SQL> create user u1 identified by u1;
User created.
SQL> grant resource,connect,dba to u1;
Grant succeeded.
2、匯入資料（匯入資料的時候也可以指定字符集）：
匯入使用者u1的資料，指定匯入資料的字符集為zhs16gbk（原則上：指定匯入資料的字符集要與匯出資料的字符集一樣，但也可以指定字符集與目標資料庫的一樣），資料庫的字符集為al32utf8：
[oracle@db11g ~]$ export NLS_LANG=american_america.zhs16gbk
[oracle@db11g ~]$ imp system/oracle@orcl file=zhs16.dmp full=y
Import: Release 11.2.0.4.0 - Production on Sun Mar 26 18:06:44 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit 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 AL16UTF16 NCHAR character set -- 提示資料以16gbk字符集匯入的
import server uses AL32UTF8 character set (possible charset conversion) -- 提示伺服器的字符集為AL32UTF8，發生字符集的轉換
. importing SYSTEM's objects into SYSTEM
. importing U1's objects into U1
. . importing table "T2" 86262 rows imported
. . importing table "T21" 1 rows imported
. . importing table "T3" 86261 rows imported
Import terminated successfully without warnings.
匯入使用者u1的資料，指定匯入資料的字符集為al32utf8（目標資料庫的字符集），資料庫的字符集為al32utf8：
[oracle@db11g ~]$ export NLS_LANG=american_america.al32utf8
[oracle@db11g ~]$ imp system/oracle@orcl file=zhs16.dmp full=y
Import: Release 11.2.0.4.0 - Production on Sun Mar 26 18:14:01 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit 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 AL32UTF8 character set and AL16UTF16 NCHAR character set -- 提示資料以AL32UTF8字符集匯入的
export client uses ZHS16GBK character set (possible charset conversion) -- 提示客戶端以ZHS16GBK字符集匯出的資料，發生字符集轉換
. importing SYSTEM's objects into SYSTEM
. importing U1's objects into U1
. . importing table "T2" 86262 rows imported
. . importing table "T21" 1 rows imported
. . importing table "T3" 86261 rows imported
Import terminated successfully without warnings.
[oracle@db11g ~]$ export NLS_LANG=american_america.al32utf8
[oracle@db11g ~]$ imp system/oracle@orcl file=al32.dmp full=y
Import: Release 11.2.0.4.0 - Production on Sun Mar 26 18:17:25 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit 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 AL32UTF8 character set and AL16UTF16 NCHAR character set -- 只提示以AL32UTF8字符集匯入
. importing SYSTEM's objects into SYSTEM. importing U1's objects into U1
. . importing table "T2" 86262 rows imported
. . importing table "T21" 1 rows imported
. . importing table "T3" 86261 rows imported
Import terminated successfully without warnings.
實驗：驗證是否正確的匯入匯出資料庫資料
1、首先建立了一個使用者u3，一個表t1：
[oracle@db11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 26 18:29:14 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user u3 identified by u3;
User created.
SQL> grant resource,connect,dba to u3;
Grant succeeded.
SQL> connect u3/u3
Connected.
SQL> create table t1(id number,name varchar2(20));
Table created.
2、在客戶端插入資料
查詢當前會話的字符集：
向t1表插入資料：
3、匯出使用者u3的資料：（匯出資料時不發生字符集的轉換）
[oracle@db11g ~]$ export NLS_LANG=american_america.al32utf8
[oracle@db11g ~]$ exp system/oracle@orcl owner=u3 file=al32.dmp
Export: Release 11.2.0.4.0 - Production on Sun Mar 26 18:42:57 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user U3
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user U3
About to export U3's objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export U3's tables via Conventional Path …
. . exporting table T1 1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
4、再匯出使用者u3的資料：（匯出資料時發生字符集的轉換）
[oracle@db11g ~]$ export NLS_LANG=american_america.zhs16gbk
[oracle@db11g ~]$ exp system/oracle@orcl owner=u3 file=zhs16.dmp
Export: Release 11.2.0.4.0 - Production on Sun Mar 26 18:45:08 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character setserver uses AL32UTF8 character set (possible charset conversion)
About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user U3
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user U3
About to export U3's objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export U3's tables via Conventional Path …
. . exporting table T1 1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
[oracle@db11g ~]$ cat al32.dmp |od -x|head -1
0000000 0303 4569 5058 524f 3a54 3156 2e31 3230
[oracle@db11g ~]$ cat zhs16.dmp |od -x|head -1
0000000 0303 4554 5058 524f 3a54 3156 2e31 3230
5、匯入使用者u3的資料：
[oracle@db11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 26 18:50:03 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop user u3;
drop user u3
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'U3'
SQL> drop user u3 CASCADE;
User dropped.
SQL> create user u3 identified by u3;
User created.
SQL> grant resource,connect,dba to u3;
Grant succeeded.
[oracle@db11g ~]$ echo $NLS_LANG
american_america.zhs16gbk
[oracle@db11g ~]$ export NLS_LANG=american_america.al32utf8
[oracle@db11g ~]$ imp system/oracle@orcl file=al32.dmp full=y
Import: Release 11.2.0.4.0 - Production on Sun Mar 26 18:54:31 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit 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 AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SYSTEM's objects into SYSTEM
. importing U3's objects into U3
. . importing table "T1" 1 rows imported
Import terminated successfully without warnings.
[oracle@db11g ~]$ echo $NLS_LANG
american_america.al32utf8
[oracle@db11g ~]$ export NLS_LANG=american_america.zhs16gbk
[oracle@db11g ~]$ imp system/oracle@orcl file=zhs16.dmp full=y
Import: Release 11.2.0.4.0 - Production on Sun Mar 26 18:57:24 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit 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 AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
. importing U3's objects into U3
. . importing table "T1" 1 rows imported
Import terminated successfully without warnings.
[oracle@db11g ~]$ export NLS_LANG=american_america.al32utf8
[oracle@db11g ~]$ imp system/oracle@orcl file=zhs16.dmp full=y
Import: Release 11.2.0.4.0 - Production on Sun Mar 26 19:05:49 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit 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 AL32UTF8 character set and AL16UTF16 NCHAR character set
export client uses ZHS16GBK character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
. importing U3's objects into U3
. . importing table "T1" 1 rows imported
Import terminated successfully without warnings.
6、客戶端查詢（無論設定NLS_LANG為資料的字符集，還是目標資料庫的字符集，資料都能正確的匯入到資料庫裡）：
