exp执行过程出现ORA-01455的解决(存疑)

向右看齐 2022-10-18 04:49 138阅读 0赞

19c的客户端用exp导出19c的某个用户，执行如下指令，

[oracle@bisal ~]$ exp bisal/bisal@bisal file=test.dmp consistent=y
    
    
    Export: Release 19.0.0.0.0 - Production on Thu Jun 3 13:43:54 2021
    Version 19.3.0.0.0
    
    
    Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
    
    
    Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Legacy Mode Active due to the following parameters:
    Legacy Mode Parameter: "consistent=TRUE" Location: Command Line, Replaced with: "flashback_time=TO_TIMESTAMP('2021-06-03 13:43:54', 'YYYY-MM-DD HH24:MI:SS')"
    Legacy Mode has set reuse_dumpfiles=true parameter.
    Starting "BISAL"."SYS_EXPORT_SCHEMA_01":  bisal/********@bisalpdb2 schemas=bisal directory=dir_exp dumpfile=test.dmp flashback_time=TO_TIMESTAMP('2021-06-03 13:43:54', 'YYYY-MM-DD HH24:MI:SS') reuse_dumpfiles=true 
    
    
    Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.3.0.0.0
    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 BISAL 
    . exporting PUBLIC type synonyms
    . exporting private type synonyms
    . exporting object type definitions for user BISAL 
    About to export BISAL's objects ...
    . exporting database links
    . exporting sequence numbers
    . exporting cluster definitions
    . about to export BISAL's tables via Conventional Path ...
    . . exporting table                              A     583096 rows exported
    EXP-00008: ORACLE error 1455 encountered
    ORA-01455: converting column overflows integer datatype
    . . exporting table                              B      73012 rows exported
    . . exporting table                    DEPARTMENTS         27 rows exported
    . . exporting table                      EMPLOYEES        107 rows exported
    . . exporting table                            T01      73035 rows exported
    . . exporting table                           TEST      73024 rows exported
    . . exporting table                         TEST_D    1100000 rows exported
    . . exporting table                      TEST_PART
    . . exporting partition                             P1        500 rows exported
    . . exporting partition                             P2        500 rows exported
    . . exporting partition                             P4       1000 rows exported
    . . exporting partition                             P6       1500 rows exported
    . . exporting partition                             P7          1 rows exported
    . . exporting partition                            P10          1 rows exported
    . . exporting partition                            P11          1 rows exported
    . . exporting partition                            P12          1 rows exported
    . . exporting partition                          P_MAX          0 rows exported
    . . exporting table                             TK          2 rows exported
    . . exporting table                          T_IOT    1100000 rows exported
    . . exporting table                     T_STAT_COL      72988 rows exported
    . . exporting table                   T_STAT_INDEX      72985 rows exported
    . . exporting table                   T_STAT_TABLE          2 rows exported
    . . exporting table                         T_USER          3 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 with warnings.

可以看到，导出成功了，但是中间过程，提示了ORA-01455错误，如果用expdp，不会出现错误，

EXP-00008: ORACLE error 1455 encountered
    ORA-01455: converting column overflows integer datatype

Conventional Export (EXP) Fails With ORA-1455 (Doc ID 1604601.1)解释了出现ORA-01455的原因，

> It is not possible to store the number 32768 in 1 byte.
> 
>   
> The column in the database table is defined as NUMBER. But in exp, the variable into which we read the content of the table column is defined as NUMBER 1 byte (this is because in older versions before 11g, we never used larger values than 255). With newer versions, the logic has changed and the column in the table may now store larger numbers, which exceed 1 byte. Because exp is not supported in newer versions, that variable is not changed and leads to ORA-1455, when a big value needs to be stored in fewer bytes than necessary.
> 
>   
> The error message ORA-1455 in this case is raised when we (exp) extract the views from data dictionary. We internally use the view EXU8VEW which has a column named FLAGS (coming from OBJ$). When a defined view stores in this column a value larger than 255 (in our present case 32768), then exp will raise ORA-1455 during the step 'exporting views'.

何老师解释，

“exp是用proc C写的，在定义字段时，字段值被定义成UB4MAXVAL，这种类型的值最大为4294967295，即2^32-1，当导出的时候，库中有些统计信息的值，超过了4294967295，所以就报错ora-1445”。

针对这个问题，网上有很多“招”，

1. 服务端和客户端版本不一致。- 都是19c。

2. 存在失效对象。- 在当前的用户下，确实存在一个INVALID的package body，删除后执行，仍出这个错。

3. MOS文章Doc ID 1604601.1，提到了检索一张系统视图，有无这个用户对应的表/视图在其中，而且字段FLAGS<>0。- 无此情况。

4. 段延迟特性，如果是空表，未给分配extent。- 删除了所有num\_rows=0的表，无空表，即使设置了deferred\_segment\_creation=false，仍出错。

5. exp增加indexes=n和statistics=n参数. - 经过测试，indexes=n没作用，但是statistics=n起到了作用，只要带上statistics=n，exp则不会出现ORA-01455的错误了。

结合3和5，user\_tab\_statistics中定义为NUMBER类型的字段，没存在超过4294967295的。

结合4和5，看下统计信息中是否存在空值的场景，一张分区表的max分区统计信息是0，

![2904d3d7f09a5e1adbf6860e15d6150d.png][]

确实这个分区记录数是0，

![ff4de55214438f596cc388c70345c0a6.png][]

插入一条记录，采集统计信息，确认无空值，

![359c757dc7656fbed619eadf573fd6bb.png][]

以上两个调整，exp执行还是提示错误，悬而未决的问题。

虽然问题解决了，但是没太懂，为什么statistics统计信息的导出，导致了ORA-01455的错误，而且只是exp报错，expdp没错？

参考文献，

https://community.oracle.com/mosc/discussion/4477829/exp-00008-oracle-error-1455-encountered

https://www.cnblogs.com/Jingkunliu/p/13706519.html

https://blog.csdn.net/michaell\_qu/article/details/8540967

一些和导入导出相关的文章，

《[imp执行错误IMP-00010][imp_IMP-00010]》

《[对imp中的fromuser参数的偏差理解][imp_fromuser]》

《[imp/exp导入导出的一些错误][imp_exp]》

《[imp错误IMP-00098: INTERNAL ERROR: impgst2Segmentation fault][imp_IMP-00098_ INTERNAL ERROR_ impgst2Segmentation fault]》

《[Oracle导入导出的常见错误][Oracle]》

《[解决导入过程中出现的ORA-02289错误][ORA-02289]》

《[生产数据导入测试环境碰见的一些问题][Link 1]》

《[使用exp导出报错EXP-00091][exp_EXP-00091]》

《[数据迁移中碰见的一些问题][Link 2]》

《[如何验证dump文件的有效性][dump]》

**近期更新的文章：**

《[尝试一下OSWatch][OSWatch]》

《[数据库hang等待链分析利器][hang]》

《[会议交流的一些杂谈][Link 3]》

《[NULL判断对SQL的影响][NULL_SQL]》

《[曾经运维生涯中的几个“最”][Link 4]》

**文章分类和索引：**

《[公众号700篇文章分类和索引][700]》

[2904d3d7f09a5e1adbf6860e15d6150d.png]: /images/20221014/924b1de22fd44577a10cca1eeba25c1c.png
[ff4de55214438f596cc388c70345c0a6.png]: /images/20221014/f57a87fa79a2496eaad763d0b66aed57.png
[359c757dc7656fbed619eadf573fd6bb.png]: /images/20221014/afd566d910b146849a0a4809fc521599.png
[imp_IMP-00010]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7baddf52eac0fed11d0b20acde13d874f8c14bb36aa7dc642318fc08d0214c1af7a32f7&idx=1&mid=2247492169&scene=21&sn=6b6a65d66fc3673ea426268b6ffeaac8#wechat_redirect
[imp_fromuser]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea8156d4ddf6dfc25c2bbf94b4b1fffef8ae8984a7cb6c0526f9089ca5067d3367f2a8846877&idx=1&mid=2247488295&scene=21&sn=c693550082cfe3db5d938db2304450b4#wechat_redirect
[imp_exp]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814df4ddf6c4e2266e8b5a5945b10a66c8c88a399eb45f9503980be4c5e838c8fe6ac96fd6&idx=1&mid=2247485447&scene=21&sn=aec596757a180e6eb34f92a2cffceed1#wechat_redirect
[imp_IMP-00098_ INTERNAL ERROR_ impgst2Segmentation fault]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814267ddf6cb712f7ccb03628b3a3f86ecb5b0c22f396a51e24a4705b1c881430d28f28648&idx=1&mid=2247485332&scene=21&sn=aec8321378b6185b645587bf9472474e#wechat_redirect
[Oracle]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814262ddf6cb740d253a17cabd54f9ff2780752df4af8587c92e430344650f7033c5ce6ad2&idx=1&mid=2247485329&scene=21&sn=7cf01bbfcc38a0477235d04587192755#wechat_redirect
[ORA-02289]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814617ddf6cf01507c300719ad166bbd90cb1757324235af810564d7d37419302435150d59&idx=1&mid=2247484388&scene=21&sn=875654fc01255f44323443b4694e35ed#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814636ddf6cf20de5bdaef143b0252f1b9623d601f7646f690ea3f2fa00aba8430ee5703c7&idx=1&mid=2247484357&scene=21&sn=d07328769ced2a7973788ebbdde76e01#wechat_redirect
[exp_EXP-00091]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814469ddf6cd7f112b9842fde70744e09fe69799ff8e2d15ada783aa29cbe70bfea682415b&idx=1&mid=2247483802&scene=21&sn=a76aa06b54c89b9c5ec3bb55bd3b6118#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea8141b1ddf6c8a730172c5fed26e17d3dbf2bb560ca904beaf9fcfa50bf9a63463528b8eb92&idx=1&mid=2247484482&scene=21&sn=d0dfc966fc795b5bf44ea0ad79ba8af5#wechat_redirect
[dump]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814622ddf6cf34de9c4da46ecaa8800c44a9652224fcbbfd097cef771d6e421d14b99d8cdd&idx=1&mid=2247484369&scene=21&sn=30dd4a81c9f904123ae1d579f1041fd9#wechat_redirect
[OSWatch]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a787ddf52e91ce0091e71c200202961a75e452bea9ed2d64cb29a82ecb145cc3a798d2ca&idx=1&mid=2247492212&scene=21&sn=966d6b5ec12307602ff44f65193fb434#wechat_redirect
[hang]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7a4ddf52eb2590eacab606223bb71dd660be9955ad297a5e44b9314c60266e13c962f34&idx=1&mid=2247492183&scene=21&sn=a80b4e1d743da42ca26aff7d8d022238#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7a2ddf52eb46af1f228aff9ee58cdb06375aa6e9cc304bcb8ecc9cc1f7f94f1f1b9a44f&idx=1&mid=2247492177&scene=21&sn=18fc61ccb334bac37dc9b091f4b32f7f#wechat_redirect
[NULL_SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7c5ddf52ed323d754ab80899ff4fc065453dc23e16b96e367d2f06d6dff3205950028ef&idx=1&mid=2247492150&scene=21&sn=128e815af4e8a0d7986889fc571b7233#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7f2ddf52ee49775b374898978d2f13624f1e51dcbfecdfdd32925332132e4eaa88cc7bd&idx=1&mid=2247492097&scene=21&sn=f2c338461919594062136eb10a70e9c4#wechat_redirect
[700]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5dfddf52cc949a5b8b316ee905927ff399713b34aec7d806c656e8ada09bfb562c039ca&idx=1&mid=2247491628&scene=21&sn=83bd320bd28da84501fada2e8d515e77#wechat_redirect