老熊的三分地-Oracle及数据恢复

此前关于字符集转换的文章，已经有三篇。写这新的一篇来源于最近有几次朋友问到的关于导入导出（exp/imp）的问题。这个问题是这样的：
使用imp导入数据后，发现数据是正确的，没有乱码，但是表和列上的注释（comments）、中文列名、Procedure/Package里面的中文全部变成了乱码。

网上很少有文章讨论到这一点，其实exp/imp与通常执行SQL引起的字符集转换有一些不同。这得从dmp文件的格式说起。
先看看下面的测试：

SQL> create table t1 ( a number,b varchar2(100));
SQL> insert into t1 values (123456,'aaaaaa');
SQL> insert into t1 values (67890,'中中中中');
SQL> commit;
SQL> comment on table t1 is '测试表';

现在将NLS_LANG设置为AMERICAN_AMERICA.ZHS16GBK，导出T1表，然后看看导出的dmp文件中的数据：

000008f0h: 22 54 31 22 0A 43 52 45 41 54 45 20 54 41 42 4C ; "T1".CREATE TABL
00000900h: 45 20 22 54 31 22 20 28 22 41 22 20 4E 55 4D 42 ; E "T1" ("A" NUMB
00000910h: 45 52 2C 20 22 42 22 20 56 41 52 43 48 41 52 32 ; ER, "B" VARCHAR2
00000920h: 28 31 30 30 29 29 20 20 50 43 54 46 52 45 45 20 ; (100)) PCTFREE
00000930h: 31 30 20 50 43 54 55 53 45 44 20 34 30 20 49 4E ; 10 PCTUSED 40 IN
00000940h: 49 54 52 41 4E 53 20 31 20 4D 41 58 54 52 41 4E ; ITRANS 1 MAXTRAN
00000950h: 53 20 32 35 35 20 53 54 4F 52 41 47 45 28 49 4E ; S 255 STORAGE(IN
00000960h: 49 54 49 41 4C 20 31 30 34 38 35 37 36 20 46 52 ; ITIAL 1048576 FR
00000970h: 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C 49 ; EELISTS 1 FREELI
00000980h: 53 54 20 47 52 4F 55 50 53 20 31 29 20 54 41 42 ; ST GROUPS 1) TAB
00000990h: 4C 45 53 50 41 43 45 20 22 54 45 53 54 5F 38 4B ; LESPACE "TEST_8K
000009a0h: 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 4F 4D 50 ; " LOGGING NOCOMP
000009b0h: 52 45 53 53 0A 49 4E 53 45 52 54 20 49 4E 54 4F ; RESS.INSERT INTO
000009c0h: 20 22 54 31 22 20 28 22 41 22 2C 20 22 42 22 29 ; "T1" ("A", "B")
000009d0h: 20 56 41 4C 55 45 53 20 28 3A 31 2C 20 3A 32 29 ; VALUES (:1, :2)
000009e0h: 0A 02 00 02 00 16 00 01 00 64 00 54 03 01 00 00 ; .........d.T....
000009f0h: 00 00 00 04 00 C3 0D 23 39 06 00 61 61 61 61 61 ; .....?#9..aaaaa
00000a00h: 61 00 00 04 00 C3 07 4F 5B 08 00 D6 D0 D6 D0 D6 ; a....?O[..中中?
00000a10h: D0 D6 D0 00 00 FF FF 0A 43 4F 4D 4D 45 4E 54 20 ; 兄?..COMMENT
00000a20h: 4F 4E 20 54 41 42 4C 45 20 22 54 31 22 20 49 53 ; ON TABLE "T1" IS
00000a30h: 20 0A 08 00 27 B2 E2 CA D4 B1 ED 27 0A 45 58 49 ; ...'测试表'.EXI
00000a40h: 54 0A 45 58 49 54 0A 00 00 00 00 00 00 00 00 00 ; T.EXIT..........

从上面的数据可以看到，有两部分数据，一部分是代码性质的数据，包括CREATE TABLE、COMMIT、INSERT等SQL语句；另一部分就是表T1的实际数据了,红色部分就是表的实际数据。

我们先看看表中的实际数据：

SQL> select a,dump(a,16) da,dump(b,16) db from t1;

         A DA                             DB
---------- ------------------------------ --------------------------------------
    123456 Typ=2 Len=4: c3,d,23,39        Typ=1 Len=6: 61,61,61,61,61,61
     67890 Typ=2 Len=4: c3,7,4f,5b        Typ=1 Len=8: d6,d0,d6,d0,d6,d0,d6,d0

对比一下就可以发现，dmp文件中T1表的数据，与数据库中原始数据是一模一样的，没有发生任何变化，也就是说，dmp文件中存储的表数据实际上与数据在数据库中存储的相同的二进制形式。

现在将NLS_LANG设置为AMERICAN_AMERICA.US7ASCII，导出T1表，然后看看导出的dmp文件中的数据：

000008f0h: 22 54 31 22 0A 43 52 45 41 54 45 20 54 41 42 4C ; "T1".CREATE TABL
00000900h: 45 20 22 54 31 22 20 28 22 41 22 20 4E 55 4D 42 ; E "T1" ("A" NUMB
00000910h: 45 52 2C 20 22 42 22 20 56 41 52 43 48 41 52 32 ; ER, "B" VARCHAR2
00000920h: 28 31 30 30 29 29 20 20 50 43 54 46 52 45 45 20 ; (100)) PCTFREE
00000930h: 31 30 20 50 43 54 55 53 45 44 20 34 30 20 49 4E ; 10 PCTUSED 40 IN
00000940h: 49 54 52 41 4E 53 20 31 20 4D 41 58 54 52 41 4E ; ITRANS 1 MAXTRAN
00000950h: 53 20 32 35 35 20 53 54 4F 52 41 47 45 28 49 4E ; S 255 STORAGE(IN
00000960h: 49 54 49 41 4C 20 31 30 34 38 35 37 36 20 46 52 ; ITIAL 1048576 FR
00000970h: 45 45 4C 49 53 54 53 20 31 20 46 52 45 45 4C 49 ; EELISTS 1 FREELI
00000980h: 53 54 20 47 52 4F 55 50 53 20 31 29 20 54 41 42 ; ST GROUPS 1) TAB
00000990h: 4C 45 53 50 41 43 45 20 22 54 45 53 54 5F 38 4B ; LESPACE "TEST_8K
000009a0h: 22 20 4C 4F 47 47 49 4E 47 20 4E 4F 43 4F 4D 50 ; " LOGGING NOCOMP
000009b0h: 52 45 53 53 0A 49 4E 53 45 52 54 20 49 4E 54 4F ; RESS.INSERT INTO
000009c0h: 20 22 54 31 22 20 28 22 41 22 2C 20 22 42 22 29 ; "T1" ("A", "B")
000009d0h: 20 56 41 4C 55 45 53 20 28 3A 31 2C 20 3A 32 29 ; VALUES (:1, :2)
000009e0h: 0A 02 00 02 00 16 00 01 00 64 00 54 03 01 00 00 ; .........d.T....
000009f0h: 00 00 00 04 00 C3 0D 23 39 06 00 61 61 61 61 61 ; .....?#9..aaaaa
00000a00h: 61 00 00 04 00 C3 07 4F 5B 08 00 D6 D0 D6 D0 D6 ; a....?O[..中中?
00000a10h: D0 D6 D0 00 00 FF FF 0A 43 4F 4D 4D 45 4E 54 20 ; 兄?..COMMENT
00000a20h: 4F 4E 20 54 41 42 4C 45 20 22 54 31 22 20 49 53 ; ON TABLE "T1" IS
00000a30h: 20 0A 05 00 27 3F 3F 3F 27 0A 45 58 49 54 0A 45 ; ...'???'.EXIT.E
00000a40h: 58 49 54 0A 00 00 00 00 00 00 00 00 00 00 00 00 ; XIT.............

这一次我们可以看到，红色部分的数据，也就是表的实际数据没有发生任何变化。
有变化的地方在哪里？稍微对比就可以发现，代码部分发生了变化。第一次导出时的COMMENT语句，明显可以看到“测试表”三个汉字，而第二次导出时，这三个汉字变成了三个问号。显然就是导出时GBK转换为US7ASCII码时，发生了乱码。

测试到这里，结论已经很明了了。exp导出时，表中的数据没有发生任何变化，以存储在数据库时的二进制一致的形式存储在了dmp文件中。然而，代码部分则于是纯文本形式的数据，在导出时要遵循字符集转换原则，发生转换。既然转换部分是代码形式的数据，那么下列代码中的数据都会发生转换：建表（CREATE TABLE），注释（COMMENT），创建视图（CREATE VIEW），其他程序代码（FUNCTION/PACKAGE/TRIGGER/PROCEDURE）如此等等。因此如果exp/imp时字符集不兼容，那么中文列名，注释、视图、程序代码中的中文都将会是乱码，而表中的实际数据则不会发生变化。这也是本文开头提到的问题的来源。