导入数据IMP-00058 ORA-00904

最新推荐文章于 2024-01-19 17:11:35 发布

一卒

最新推荐文章于 2024-01-19 17:11:35 发布

阅读量2.8k

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle 文章标签： IMP-00058 ORA-00904 oracle

本文链接：https://blog.youkuaiyun.com/wxl1314920/article/details/17024967

oracle 专栏收录该内容

27 篇文章

订阅专栏

本文介绍在将Oracle源库分区表数据迁移到历史库时遇到IMP-00058 ORA-00904错误的解决过程。通过对比源库和目标库表结构差异，添加缺失字段后成功完成数据迁移。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

将源库分区表数据迁移到历史库中碰到IMP-00058 ORA-00904错误，具体操作及解决如下：

$imp

\'/ as sysdba\' STATISTICS=NONEDATA_ONLY=y file=/backup/wxlun20130911.dmplog=/backup/wxlun20130911.implog fromuser=wxlun touser=wxlun_his buffer=5400000

Import: Release 11.2.0.3.0 - Production on Wed Sep 11 10:54:392013

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real ApplicationTesting options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by WXLUN, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR characterset
. importing WXLUN's objects into WXLUN_HIS
. . importing partition "WXLUN_T01":"PT_201306"
IMP-00058: ORACLE error 904 encountered
ORA-00904: "TOEXPRESSCOMPANYID": invalid identifier
IMP-00057: Warning: Dump file may not contain data of allpartitions of this table
. . importing partition "WXLUN_T02":"VAPT_201306"  12553338 rows imported
IMP-00057: Warning: Dump file may not containdata of all partitions of this table ---因为只导入一个分区数据，会报这个错误，在此忽略
Import terminated successfully with warnings.

表WXLUN_T02分区数据导入成功，而WXLUN_T01导入失败，查看目的库的WXLUN_T01表结构
SYS@wxlun_his>desc WXLUN.WXLUN_T01
Name                                     Null?   Type
------------------------------------------------- ----------------------------
BCID                                     NOT NULL VARCHAR2(20)
FORMCODE                                 NOT NULL VARCHAR2(50)
CURRENTSTATUS                            NOT NULL NUMBER(3)
PRESTATUS                                NOT NULL NUMBER(3)
RETURNSTATUS                                      NUMBER(3)
OPERATETYPE                              NOT NULL NUMBER(5)
CURRENTDISTRIBUTIONCODE                  NOT NULL VARCHAR2(50)
DELIVERSTATIONID                         NOT NULL NUMBER(10)
NOTE                                     NOT NULL VARCHAR2(400)
CREATEBY                                 NOT NULL NUMBER(10)
CREATEDEPT                               NOT NULL NUMBER(10)
CREATETIME                               NOT NULL DATE
ISDELETED                                NOT NULL NUMBER(1)
SYNCFLAG                                 NOT NULL NUMBER(1)
SYNCTIME                                          DATE
IPADDRESS                                         VARCHAR2(20)
CLIENTINFO                                        VARCHAR2(2000)

源库WXLUN_T01表结构
sys@wxlun:SQL>desc WXLUN.WXLUN_T01
Name                                                                    Null?   Type
-------------------------------------------------------------------------------- -------------------------------------------------
BCID                                                                    NOT NULL VARCHAR2(20)
FORMCODE                                                                NOT NULL VARCHAR2(50)
CURRENTSTATUS                                                           NOT NULL NUMBER(3)
PRESTATUS                                                               NOT NULL NUMBER(3)
RETURNSTATUS                                                                     NUMBER(3)
OPERATETYPE                                                             NOT NULL NUMBER(5)
CURRENTDISTRIBUTIONCODE                                                 NOT NULL VARCHAR2(50)
DELIVERSTATIONID                                                        NOT NULL NUMBER(10)
NOTE                                                                    NOT NULL VARCHAR2(400)
CREATEBY                                                                NOT NULL NUMBER(10)
CREATEDEPT                                                              NOT NULL NUMBER(10)
CREATETIME                                                              NOT NULL DATE
ISDELETED                                                               NOT NULL NUMBER(1)
SYNCFLAG                                                                NOT NULL NUMBER(1)
SYNCTIME                                                                         DATE
IPADDRESS                                                                        VARCHAR2(20)
CLIENTINFO                                                                       VARCHAR2(2000)
TODISTRIBUTIONCODE                                                               VARCHAR2(100)
TOEXPRESSCOMPANYID                                                               NUMBER

发现源库的表增加了两个字段(随着业务需要，某些表会增加字段)，将目的库表添加相应字段，并将导入失败的表数据重新导入：

SYS@wxlun_his>alter table WXLUN.WXLUN_T01 add TODISTRIBUTIONCODEVARCHAR2(100);
alter table WXLUN.WXLUN_T01 add TOEXPRESSCOMPANYID NUMBER;

Table altered.

SYS@wxlun_his>
Table altered.

$imp \'/ as sysdba\' STATISTICS=NONEDATA_ONLY=y file=/backup/wxlun20130911.dmplog=/backup/wxlun20130911.implog fromuser=wxlun touser=wxlun_his buffer=5400000 tables=WXLUN_T01

Import: Release 11.2.0.3.0 - Production on Wed Sep 11 11:09:282013

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real ApplicationTesting options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by WXLUN, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR characterset
. importing WXLUN's objects into WXLUN_HIS
. . importing partition"WXLUN_T01":"PT_201306"   6779635 rowsimported   ---导入成功
IMP-00057: Warning: Dump file may not contain data of allpartitions of this table
Import terminated successfully with warnings.
$