在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';