1. 首页
  2. IT资讯

Oracle 11.2.0.2 exp导出错误处理一则

Oracle 11.2.0.2 exp导出错误处理一则

    博客分类:

  • ORACLE管理

客户生产库版本为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 行 成功终止导出, 没有出现警告。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27042095/viewspace-734456/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195820.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code