RE: Questions re datapump import

From: Mark W. Farnham <>
Date: Thu, 19 Nov 2009 09:10:36 -0500
Message-ID: <>

I'm a little confused by your goal. If you want to "revert" to an entire earlier version of a schema, why do you NOT want to drop before re-importing?  

Are your developers not allowed to change schema objects? For example, might they add a column to a table or drop an index?  

Without looking in the utilities manual I frankly cannot remember whether there is an option to replace object in their entirety (as opposed the the options about rows mentioned earlier in the thread.)  

But if you want back the prior versions of LIBRARY, TYPE, SEQUENCE, etc., then it seems to me it would be far easier to either drop the entire schema or drop the desired object types with a script generated from the dictionary. Then datapump wouldn't have to go into an error interpretation mode.  

So are you trying to essentially revert to a previous generation that you exported or not? I would think you would have in mind two scenarios: Wholesale reversion (in which case dropping before importing seems most straight forward), and selective object reversion (in the event your developer wants to undo changes to a limited set of objects without losing progress on changes to other objects.) In that case you would probably do well to drop the individual objects the developer(s) wanted to revert and subsequently run import specifying those objects. I suppose if the developers wanted to keep progress on just a few objects, then object selective special exports, wholesale drop, wholesale reversion import, selective drop and wholesale import of the selective import would be less total work for the computer (but a few more steps for you, so I don't know which would be a better scenario because I don't know the relative costs.)  

But that is just the need I usually see. Your goals may be different. But from what you've written I don't understand the value of avoiding the drop and complicating the import.  

Specifically in the case you cite below I'm pretty sure you are left with the incumbent versions, not the ones you attempted to import.  


From: [] On Behalf Of John Dunn
Sent: Thursday, November 19, 2009 8:35 AM To:
Cc:; Subject: RE: Questions re datapump import  


That works for tables, but I am also getting "already exists" errors on other objects (LIBRARY, TYPE and SEQUENCE, PACKAGE, FUNCTION, etc etc)

Does this means these objects have not been imported?

How can I ensure these objects are replaced?

ORA-31684: Object type PACKAGE_BODY:"PRODUCER"."SERVER_JES_MONITOR" already exists

<snipped and minus nl's above>

Received on Thu Nov 19 2009 - 08:10:36 CST

Original text of this message