关于impdp导入不能替换procedure以及package问题

woihzh

在expdp和impdp过程中，无意间发现expdp按某个schema导出后，然后对此schema下的procedure做个修改，然后再用导出的dmp导入，发现即使用了参数 table_exists_action=replace，也只是对表做了替换，而存储过程以及包等却没有被替换成expdp导出的内容，想问有谁遇到过这个问题？想知道这个算10g的一个bug还是有其他参数呢？

am196

是不会替换，把无效的重新再编译一下就OK了。

woihzh

am196 发表于 2013-1-28 13:38
是不会替换，把无效的重新再编译一下就OK了。

主要是，如果我导出来之后，然后我把库中的某个procedure修改了，但是哪天我想恢复成以前的procedure，这时候我用impdp导入，可是却不会替换成以前的，而是依然是修改完的内容。

Maclean Liu(刘相兵

连接到:
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.

woihzh

Maclean Liu(刘相兵 发表于 2013-1-28 13:57
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partit ...

谢谢！了解了