IMP/EXP后COMMENTS乱码问题

深圳－刀

IMP、EXP中如果导入和导出时CLIENT的字符集不一致时容易出现乱码问题，这个大家都遇到过。今天遇到一件比较奇怪的问题，开发那边报告前段时间从生产库中EXP一个用户然后IMP到开发环境中，最近发现开发环境中：表的数据、存储过程中的中文注释等都正常，但所有表、字段的COMMENTS都是变成了“?????????|????????? ”类似的乱码。
现在无法得知当时CLIENT设置的字符集，但如果说是字符集的问题，那么应该存储过程中的中文注释也应该是乱码才对，为何只有表、字段的COMMENTS变成乱码？

Maclean Liu(刘相兵

ODM TEST:

[oracle@vrh8 ~]$ export NLS_LANG=american_america.AL32UTF8
[oracle@vrh8 ~]$ sqlplus / as sysdba

SQL> conn maclean/maclean
Connected.


SQL> drop table need_comment;

Table dropped.

SQL> create table need_comment(t1 int);

Table created.

SQL> insert into need_comment values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> comment on table need_comment is '我是注释';

Comment created.

SQL> select table_name, comments from user_tab_comments where table_name='NEED_COMMENT';

TABLE_NAME
------------------------------
COMMENTS
--------------------------------------------------------------------------------
NEED_COMMENT
我是注释



SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

[oracle@vrh8 ~]$ exp maclean/maclean owner=maclean file=comment_test.dmp

Export: Release 10.2.0.1.0 - Production on Tue Apr 10 09:53:12 2012

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining 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 MACLEAN
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user MACLEAN
About to export MACLEAN's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export MACLEAN's tables via Conventional Path ...
. . exporting table                   NEED_COMMENT          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.





COMMENT ON TABLE "NEED_COMMENT" IS


SQL> select dump('我是注释',16) from dual;

DUMP('我6)
--------------------------------------------------------------
Typ=96 Len=16: e6,88,91,e5,8f,e6,98,af,e4,b8,e6,b3,a8,e9,87,8a

Maclean Liu(刘相兵

Action Plan:

NEED MORE INFO ABOUT CHARACTER SET  &  NLS_LANG etc ...