Oracle 11.2.0.2 exp导出错误处理一则

fgemkojfdw

客户生产库版本为AIX 11.2.0.2,客户端版本为11.2.0.1，在客户端按用户级别导出表格的时候出现以下错误：
. . 正在导出表           S61_B214_GT_2010_3301
导出了                                                          1224 行
EXP-00008: 遇到 ORACLE 错误 1455
ORA-01455: 转换列溢出整数数据类型
EXP-00000: 导出终止失败
为了排除错误，客户端以表方式导出，S61_B214_GT_2010_3301，的下一张表格报同样错误，同时在某些客户端报错信息多了以下错误：
EXP-00003: no storage definition found for segment(0,0)
这个错误已经很明显了，因为在11.2.0.1时，由参数deferred_segment_creation（默认为true）控制是否对新建表格默认分配段空间，所以在exp新建表格时，往往会出现此类错误。
最终Oracle认为此类错误为bug，并在11.2.0.2时得以修正。知道问题的原因之后，我们就可以有以下3种解决方案：
1、在生产端导出
2、客户端升级到11.2.0.2，由于客观原因，不能升级客户端.
3、生产库将参数deferred_segment_creation由true改为false（所幸该参数可以动态修改），并重建相关新建表格
select owner,table_name from dba_tables where table_name not in (select segment_name from dba_segments where segment_type = 'TABLE') and owner='HZ';



以下为测试过程：
SQL> create user zhoul identified by zhoul;

User created.

SQL> grant dba to zhoul;

Grant succeeded.

SQL> conn zhoul/zhoul
Connected.
SQL> create table test1 (id number);

Table created.

SQL> create table test2 as select file# from v$datafile;

Table created.

SQL> select owner,table_name from dba_tables where table_name not in (select segment_name from dba_segments where segment_type = 'TABLE') and owner='ZHOUL';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
ZHOUL                          TEST1

SQL> show parameter deferred_segment_creation

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE

[ora11g@linux-64 ~]$ exp zhoul/zhoul file=/tmp/zhoul.dmp owner=zhoul

Export: Release 11.2.0.2.0 - Production on Fri Nov 11 10:54:18 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user ZHOUL
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user ZHOUL
About to export ZHOUL's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export ZHOUL's tables via Conventional Path ...
. . exporting table                          TEST1          0 rows exported
. . exporting table                          TEST2          5 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.
[ora11g@linux-64 ~]$ exp zhoul/zhoul file=/tmp/test1.dmp tables=test1 owner=zhoul

Export: Release 11.2.0.2.0 - Production on Fri Nov 11 10:55:39 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00026: conflicting modes specified
EXP-00000: Export terminated unsuccessfully
[ora11g@linux-64 ~]$ exp zhoul/zhoul file=/tmp/test1.dmp tables=test1

Export: Release 11.2.0.2.0 - Production on Fri Nov 11 10:55:45 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII 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                          TEST1          0 rows exported
Export terminated successfully without warnings.



D:\>exp zhoul/zhoul@drb_11g file=d:\zhoul.dmp tables=test1

Export: Release 11.2.0.1.0 - Production on 星期五 11月 11 11:29:04 2011

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


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...
. . 正在导出表                           TEST1
EXP-00003: 未找到段 (0,0) 的存储定义
导出成功终止, 但出现警告。


SQL> conn zhoul/zhoul
Connected.
SQL> alter system set deferred_segment_creation=false;

System altered.

SQL> drop table test1;

Table dropped.

SQL> create table test1 (id number);

Table created.

D:\>exp zhoul/zhoul@drb_11g file=d:\zhoul.dmp tables=test1

Export: Release 11.2.0.1.0 - Production on 星期五 11月 11 11:31:03 2011

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


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Produc
tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

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