表导入出现ORA-00017 IMP-00003 ORA-02264错误

根据客户要求从生产库中导出3张表到测试库，在表导入的时候出现ORA-00017 IMP-00003 ORA-02264错误，下面是具体操作：

1.导出表
C:\Users\hzq> exp mbp/mbp@mbpdb file='C:\table_exp\20160519.dmp' log=20160519.log tables=pay_trade_list,pay_card_info,pay_cust_info
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                 PAY_TRADE_LIST    8229042 rows exported
. . exporting table                  PAY_CARD_INFO     329223 rows exported
. . exporting table                  PAY_CUST_INFO     329238 rows exported
Export terminated successfully without warnings.

2.删除测试库的表
truncate table PAY_TRADE_LIST;
truncate table PAY_CARD_INFO;
truncate table PAY_CUST_INFO;

drop table PAY_TRADE_LIST;
drop table PAY_CARD_INFO;
drop table PAY_CUST_INFO;

3. 导入表
ZY1:/home/oracle#imp mbp/mbp file=/home/oracle/20160519.dmp ignore=y full=y log=20160519.log 

Import: Release 11.2.0.3.0 - Production on Thu May 19 15:54:17 2016

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Pro
ductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing MBP's objects into MBP
. importing MBP's objects into MBP
. . importing table               "PAY_TRADE_LIST"    8229042 rows imported
. . importing table                "PAY_CARD_INFO"     329223 rows imported
IMP-00017: following statement failed with ORACLE error 2264:
 "ALTER TABLE "PAY_CARD_INFO" ADD  CONSTRAINT "PK_PAY_CARD_INFO" PRIMARY KEY "
 "("ID") DISABLE           "
IMP-00003: ORACLE error 2264 encountered
ORA-02264: name already used by an existing constraint
. . importing table                "PAY_CUST_INFO"     329238 rows imported
IMP-00017: following statement failed with ORACLE error 2264:
 "ALTER TABLE "PAY_CUST_INFO" ADD  CONSTRAINT "PK_PAY_CUST_INFO" PRIMARY KEY "
 "("ID") DISABLE           "
IMP-00003: ORACLE error 2264 encountered
ORA-02264: name already used by an existing constraint
Import terminated successfully with warnings.

由报错可知，是由于“PK_PAY_CARD_INFO”主键已经存在，于是查看那张表占用了主键名
SQL> select table_name,constraint_type,constraint_name from user_constraints where 
constraint_name='PK_PAY_CARD_INFO';
TABLE_NAME                     C CONSTRAINT_NAME
------------------------------ - ------------------------------
PAY_CARD_INFO_20160504         P PK_PAY_CARD_INFO

查看数据导入情况，和上面的导出的数据量一样，在与客户沟通得知，客户只需要数据，主键约束会自己重建。
SQL> select count(*) from PAY_TRADE_LIST;

  COUNT(*)
----------
   8229042

SQL> select count(*) from PAY_CARD_INFO;

  COUNT(*)
----------
    329223

SQL> select count(*) from PAY_CUST_INFO;

  COUNT(*)
----------
    329238

 

转载于:https://www.cnblogs.com/houzhiqing/p/5509098.html