38、字符集_2（导出导入指定字符集）
字符集
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为数据的字符集，还是目标数据库的字符集，数据都能正确的导入到数据库里）：
