想变强的DWB
2022-06-20 23:20
采纳率: 0%
浏览 8

Oracle下impdp导入报错

在Linux环境下使用impdp遇到报错ORA-00955: name is already used by an existing object
测试表语句,没有任何约束、主外键
  CREATE TABLE "WMS_LP_ZHONGCHE"."DWBTEST2_BAK" 
   (    "ID" NUMBER(*,0), 
    "REMARK" VARCHAR2(100), 
    "ISMODIFY" NUMBER(5,0)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" 
同一张目标表,已存在,测试了table_exists_action的几种方式,其中replace、append正常,truncate一直报错
#truncate
[root@WMS-DB ~]# impdp 用户名/密码@orcl directory=AUTO_BAK dumpfile=20220620.dump tables=DWBTEST2_BAK table_exists_action=truncate

Import: Release 11.2.0.4.0 - Production on Mon Jun 20 21:55:42 2022

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "WMS_LP_ZHONGCHE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "WMS_LP_ZHONGCHE"."SYS_IMPORT_TABLE_01":  wms_lp_zhongche/********@orcl directory=AUTO_BAK dumpfile=20220620.dump tables=DWBTEST2_BAK table_exists_action=truncate 
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39120: Table "WMS_LP_ZHONGCHE"."DWBTEST2_BAK" can't be truncated, data will be skipped. Failing error is:
ORA-20001: ????Truncate???????
ORA-00955: name is already used by an existing object
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"WMS_LP_ZHONGCHE"."DWBTEST2_BAK" creation failed
Job "WMS_LP_ZHONGCHE"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at Mon Jun 20 21:55:43 2022 elapsed 0 00:00:01
#append
[root@WMS-DB ~]# impdp 用户名/密码@orcl directory=AUTO_BAK dumpfile=20220620.dump tables=DWBTEST2_BAK table_exists_action=append

Import: Release 11.2.0.4.0 - Production on Mon Jun 20 21:55:34 2022

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "WMS_LP_ZHONGCHE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "WMS_LP_ZHONGCHE"."SYS_IMPORT_TABLE_01":  wms_lp_zhongche/********@orcl directory=AUTO_BAK dumpfile=20220620.dump tables=DWBTEST2_BAK table_exists_action=append 
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "WMS_LP_ZHONGCHE"."DWBTEST2_BAK" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "WMS_LP_ZHONGCHE"."DWBTEST2_BAK"            6.398 KB      37 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "WMS_LP_ZHONGCHE"."SYS_IMPORT_TABLE_01" successfully completed at Mon Jun 20 21:55:35 2022 elapsed 0 00:00:01
#replace
[root@WMS-DB ~]# impdp 用户名/密码@orcl directory=AUTO_BAK dumpfile=20220620.dump tables=DWBTEST2_BAK table_exists_action=replace

Import: Release 11.2.0.4.0 - Production on Mon Jun 20 21:38:31 2022

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "WMS_LP_ZHONGCHE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "WMS_LP_ZHONGCHE"."SYS_IMPORT_TABLE_01":  wms_lp_zhongche/********@orcl directory=AUTO_BAK dumpfile=20220620.dump tables=DWBTEST2_BAK table_exists_action=replace 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "WMS_LP_ZHONGCHE"."DWBTEST2_BAK"            6.398 KB      37 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "WMS_LP_ZHONGCHE"."SYS_IMPORT_TABLE_01" successfully completed at Mon Jun 20 21:38:32 2022 elapsed 0 00:00:01

查询了网上的相关内容,都说truncate是清空目标表数据,然后从原表中写入,但是不清楚为什么清空表数据会提示对象已存在,我查询了对象表,同名的只有我这个目标表本身

SELECT * FROM all_objects WHERE object_name = 'DWBTEST2_BAK';

img

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

1条回答 默认 最新

相关推荐 更多相似问题