ORA-00904 & EXP-00091

11.2.0.1.0客户端通过exp导出10.2.0.4.0的数据时出现ORA-00904: "POLTYP": invalid identifier错误。

换个10.2.0.4.0的客户端能正常导出10.2.0.4.0的数据。

E:/>exp user/password@dev file=a.dmp tables=aaaa
Export: Release 11.2.0.1.0 - Production on 星期二 4月 19 17:14:30 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Produc
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
即将导出指定的表通过常规路径...
EXP-00008: 遇到 ORACLE 错误 904
ORA-00904: "POLTYP": invalid identifier
EXP-00000: 导出终止失败

起本地的11.2.0.1.0用exp测试导出，能正常导出，但出现EXP-00091错误。
D:/>exp report/myoracle file=ora11.dmp tables=emp
Export: Release 11.2.0.1.0 - Production on 星期二 4月 19 17:34:43 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
服务器使用 AL32UTF8 字符集 (可能的字符集转换)
即将导出指定的表通过常规路径...
. . 正在导出表                             EMP导出了           3 行
EXP-00091: 正在导出有问题的统计信息。
EXP-00091: 正在导出有问题的统计信息。
导出成功终止, 但出现警告。

 

ID 730106.1中记录了解决方法，选了个比较简单的在导出命令后加statistics=none

D:/>exp scott/tiger file=ora11.dmp tables=emp statistics=none
Export: Release 11.2.0.1.0 - Production on 星期二 4月 19 17:35:15 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
服务器使用 AL32UTF8 字符集 (可能的字符集转换)

即将导出指定的表通过常规路径...
. . 正在导出表                             EMP导出了           3 行
成功终止导出, 没有出现警告。

 

 

以下是ID 730106.1中的部分片段:

The pre-calculated optimizer statistics are flagged as questionable at export time if:
1. There are row errors while exporting.

2. The client characterset or NCHAR characterset does not match the server characterset or
NCHAR characterset.

3. A QUERY clause is specified.

4. Only certain partitions or subpartitions are exported.

 

==>This customer matches 2, the client side characterset is not set, the server characterset is
ZHS16GBK, so the client side characterset does not match server side characterset.

$ echo $NLS_LANG
su: NLS_LANG: Parameter not set.

SQL> select parameter,value from nls_database_parameters where parameter like '%CHARACTERSET%';
NLS_NCHAR_CHARACTERSET
AL16UTF16
NLS_CHARACTERSET
ZHS16GBK

 

Solution
To implement the solution, please execute the following steps:

1. Set NLS_LANG on OS, then exp again:
$export NLS_LANG=american_america.ZHS16GBK
$echo $NLS_LANG
$exp system/passwd full=y

2. You can add statistics=none:
$exp system/passwd full=y statistics=none