Oracle 11.2.0.2 exp匯出錯誤處理一則

客戶生產庫版本為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 wner='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 wner='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 wner=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 wner=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 行
成功終止匯出, 沒有出現警告。