手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

导入数据IMP-00058 ORA-00904

[日期:2013-12-03] 来源:Linux社区  作者:wxl1314920 [字体: ]
将源库分区表数据迁移到历史库中碰到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.
$

更多Oracle相关信息见Oracle 专题页面 http://www.chineselinuxuniversity.net/topicnews.aspx?tid=12

相关阅读

ORA-01172、ORA-01151错误处理 http://www.chineselinuxuniversity.net/Linux/2013-06/86529.htm

ORA-00600 [2662]错误解决 http://www.chineselinuxuniversity.net/Linux/2013-06/86528.htm

ORA-01078 和 LRM-00109 报错解决方法 http://www.chineselinuxuniversity.net/Linux/2012-07/66044.htm

ORA-00471 处理方法笔记 http://www.chineselinuxuniversity.net/Linux/2013-09/90017.htm

ORA-00314,redolog 损坏,或丢失处理方法 http://www.chineselinuxuniversity.net/Linux/2013-09/90646.htm

ORA-00257 归档日志过大导致无法存储的解决办法 http://www.chineselinuxuniversity.net/Linux/2013-09/90594.htm

linux
相关资讯       ORA-00904  IMP-00058 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款