oracle impdp导入覆盖,关于impdp导入不能替换procedure以及package问题

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL> create procedure testproc as begin null; end;

2  /

过程已创建。

expdp system/oracle dumpfile=maclean:proc.dmp schemas=maclean include=procedure:\"\= \'TESTPROC\' \"

create or replace procedure testproc as begin execute immediate 'select 1 from dual '; end;

/

过程已创建。

C:\Users\xiangbli>impdp system/oracle dumpfile=maclean:proc.dmp  include=procedure:\"\= \'TESTPROC\' \"

Import: Release 11.2.0.3.0 - Production on 星期一 1月 28 13:55:47 2013

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

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_FULL_01"

启动 "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=maclean:proc.dmp include=procedure:"\= \'TESTPROC\' "

处理对象类型 SCHEMA_EXPORT/PROCEDURE/PROCEDURE

ORA-31684: 对象类型 PROCEDURE:"MACLEAN"."TESTPROC" 已存在

处理对象类型 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

作业 "SYSTEM"."SYS_IMPORT_FULL_01" 已经完成, 但是有 1 个错误 (于 13:55:48 完成)

C:\Users\xiangbli>impdp system/oracle dumpfile=maclean:proc.dmp  include=procedure:\"\= \'TESTPROC\' \"  TABLE_EXISTS_ACTION=replace

Import: Release 11.2.0.3.0 - Production on 星期一 1月 28 13:55:34 2013

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

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_FULL_01"

启动 "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=maclean:proc.dmp include=procedure:"\= \'TESTPROC\' " TABLE_EXISTS_ACTION=replace

处理对象类型 SCHEMA_EXPORT/PROCEDURE/PROCEDURE

ORA-31684: 对象类型 PROCEDURE:"MACLEAN"."TESTPROC" 已存在

处理对象类型 SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

作业 "SYSTEM"."SYS_IMPORT_FULL_01" 已经完成, 但是有 1 个错误 (于 13:55:35 完成)

文档ORA-31684: OBJECT TYPE ... ALREADY EXISTS WHEN TRYING TO SKIP OBJECTS OTHER THAN TABLES [ID 1323411.1]对该问题有说明

对于如LOAD SEQUENCES/PROC/FUNC/PKGS的对象无法imp replace ，这不是一个BUG，而是本身设计成这样的，今后可能会加强，但是并不在议事日程上

ORA-31684 in this situation cannot be resolved. The original EXP/IMP tools could not replace such objects and EXPDP/IMPDP has been designed the same way.

Two 'Enhancement Requests' exist for the issue :-

Bug.6314742 (15) IMPDP OPTIONS TO LOAD SEQUENCES/PROC/FUNC/PKGS THAT ALREADY EXIST Gen V10202:

Bug.5222236 (15) PRODUCT ENHANCEMENT NEW IMPORT DATAPUMP PARAMETER OBJECT_EXISTS_ACTION Gen V10202

EHRs are filed as 'BUGs' but are not a true BUG. These enhancements are something that

'might' be seen in a future release but Support do not drive this decision. The only method to

ensure such objects are imported would be to remove them from the target database prior to

IMPDP so they are recreated by IMPDP on import.