Daily Archives: 2011 年 12 月 21 日

通过ZHS16GBK和AL32UTF8字符编码分析exp/imp

Posted on by

一、试验环境和试验准备工作
1.源端

[oracle@node1 ~]$ echo $LANG
zh_CN.gb2312
[oracle@node1 ~]$ sqlplus hr/xifenfei

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Dec 21 14:07:24 2011

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT userenv('language') FROM dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK

SQL> create table xifenfei (name varchar2(6));

Table created.

SQL> insert into xifenfei values('惜分飞');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xifenfei;

NAME
------
惜分飞
惜分飞
惜分飞
惜分飞

2.目标端

[oracle@node1 ~]$ echo $LANG
zh_CN.gb2312
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 15:26:18 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> SELECT userenv('language') FROM dual;

USERENV('LANGUAGE')
----------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

二、导出测试表(xifenfei)
1.ZHS16GBK编码

[oracle@node1 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ exp hr/xifenfei file=/tmp/xifenfei.dmp \
log=/tmp/xifenfei.log tables=xifenfei

Export: Release 10.2.0.5.0 - Production on Wed Dec 21 14:07:11 2011

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.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

About to export specified tables via Conventional Path ...
. . exporting table                       XIFENFEI          4 rows exported
Export terminated successfully without warnings.

2.AL32UTF8编码

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@node1 ~]$ exp hr/xifenfei file=/tmp/xifenfei_new.dmp \
> log=/tmp/xifenfei_new.log tables=xifenfei

Export: Release 10.2.0.5.0 - Production on Wed Dec 21 14:38:14 2011

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.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
server uses ZHS16GBK character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                       XIFENFEI          4 rows exported
Export terminated successfully without warnings.

3.dmp文件比较
3.1)文件大小比较

[oracle@node1 ~]$ cd /tmp
[oracle@node1 tmp]$ ll *.dmp
-rw-r--r-- 1 oracle oinstall 16384 12-21 14:07 xifenfei.dmp
-rw-r--r-- 1 oracle oinstall 16384 12-21 14:38 xifenfei_new.dmp
虽然导出客户端设置了不步的编码，但是导出的文件大小相等

3.2)文件头部比较

--xifenfei.dmp
00000000h: 03 03 54 45 58 50 4F 52 54 3A 56 31 30 2E 30 32 ; ..TEXPORT:V10.02
00000010h: 2E 30 31 0A 44 48 52 0A 52 54 41 42 4C 45 53 0A ; .01.DHR.RTABLES.
00000020h: 38 31 39 32 0A 30 0A 33 32 0A 30 0A 03 54 03 54 ; 8192.0.32.0..T.T
00000030h: 07 D0 00 01 00 00 00 00 00 00 00 00 00 11 00 20 ; .?............ 
00000040h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;                 
00000050h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;                 
00000060h: 20 20 20 20 20 20 20 20 20 57 65 64 20 44 65 63 ;          Wed Dec
00000070h: 20 32 31 20 31 34 3A 37 3A 31 32 20 32 30 31 31 ;  21 14:7:12 2011
00000080h: 2F 74 6D 70 2F 78 69 66 65 6E 66 65 69 2E 64 6D ; /tmp/xifenfei.dm
00000090h: 70 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; p...............

--xifenfei_new.dmp
00000000h: 03 03 69 45 58 50 4F 52 54 3A 56 31 30 2E 30 32 ; ..iEXPORT:V10.02
00000010h: 2E 30 31 0A 44 48 52 0A 52 54 41 42 4C 45 53 0A ; .01.DHR.RTABLES.
00000020h: 38 31 39 32 0A 30 0A 33 32 0A 30 0A 03 69 03 54 ; 8192.0.32.0..i.T
00000030h: 07 D0 00 01 00 00 00 00 00 00 00 00 00 15 00 20 ; .?............ 
00000040h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;                 
00000050h: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 ;                 
00000060h: 20 20 20 20 20 20 20 20 57 65 64 20 44 65 63 20 ;         Wed Dec 
00000070h: 32 31 20 31 34 3A 33 38 3A 31 35 20 32 30 31 31 ; 21 14:38:15 2011
00000080h: 2F 74 6D 70 2F 78 69 66 65 6E 66 65 69 5F 6E 65 ; /tmp/xifenfei_ne
00000090h: 77 2E 64 6D 70 00 00 00 00 00 00 00 00 00 00 00 ; w.dmp...........

--ZHS16GBK dmp 文件 第2,3字节为 0354
--AL32UTF8 dmp 文件 第2,3字节为 0369

3.3)比较dmp文件内容

--xifenfei.dmp
000020ceh: 2B 30 30 3A 30 30 00 00 04 00 42 59 54 45 06 00 ; +00:00....BYTE..
000020deh: 55 4E 55 53 45 44 01 00 32 0B 00 49 4E 54 45 52 ; UNUSED..2..INTER
000020eeh: 50 52 45 54 45 44 0B 00 44 49 53 41 42 4C 45 3A ; PRETED..DISABLE:
000020feh: 41 4C 4C 00 00 0A 4D 45 54 52 49 43 53 54 0A 54 ; ALL...METRICST.T
0000210eh: 41 42 4C 45 20 22 58 49 46 45 4E 46 45 49 22 0A ; ABLE "XIFENFEI".
0000211eh: 43 52 45 41 54 45 20 54 41 42 4C 45 20 22 58 49 ; CREATE TABLE "XI
0000212eh: 46 45 4E 46 45 49 22 20 28 22 4E 41 4D 45 22 20 ; FENFEI" ("NAME" 
0000213eh: 56 41 52 43 48 41 52 32 28 36 29 29 20 20 50 43 ; VARCHAR2(6))  PC
0000214eh: 54 46 52 45 45 20 31 30 20 50 43 54 55 53 45 44 ; TFREE 10 PCTUSED
0000215eh: 20 34 30 20 49 4E 49 54 52 41 4E 53 20 31 20 4D ;  40 INITRANS 1 M
0000216eh: 41 58 54 52 41 4E 53 20 32 35 35 20 53 54 4F 52 ; AXTRANS 255 STOR
0000217eh: 41 47 45 28 49 4E 49 54 49 41 4C 20 36 35 35 33 ; AGE(INITIAL 6553
0000218eh: 36 20 4E 45 58 54 20 31 30 34 38 35 37 36 20 46 ; 6 NEXT 1048576 F
0000219eh: 52 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C ; REELISTS 1 FREEL
000021aeh: 49 53 54 20 47 52 4F 55 50 53 20 31 20 42 55 46 ; IST GROUPS 1 BUF
000021beh: 46 45 52 5F 50 4F 4F 4C 20 44 45 46 41 55 4C 54 ; FER_POOL DEFAULT
000021ceh: 29 20 54 41 42 4C 45 53 50 41 43 45 20 22 55 53 ; ) TABLESPACE "US
000021deh: 45 52 53 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 ; ERS" LOGGING NOC
000021eeh: 4F 4D 50 52 45 53 53 0A 49 4E 53 45 52 54 20 49 ; OMPRESS.INSERT I
000021feh: 4E 54 4F 20 22 58 49 46 45 4E 46 45 49 22 20 28 ; NTO "XIFENFEI" (
0000220eh: 22 4E 41 4D 45 22 29 20 56 41 4C 55 45 53 20 28 ; "NAME") VALUES (
0000221eh: 3A 31 29 0A 01 00 01 00 06 00 54 03 01 00 00 00 ; :1).......T.....
0000222eh: 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 06 00 CF A7 ; ....惜分飞....惜
0000223eh: B7 D6 B7 C9 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 ; 分飞....惜分飞..
0000224eh: 06 00 CF A7 B7 D6 B7 C9 00 00 FF FF 0A 4D 45 54 ; ..惜分飞...MET
0000225eh: 52 49 43 53 54 72 65 66 65 72 65 6E 74 69 61 6C ; RICSTreferential
0000226eh: 20 69 6E 74 65 67 72 69 74 79 20 63 6F 6E 73 74 ;  integrity const
0000227eh: 72 61 69 6E 74 73 0A 4D 45 54 52 49 43 45 54 20 ; raints.METRICET 
0000228eh: 34 0A 4D 45 54 52 49 43 53 54 74 72 69 67 67 65 ; 4.METRICSTtrigge
0000229eh: 72 73 0A 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 ; rs.METRICET 4.ME
000022aeh: 54 52 49 43 53 54 62 69 74 6D 61 70 2C 20 66 75 ; TRICSTbitmap, fu
000022beh: 6E 63 74 69 6F 6E 61 6C 20 61 6E 64 20 65 78 74 ; nctional and ext
000022ceh: 65 6E 73 69 62 6C 65 20 69 6E 64 65 78 65 73 0A ; ensible indexes.
000022deh: 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 54 52 49 ; METRICET 4.METRI
000022eeh: 43 53 54 70 6F 73 74 74 61 62 6C 65 73 20 61 63 ; CSTposttables ac
000022feh: 74 69 6F 6E 73 0A 4D 45 54 52 49 43 45 54 20 34 ; tions.METRICET 4
0000230eh: 0A 4D 45 54 52 49 43 53 54 50 6F 73 74 2D 69 6E ; .METRICSTPost-in
0000231eh: 73 74 20 70 72 6F 63 65 64 75 72 61 6C 20 61 63 ; st procedural ac
0000232eh: 74 69 6F 6E 73 20 0A 4D 45 54 52 49 43 45 54 20 ; tions .METRICET 
0000233eh: 34 0A 4D 45 54 52 49 43 45 54 47 30 0A 45 58 49 ; 4.METRICETG0.EXI
0000234eh: 54 0A 45 58 49 54 0A                            ; T.EXIT.

--xifenfei_new.dmp
000020ceh: 2B 30 30 3A 30 30 00 00 04 00 42 59 54 45 06 00 ; +00:00....BYTE..
000020deh: 55 4E 55 53 45 44 01 00 32 0B 00 49 4E 54 45 52 ; UNUSED..2..INTER
000020eeh: 50 52 45 54 45 44 0B 00 44 49 53 41 42 4C 45 3A ; PRETED..DISABLE:
000020feh: 41 4C 4C 00 00 0A 4D 45 54 52 49 43 53 54 0A 54 ; ALL...METRICST.T
0000210eh: 41 42 4C 45 20 22 58 49 46 45 4E 46 45 49 22 0A ; ABLE "XIFENFEI".
0000211eh: 43 52 45 41 54 45 20 54 41 42 4C 45 20 22 58 49 ; CREATE TABLE "XI
0000212eh: 46 45 4E 46 45 49 22 20 28 22 4E 41 4D 45 22 20 ; FENFEI" ("NAME" 
0000213eh: 56 41 52 43 48 41 52 32 28 36 29 29 20 20 50 43 ; VARCHAR2(6))  PC
0000214eh: 54 46 52 45 45 20 31 30 20 50 43 54 55 53 45 44 ; TFREE 10 PCTUSED
0000215eh: 20 34 30 20 49 4E 49 54 52 41 4E 53 20 31 20 4D ;  40 INITRANS 1 M
0000216eh: 41 58 54 52 41 4E 53 20 32 35 35 20 53 54 4F 52 ; AXTRANS 255 STOR
0000217eh: 41 47 45 28 49 4E 49 54 49 41 4C 20 36 35 35 33 ; AGE(INITIAL 6553
0000218eh: 36 20 4E 45 58 54 20 31 30 34 38 35 37 36 20 46 ; 6 NEXT 1048576 F
0000219eh: 52 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C ; REELISTS 1 FREEL
000021aeh: 49 53 54 20 47 52 4F 55 50 53 20 31 20 42 55 46 ; IST GROUPS 1 BUF
000021beh: 46 45 52 5F 50 4F 4F 4C 20 44 45 46 41 55 4C 54 ; FER_POOL DEFAULT
000021ceh: 29 20 54 41 42 4C 45 53 50 41 43 45 20 22 55 53 ; ) TABLESPACE "US
000021deh: 45 52 53 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 ; ERS" LOGGING NOC
000021eeh: 4F 4D 50 52 45 53 53 0A 49 4E 53 45 52 54 20 49 ; OMPRESS.INSERT I
000021feh: 4E 54 4F 20 22 58 49 46 45 4E 46 45 49 22 20 28 ; NTO "XIFENFEI" (
0000220eh: 22 4E 41 4D 45 22 29 20 56 41 4C 55 45 53 20 28 ; "NAME") VALUES (
0000221eh: 3A 31 29 0A 01 00 01 00 06 00 54 03 01 00 00 00 ; :1).......T.....
0000222eh: 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 06 00 CF A7 ; ....惜分飞....惜
0000223eh: B7 D6 B7 C9 00 00 06 00 CF A7 B7 D6 B7 C9 00 00 ; 分飞....惜分飞..
0000224eh: 06 00 CF A7 B7 D6 B7 C9 00 00 FF FF 0A 4D 45 54 ; ..惜分飞...MET
0000225eh: 52 49 43 53 54 72 65 66 65 72 65 6E 74 69 61 6C ; RICSTreferential
0000226eh: 20 69 6E 74 65 67 72 69 74 79 20 63 6F 6E 73 74 ;  integrity const
0000227eh: 72 61 69 6E 74 73 0A 4D 45 54 52 49 43 45 54 20 ; raints.METRICET 
0000228eh: 34 0A 4D 45 54 52 49 43 53 54 74 72 69 67 67 65 ; 4.METRICSTtrigge
0000229eh: 72 73 0A 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 ; rs.METRICET 4.ME
000022aeh: 54 52 49 43 53 54 62 69 74 6D 61 70 2C 20 66 75 ; TRICSTbitmap, fu
000022beh: 6E 63 74 69 6F 6E 61 6C 20 61 6E 64 20 65 78 74 ; nctional and ext
000022ceh: 65 6E 73 69 62 6C 65 20 69 6E 64 65 78 65 73 0A ; ensible indexes.
000022deh: 4D 45 54 52 49 43 45 54 20 34 0A 4D 45 54 52 49 ; METRICET 4.METRI
000022eeh: 43 53 54 70 6F 73 74 74 61 62 6C 65 73 20 61 63 ; CSTposttables ac
000022feh: 74 69 6F 6E 73 0A 4D 45 54 52 49 43 45 54 20 34 ; tions.METRICET 4
0000230eh: 0A 4D 45 54 52 49 43 53 54 50 6F 73 74 2D 69 6E ; .METRICSTPost-in
0000231eh: 73 74 20 70 72 6F 63 65 64 75 72 61 6C 20 61 63 ; st procedural ac
0000232eh: 74 69 6F 6E 73 20 0A 4D 45 54 52 49 43 45 54 20 ; tions .METRICET 
0000233eh: 34 0A 4D 45 54 52 49 43 45 54 47 30 0A 45 58 49 ; 4.METRICETG0.EXI
0000234eh: 54 0A 45 58 49 54 0A                            ; T.EXIT.
--通过比较知道，不同客户端编码，导出的数据内容相同，
--都是一个汉字对应两个字节(说明是按照服务端编码导出)

三、导入测试表(xifenfei)
1.导入xifenfei.dmp(ZHS16GBK编码导出)
1.1)客户端编码设置ZHS16GBK

[oracle@node1 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \
 log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test

Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:16:27 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HR, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞          0 rows imported
Import terminated successfully with warnings.

1.2)客户端编码设置为AL32UTF8

[oracle@node1 ~]$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \
log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test

Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:21:41 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HR, not by you

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export client uses ZHS16GBK character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋         0 rows imported
Import terminated successfully with warnings.

2.导入xifenfei_new.dmp(AL32UTF8编码导出)
2.1)客户端编码设置ZHS16GBK

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei_new.dmp \
> log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test

Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:39:53 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HR, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses AL32UTF8 character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 惜分飞          0 rows imported
Import terminated successfully with warnings.

2.2)客户端编码设置为AL32UTF8

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei_new.dmp \
> log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test

Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:41:12 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HR, not by you

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋
IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "TEST"."XIFENFEI"."NAME" (actual: 9, maximum: 6)
Column 1 鎯滃垎椋         0 rows imported
Import terminated successfully with warnings.

四、修改列长度，导入成功
无论源端客户端使用何种编码导出，目标端使用何种编码导入(仅限我这里说的AL32UTF8和ZHS16GBK),如果客户端编码是ZHS16GBK，验证数据的时候，可以省略掉设置编码的过程。

--导入报错后，登录数据库，修改列长度，因为目标端数据库编码是AL32UTF8，
--1个汉字占用3个字节修改列的程度满足错误提示的最大程度。
SQL> alter table xifenfei modify name varchar2(9);

Table altered.

[oracle@node1 ~]$ imp test/xifenfei file=/tmp/xifenfei.dmp \
log=/tmp/xifenfei_in.log tables=xifenfei fromuser=hr touser=test IGNORE=y

Import: Release 11.2.0.3.0 - Production on Wed Dec 21 14:25:07 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing option

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HR, not by you

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export client uses ZHS16GBK character set (possible charset conversion)
. importing HR's objects into TEST
. . importing table                     "XIFENFEI"          4 rows imported
Import terminated successfully without warnings.

--因为目标端客户端编码为AL32UTF8，而linux展示平台编码为gbk，所以出现乱码
--修改客户端编码后，查询乱码问题消失
[oracle@node1 ~]$ sqlplus test/xifenfei

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 14:25:14 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> select * from xifenfei;

NAME
---------
鎯滃垎椋
鎯滃垎椋
鎯滃垎椋
鎯滃垎椋

SQL> exit

[oracle@node1 ~]$ export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@node1 ~]$ sqlplus test/xifenfei

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 14:25:52 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> select * from xifenfei;

NAME
------------------
惜分飞
惜分飞
惜分飞
惜分飞

五、试验总结
1.当源端字符编码为ZHS16GBK，目标端编码为AL32UTF8，客户端随便为其中的一种编码，迁移数据不会出现乱码，但是会出现列长度不够现象。反过来不行，因为utf8中的部分字符转换到gbk中肯定会不支持
2.设置了源端客户端编码，仅仅是导出来的dmp文件头部有编码字符标示不一样，存储数据还是按照服务端存储
3.打破神话，exp/imp导入要不乱码，导出和导入的客户端编码要一致

修改oracle数据库字符集

Posted on by

现在有个需求，需要数据库字符集从ZHS16GBK修改为AL32UTF8，因为他们没有子集的关系，所以在转换前，需要先检测库中的数据内容是否全库可以转换为AL32UTF8字符集，检测使用oracle提供的csscan工具实现
一、csscan使用
1.安装csscan相关数据字典

SQL> @?/rdbms/admin/csminst.sql

2.csscan使用说明

[oracle@node1 ~]$ csscan help=y


Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Wed Dec 21 12:30:42 2011

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


You can let Scanner prompt you for parameters by entering the CSSCAN        
command followed by your username/password:                                 
                                                                            
  Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\"                          
                                                                            
Or, you can control how Scanner runs by entering the CSSCAN command         
followed by various parameters. To specify parameters, you use keywords:    
                                                                            
  Example:                                                                  
    CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3  
                                                                            
Keyword    Default Prompt Description                                       
---------- ------- ------ ------------------------------------------------- 
USERID             yes    username/password                                 
FULL       N       yes    scan entire database                              
USER               yes    owner of tables to be scanned                     
TABLE              yes    list of tables to scan                            
COLUMN             yes    list of columns to scan                            
EXCLUDE                   list of tables to exclude from scan               
TOCHAR             yes    new database character set name                   
FROMCHAR                  current database character set name               
TONCHAR                   new national character set name                   
FROMNCHAR                 current national character set name               
ARRAY      1024000 yes    size of array fetch buffer                        
PROCESS    1       yes    number of concurrent scan process                 
MAXBLOCKS                 split table if block size exceed MAXBLOCKS        
CAPTURE    N              capture convertible data                          
SUPPRESS                  maximum number of exceptions logged for each table
FEEDBACK                  report progress every N rows                      
BOUNDARIES                list of column size boundaries for summary report 
LASTRPT    N              generate report of the last database scan         
LOG        scan           base file name of report files                    
PARFILE                   parameter file name                               
PRESERVE   N              preserve existing scan results                    
LCSD       N       no     enable language and character set detection       
LCSDDATA   LOSSY   no     define the scope of the detection                 
HELP       N              show help screen (this screen)                    
QUERY      N              select clause to scan subset of tables or columns 
---------- ------- ------ ------------------------------------------------- 
Scanner terminated successfully.

3.使用csscan检测当前数据库

[oracle@node1 ~]$ csscan userid="'"sys/xifenfei as sysdba"'" full=y \
fromchar=ZHS16GBK tochar=AL32UTF8 log=/tmp/check.log capture=y array=1000000 process=4


Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Wed Dec 21 12:36:37 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

Enumerating tables to scan...

. process 1 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA]
. process 2 scanning XFF.T1[AAATjjAAEAAAC7AAAA]
. process 3 scanning TEST.T_XFF[AAAT+6AAEAAAAIQAAA]
. process 2 scanning SYS.METHOD$[AAAAHvAABAAAA0QAAA]
. process 4 scanning SYS.ATTRIBUTE$[AAAAHvAABAAAA0QAAA]
…………………………………………
. process 4 scanning CTXSYS.DR$INDEX_CDI_COLUMN
. process 2 scanning CTXSYS.DR$SDATA_UPDATE
. process 1 scanning EXFSYS.RLM$RULESETSTCODE
. process 3 scanning EXFSYS.RLM$RULESET

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.

4.查看csscan检查日志

[oracle@node1 ~]$ ll /tmp/check.log.*
-rw-r--r-- 1 oracle oinstall  14526 12-21 12:37 /tmp/check.log.err
-rw-r--r-- 1 oracle oinstall 100235 12-21 12:37 /tmp/check.log.out
-rw-r--r-- 1 oracle oinstall   8265 12-21 12:37 /tmp/check.log.txt
--err是错误日志
--out是执行过程
--txt是执行结果汇总
[oracle@node1 tmp]$ cat /tmp/check.log.txt 
Database Scan Summary Report

Time Started  : 2011-12-21 12:36:37
Time Completed: 2011-12-21 12:37:13

Process ID         Time Started       Time Completed
---------- -------------------- --------------------
         1  2011-12-21 12:36:43  2011-12-21 12:37:12
         2  2011-12-21 12:36:43  2011-12-21 12:37:12
         3  2011-12-21 12:36:43  2011-12-21 12:37:12
         4  2011-12-21 12:36:43  2011-12-21 12:37:12
---------- -------------------- --------------------

[Database Size]

Tablespace                           Used            Free           Total       Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM                            723.63M           6.38M         730.00M            .00K
SYSAUX                            686.56M         103.44M         790.00M            .00K
UNDOTBS1                           18.31M         126.69M         145.00M            .00K
TEMP                                 .00K            .00K            .00K            .00K
USERS                             138.69M          22.56M         161.25M            .00K
EXAMPLE                           310.13M          35.50M         345.63M            .00K
------------------------- --------------- --------------- --------------- ---------------
Total                           1,877.31M         294.56M       2,171.88M            .00K

[Database Scan Parameters]

Parameter                      Value                                           
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1                                            
Instance Name                  ora11g                                          
Database Version               11.2.0.3.0                                      
Scan type                      Full database                                   
Scan CHAR data?                YES                                             
Database character set         ZHS16GBK                                        
FROMCHAR                       ZHS16GBK                                        
TOCHAR                         AL32UTF8                                        
Scan NCHAR data?               NO                                              
Array fetch buffer size        1000000                                         
Number of processes            4                                               
Capture convertible data?      YES                                             
------------------------------ ------------------------------------------------

[Scan Summary]

All character type data in the data dictionary are convertible to the new character set
All character type application data remain the same in the new character set

[Data Dictionary Conversion Summary]

Data Dictionary Tables:

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     6,061,403              152                0                0
CHAR                             4,988                0                0                0
LONG                           252,530                0                0                0
VARRAY                          50,812                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        6,369,733              152                0                0
Total in percentage             99.998%           0.002%           0.000%           0.000%

The data dictionary can not be safely migrated using the CSALTER script

XML CSX Dictionary Tables:

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                           711                0                0                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                              711                0                0                0
Total in percentage            100.000%           0.000%           0.000%           0.000%

[Application Data Conversion Summary]

Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     8,142,707                0                0                0
CHAR                            63,085                0                0                0
LONG                                 0                0                0                0
VARRAY                             583                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        8,206,375                0                0                0
Total in percentage            100.000%           0.000%           0.000%           0.000%

[Distribution of Convertible, Truncated and Lossy Data by Table]

Data Dictionary Tables:

USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
CTXSYS.DR$INDEX_VALUE                                            76                0                0
CTXSYS.DR$STOPWORD                                               76                0                0
-------------------------------------------------- ---------------- ---------------- ----------------

XML CSX Dictionary Tables:

USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

Application Data:

USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

[Distribution of Convertible, Truncated and Lossy Data by Column]

Data Dictionary Tables:

USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
CTXSYS.DR$INDEX_VALUE|IXV_VALUE                                  76                0                0
CTXSYS.DR$STOPWORD|SPW_WORD                                      76                0                0
-------------------------------------------------- ---------------- ---------------- ----------------

XML CSX Dictionary Tables:

USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

Application Data:

USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------

[Indexes to be Rebuilt]

USER.INDEX on USER.TABLE(COLUMN)                                                         
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------

--因为检测结果没有Truncation(截断数据)/Lossy(丢失数据)都不存在记录，所以不用查看错误日志
--如果发现不为0，需要检查err日志，然后先处理丢这些记录，然后再转换

二、修改数据库字符集

[oracle@node1 tmp]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 12:59:55 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> select value$ from props$ where name='NLS_CHARACTERSET';

VALUE$
------------------------------------------------------
ZHS16GBK

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.

SQL> alter system enable restricted session;

System altered.

SQL> alter system set job_queue_processes=0 scope=memory;

System altered.

SQL> alter system set aq_tm_processes=0  scope=memory;

System altered.

SQL> alter database open;

Database altered.

SQL> alter database character set internal_use AL32UTF8;

Database altered.

SQL> shutdown  immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
Database opened.
SQL> select value$ from props$ where name='NLS_CHARACTERSET';

VALUE$
--------------------------------------------------------
AL32UTF8

DBMS_SCHEDULER常规操作

Posted on by

1.create job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
  job_name =>'xifenfei_job',
  job_type =>'STORED_PROCEDURE',
  job_action =>'p_schedule',
  repeat_interval =>'FREQ=DAILY; BYHOUR=18,20,22',
  enabled => true,
  comments => 'XIFENFEI');
END;  
--每天18/20/22点执行p_schedule过程

2.disable job

BEGIN
    DBMS_SCHEDULER.DISABLE(name => 'xifenfei_job'); 
END;

3.enable job

BEGIN
    DBMS_SCHEDULER.ENABLE(name => 'xifenfei_job'); 
END;

4.select job

select * from USER_SCHEDULER_JOBS;

5.query logs

SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS 
WHERE job_name='XIFENFEI_JOB';

6.delete logs

--ALL_SCHEDULER_JOB_RUN_DETAILS视图
CREATE OR REPLACE VIEW ALL_SCHEDULER_JOB_RUN_DETAILS
(log_id, log_date, owner, job_name, job_subname, status, error#, 
req_start_date, actual_start_date, run_duration, instance_id, 
session_id, slave_pid, cpu_used, credential_owner, credential_name, 
destination_owner, destination, additional_info)
AS
(SELECT
     j.LOG_ID, j.LOG_DATE, e.OWNER,
     DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1)),
     DECODE(instr(e.NAME,'"'),0,NULL,substr(e.NAME,instr(e.NAME,'"')+1)),
     e.STATUS, j.ERROR#, j.REQ_START_DATE, j.START_DATE, j.RUN_DURATION,
     j.INSTANCE_ID, j.SESSION_ID, j.SLAVE_PID, j.CPU_USED,
     decode(e.credential, NULL, NULL,
        substr(e.credential, 1, instr(e.credential, '"')-1)),
     decode(e.credential, NULL, NULL,
        substr(e.credential, instr(e.credential, '"')+1,
           length(e.credential) - instr(e.credential, '"'))),
     decode(bitand(e.flags, 1), 0, NULL,
        substr(e.destination, 1, instr(e.destination, '"')-1)),
     decode(bitand(e.flags, 1), 0, e.destination,
        substr(e.destination, instr(e.destination, '"')+1,
           length(e.destination) - instr(e.destination, '"'))),
     j.ADDITIONAL_INFO
   FROM scheduler$_job_run_details j, scheduler$_event_log e
   WHERE j.log_id = e.log_id
   AND e.type# = 66 and e.dbid is null
   AND ( e.owner = SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
         or  /* user has object privileges */
            ( select jo.obj# from obj$ jo, user$ ju where
                DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1)) 
                = jo.name and e.owner = ju.name and jo.owner# = ju.user#
                and jo.subname is null and jo.type# = 66
            ) in
            ( select oa.obj#
                from sys.objauth$ oa
                where grantee# in ( select kzsrorol from x$kzsro )
            )
         or /* user has system privileges */
            (exists ( select null from v$enabledprivs
                       where priv_number = -265 /* CREATE ANY JOB */
                   )
             and e.owner!='SYS')
        )
  );
--从这个视图中可以发现，日志有存在SCHEDULER$_JOB_RUN_DETAILS和
--SCHEDULER$_EVENT_LOG两张表中,所以要删除日志，就需要处理这两张表

######################删除日志操作#################
--删除SYS.SCHEDULER$_JOB_RUN_DETAILS中数据
DELETE FROM SYS.SCHEDULER$_JOB_RUN_DETAILS A
 WHERE EXISTS (SELECT 1
          FROM SYS.SCHEDULER$_EVENT_LOG B
         WHERE B.NAME = 'XIFENFEI_JOB'
           AND A.LOG_ID = B.LOG_ID);  
--删除SYS.SCHEDULER$_EVENT_LOG中数据     
DELETE FROM SYS.SCHEDULER$_EVENT_LOG B
 WHERE B.NAME = 'XIFENFEI_JOB';
--提交
 COMMIT;
#####################################################

7.delete jobs

BEGIN
    DBMS_SCHEDULER.DROP_JOB(job_name => 'xifenfei_job'); 
END;

参考:http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm