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

bisal(Chen Liu)

于 2021-06-04 07:50:00 发布

阅读量2.3k

点赞数

文章标签： java mysql 数据库 oracle linux

本文链接：https://blog.csdn.net/bisal/article/details/117575500

版权

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，