不同字符集数据库导数据

--------------------------------------------------------------------------
-----------------------------Cryking原创------------------------------
-----------------------转载请注明出处,谢谢!------------------------ 

 

字符集相关环境:

数据库字符集、客户端字符集、查询环境字符集

 

这里主要是指使用EXPDP或EXP导出,然后使用IMPDP或IMP导入.

一般来说如果一个字符集是另一个字符集的超集的时候,可以成功的导出导入,我这里主要测试不能自动发生转换的,即不是超集关系时的问题。

 

测试环境:

数据库版本都是 Oracle Database 11g Release 11.2.0.1.0 - Production

导出的库的字符集是:AMERICAN_AMERICA.ZHS16GBK

导出时的客户端字符集是:AMERICAN_AMERICA.ZHS16GBK

 

导入的库的字符集是:AMERICAN_AMERICA.WE8MSWIN1252

 

由于WE8MSWIN1252是西欧字符集,不支持中文.我开始不清楚这些字符集之间的关系,就直接设置导入客户端的字符集为AMERICAN_AMERICA.ZHS16GBK后进行导入操作。

[oracle@oracleserver ~]$ export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"            
[oracle@oracleserver ~]$ impdp cry/cry directory=LOG_FILE_DIR DUMPFILE=CRY20130527.DMP TABLES=CHINESE_LANGUAGE

Import: Release 11.2.0.1.0 - Production on Thu Jan 3 11:41:44 2013

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

Connected to: Oracle Database 11g Release 11.2.0.1.0 - Production
Master table "CRY"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "CRY"."SYS_IMPORT_TABLE_01":  cry/******** directory=LOG_FILE_DIR DUMPFILE=CRY20130527.DMP TABLES=CHINESE_LANGUAGE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "CRY"."CHINESE_LANGUAGE"                    9.757 KB       5 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "CRY"."SYS_IMPORT_TABLE_01" successfully completed at 11:41:52

导入还是成功的,然后将查询端的NLS_LANG设置为AMERICAN_AMERICA.ZHS16GBK

进行查询,发现出现乱码.

SQL> select SENTENCE from chinese_language where rownum=1;
 
SENTENCE
--------------------------------------------------------------------------------
靠靠?靠靠?靠靠?靠靠?

 

经过先后三次尝试:

尝试1
修改导入的客户端字符集为:ZHS16GBK
查询客户端字符集为:ZHS16GBK
查询数据乱码

尝试2
修改导入的客户端字符集为:WE8MSWIN1252
查询客户端字符集为:ZHS16GBK
查询数据还是乱码

 尝试3
修改导入的客户端字符集为:WE8MSWIN1252
查询客户端字符集为:WE8MSWIN1252
查询数据还是乱码

 

最后通过下面的命令(更改数据库字符集命令),发现

alter database character set ZHS16GBK
 
ORA-12712: new character set must be a superset of old character set

ZHS16GBK不是WE8MSWIN1252的超集。

难道真的只能重新建库了吗？

死马当活马医吧。(尝试修改数据库字符集)

12:28:34 SYS@cry> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
12:28:48 SYS@cry> startup restrict
ORACLE instance started.

Total System Global Area  435224576 bytes
Fixed Size                  1337044 bytes
Variable Size             297797932 bytes
Database Buffers          130023424 bytes
Redo Buffers                6066176 bytes
Database mounted.
Database opened.
12:29:25 SYS@cry> alter database character set INTERNAL_USE ZHS16GBK;

Database altered.

Elapsed: 00:00:17.88
12:30:07 SYS@cry> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
12:31:24 SYS@cry> startup
ORACLE instance started.

Total System Global Area  435224576 bytes
Fixed Size                  1337044 bytes
Variable Size             297797932 bytes
Database Buffers          130023424 bytes
Redo Buffers                6066176 bytes
Database mounted.
Database opened.
12:34:13 SYS@cry> exit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - Production
[oracle@oracleserver ~]$ export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"            
[oracle@oracleserver ~]$ impdp cry/cry directory=LOG_FILE_DIR DUMPFILE=CRY20130527.DMP TABLES=CHINESE_LANGUAGE

Import: Release 11.2.0.1.0 - Production on Thu Jan 3 12:35:46 2013

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

Connected to: Oracle Database 11g Release 11.2.0.1.0 - Production
ORA-39006: internal error
ORA-39213: Metadata processing is not available

[oracle@oracleserver ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 3 12:38:21 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

12:38:21 @> conn / as sysdba
Connected.
12:38:27 SYS@cry> execute sys.dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.07
12:38:32 SYS@cry> exit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - Production
[oracle@oracleserver ~]$ export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"            
[oracle@oracleserver ~]$ impdp cry/cry directory=LOG_FILE_DIR DUMPFILE=CRY20130527.DMP TABLES=CHINESE_LANGUAGE

Import: Release 11.2.0.1.0 - Production on Thu Jan 3 12:38:48 2013

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

Connected to: Oracle Database 11g Release 11.2.0.1.0 - Production
Master table "CRY"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "CRY"."SYS_IMPORT_TABLE_01":  cry/******** directory=LOG_FILE_DIR DUMPFILE=CRY20130527.DMP TABLES=CHINESE_LANGUAGE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "CRY"."CHINESE_LANGUAGE"                    9.757 KB       5 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "CRY"."SYS_IMPORT_TABLE_01" successfully completed at 12:38:56

注意上面在修改字符集之后,再次IMPDP导入时候,报了一个ORA-39213及ORA-39006错误,通过执行sys.dbms_metadata_util.load_stylesheets后解决.
 导入成功之后,查询字符集:

SQL> select userenv('language') from dual;
 
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

发现已经更改为ZHS16GBK的字符集了.

在查询端查询数据,发现 终于不乱码了

 

 

SQL> select * from chinese_language where rownum=1;
 
        ID CLASSIFY             SENTENCE                                                                         WRITER               LIKEFLAG TITLE                                                                            ADDDATE
---------- -------------------- -------------------------------------------------------------------------------- -------------------- -------- -------------------------------------------------------------------------------- -----------
         5 诗词                 功盖三分国,名成八阵图.江流石不转,遗恨失吞吴. 

 

注:以上修改数据库字符集的操作,盖大师有警言"使用alter database character set INTERNAL_USE ZHS16GBK这个命令时，Oracle会跳过所有子集及超集的检查，在任意字符集之间进行强制转换，所以，使用这个命令时你必须十分小心，你必须清楚这一操作会带来的风险"。