含有漢字的固定字元由ZHS16GBK資料庫匯入到AL32UTF8的資料庫

導讀：
此文章是對於上一個實驗的補充，上一次實驗僅僅考慮的 varchar2 的情況。這次考慮到對於char型別的含有中文資料的情況。
對於英文：
對英文，在al32utf8中仍然和zhs16gbk一樣用1個位元組表示，因此匯入固定長度英文字元資料時不會出錯。
對於中文：
例如在字符集為zhs16gbk 資料庫中建立表時指定欄位 val char（15），該欄位含有資料 ‘阿里雲端計算公司’在字符集為zhs16gbk 資料庫中佔用14個字

節，而在字符集為al32utf8 資料庫中佔用21個位元組 大於 char（15）所指定的長度15.此時匯入資料就會失敗。
下面對於上述情況給以說明。
=======================遷移 英文固定長度字元資料====================
=======================建立表，含有英文固定長度字元資料=============
yang@ORACL> create table fixed_char ( val char(15) );
表已建立。
yang@ORACL> insert into fixed_char select
  2  dbms_random.string('l',15) from dual
  3  connect by level <125;
已建立124行。
yang@ORACL> select val from fixed_char;
VAL
---------------
oxmjgwgzbjhthcr
elbdlcpkfajsmrc
jrwccslaywxpiwj
...............
ljkowkocmdqnkgj
idialvaxohrahah
zwrqynvtevfujao
funkxaokotsblww
snyetpafaneicjm
kgrcrpbwlvtotcv
knhcazjkgotzvmg
myqgvjqnsingmxv
klthqehltsyzrxe
voucbpykpnsbopx
vtvavjddyafwqxt
omcnkpvlhlxdvvg
ccfpttivbdvursz
已選擇124行。
yang@ORACL> spool d:\fixed_char.txt
yang@ORACL> insert into fixed_char select
  2  dbms_random.string('l',15) from dual
  3  connect by level <125;
已建立124行。
yang@ORACL> select val from fixed_char where rownum <10;
VAL
---------------
oxmjgwgzbjhthcr
elbdlcpkfajsmrc
jrwccslaywxpiwj
clizpikggppgfwy
yqqxljlscqaiqli
lrwoayaxyjzgdhy
rkqpujyupltmrqb
qvycepfgtwipwat
iccsgvrpfxwligq
已選擇9行。
yang@ORACL> commit;
提交完成。
=========================匯出資料================================
C:\Users\aaaa>expdp yang/yang tables=fixed_char directory=dumpdir dumpfile=fixed_char_zhs16gbk.dmp l
ogfile=fixed_char.log
Export: Release 11.1.0.6.0 - Production on 星期六, 12 3月, 2011 12:25:55
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
連線到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
啟動 "YANG"."SYS_EXPORT_TABLE_01":  yang/******** tables=fixed_char directory=dumpdir dumpfile=fixed
_char_zhs16gbk.dmp logfile=fixed_char.log
正在使用 BLOCKS 方法進行估計...
處理物件型別 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的總估計: 64 KB
處理物件型別 TABLE_EXPORT/TABLE/TABLE
. . 匯出了 "YANG"."FIXED_CHAR"                         9.851 KB     248 行
已成功載入/解除安裝了主表 "YANG"."SYS_EXPORT_TABLE_01"
******************************************************************************
YANG.SYS_EXPORT_TABLE_01 的轉儲檔案集為:
  D:\DUMP\FIXED_CHAR_ZHS16GBK.DMP
作業 "YANG"."SYS_EXPORT_TABLE_01" 已於 12:27:32 成功完成
=====================================匯入資料=============================================
oracle@rac1:rac1 /tmp/dump>impdp yang/yang tables=fixed_char directory=dumpdir dumpfile=fixed_char_zhs16gbk.dmp

log=fixedchar_zhs16gbk_to_al32utf8.log
Import: Release 11.2.0.1.0 - Production on Sat Mar 12 12:31:34 2011
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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=fixedchar_zhs16gbk_to_al32utf8.log" Location: Command Line, Replaced with:

"logfile=fixedchar_zhs16gbk_to_al32utf8.log"
Master table "YANG"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "YANG"."SYS_IMPORT_TABLE_01":  yang/******** tables=fixed_char directory=dumpdir dumpfile=fixed_char_zhs16gbk.dmp

logfile=fixedchar_zhs16gbk_to_al32utf8.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "YANG"."FIXED_CHAR"                         9.851 KB     248 rows
Job "YANG"."SYS_IMPORT_TABLE_01" successfully completed at 12:31:38
==================================在目標庫裡驗證資料====================================
yang@rac1>select table_name from user_tables;
TABLE_NAME
------------------------------
CHARSET
CHART
TCHAR
FIXED_CHAR
yang@rac1>desc fixed_char
Name                     Null?    Type
----------------------- -------- -------
VAL                              CHAR(15)
yang@rac1>select val from fixed_char where rownum <12;
VAL
---------------
oxmjgwgzbjhthcr
elbdlcpkfajsmrc
jrwccslaywxpiwj
clizpikggppgfwy
yqqxljlscqaiqli
lrwoayaxyjzgdhy
rkqpujyupltmrqb
qvycepfgtwipwat
iccsgvrpfxwligq
zrtwmeviosnsgmv
chwsezhjfgnsjll
11 rows selected.
yang@rac1>select dump(val) from fixed_char where rownum <10;
DUMP(VAL)
--------------------------------------------------------------------------
Typ=96 Len=15: 111,120,109,106,103,119,103,122,98,106,104,116,104,99,114
Typ=96 Len=15: 101,108,98,100,108,99,112,107,102,97,106,115,109,114,99
Typ=96 Len=15: 106,114,119,99,99,115,108,97,121,119,120,112,105,119,106
Typ=96 Len=15: 99,108,105,122,112,105,107,103,103,112,112,103,102,119,121
Typ=96 Len=15: 121,113,113,120,108,106,108,115,99,113,97,105,113,108,105
Typ=96 Len=15: 108,114,119,111,97,121,97,120,121,106,122,103,100,104,121
Typ=96 Len=15: 114,107,113,112,117,106,121,117,112,108,116,109,114,113,98
Typ=96 Len=15: 113,118,121,99,101,112,102,103,116,119,105,112,119,97,116
Typ=96 Len=15: 105,99,99,115,103,118,114,112,102,120,119,108,105,103,113
9 rows selected.
對於英文字元可以實現由zhs16gbk 到 al32utf8的轉換。
解釋：用UTF-8，UNICODE的2位元組字元用變長個（1－3個位元組）表示：
1. 對英文，仍然和ASCII一樣用1個位元組表示，這個位元組的值小於128(\x80)；
2. 擴充套件的ASCII字元（主要是西歐），第一位元組用C2 - DF之間的範圍，雙位元組表示。
3.對其他語言，比如亞洲語系，還有各種特殊符號，使用3個位元組表示；
因此，在應用中程式處理過程中所有字元都是16位（雙位元組），但在存取轉換成位元組流時使用UTF-8格式轉換，對於英文字元來說和原來用ASCII方式存取

時相比大小仍然是一樣的，而對中文來說和原來的GB2312編碼方式相比，大小為：(3位元組/2位元組)=1.5倍，這也是下面匯入資料失敗的原因。
==================================遷移含有漢字 固定字元資料=============================
=====================建立表，含有漢字的固定字元資料======================================
yang@ORACL> create table fixed( val1 char(15),val2 char(15) );
表已建立。
yang@ORACL> insert into fixed select
  2  dbms_random.string('l',15) val1,
  3  '阿里雲算公司' as val2
  4  from dual
  5  connect by level <500;
已建立499行。
yang@ORACL> select dump('阿里雲端計算公司') from dual;
DUMP('阿里雲端計算公司')
----------------------------------------------------------------------
Typ=96 Len=14: 176,162,192,239,212,198,188,198,203,227,185,171,203,190
yang@ORACL> commit;
提交完成。
yang@ORACL> select count(*) from fixed;
  COUNT(*)
----------
       499
yang@ORACL>
========================匯出操作===========================================
C:\Users\aaaa>expdp yang/yang tables=fixed directory=dumpdir dumpfile=fixed_zhs16gbk.dmp logfile=fixed.log
Export: Release 11.1.0.6.0 - Production on 星期六, 12 3月, 2011 12:50:05
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
連線到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
啟動 "YANG"."SYS_EXPORT_TABLE_01":  yang/******** tables=fixed directory=dumpdir dumpfile=fixed_zhs1
6gbk.dmp logfile=fixed.log
正在使用 BLOCKS 方法進行估計...
處理物件型別 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的總估計: 64 KB
處理物件型別 TABLE_EXPORT/TABLE/TABLE
. . 匯出了 "YANG"."FIXED"                              22.94 KB     499 行
已成功載入/解除安裝了主表 "YANG"."SYS_EXPORT_TABLE_01"
******************************************************************************
YANG.SYS_EXPORT_TABLE_01 的轉儲檔案集為:
  D:\DUMP\FIXED_ZHS16GBK.DMP
作業 "YANG"."SYS_EXPORT_TABLE_01" 已於 12:50:53 成功完成
C:\Users\aaaa>
========================匯入操作=====================================================================
oracle@rac1:rac1 /tmp/dump>impdp yang/yang tables=fixed directory=dumpdir dumpfile=fixed_zhs16gbk.dmp

log=fixed_zhs16gbk_to_al32utf8.log
Import: Release 11.2.0.1.0 - Production on Sat Mar 12 12:52:38 2011
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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=fixed_zhs16gbk_to_al32utf8.log" Location: Command Line, Replaced with:

"logfile=fixed_zhs16gbk_to_al32utf8.log"
Master table "YANG"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "YANG"."SYS_IMPORT_TABLE_01":  yang/******** tables=fixed directory=dumpdir dumpfile=fixed_zhs16gbk.dmp

logfile=fixed_zhs16gbk_to_al32utf8.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-02374: conversion error loading table "YANG"."FIXED"
ORA-12899: value too large for column VAL2 (actual: 21, maximum: 15)===> 需要21個位元組但是 char(15）規定val2欄位長度是15
ORA-02372: data for row: VAL2 : 0X'B0A2C0EFD4C6CBE3B9ABCBBE202020'
ORA-02374: conversion error loading table "YANG"."FIXED"
ORA-12899: value too large for column VAL2 (actual: 21, maximum: 15)
ORA-02372: data for row: VAL2 : 0X'B0A2C0EFD4C6CBE3B9ABCBBE202020'
ORA-02374: conversion error loading table "YANG"."FIXED"
ORA-12899: value too large for column VAL2 (actual: 21, maximum: 15)
ORA-02372: data for row: VAL2 : 0X'B0A2C0EFD4C6CBE3B9ABCBBE202020'
ORA-02374: conversion error loading table "YANG"."FIXED"
ORA-12899: value too large for column VAL2 (actual: 21, maximum: 15)
ORA-02372: data for row: VAL2 : 0X'B0A2C0EFD4C6CBE3B9ABCBBE202020'
ORA-02374: conversion error loading table "YANG"."FIXED"
ORA-12899: value too large for column VAL2 (actual: 21, maximum: 15)