Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle migration vs Exp/Imp

Re: Oracle migration vs Exp/Imp

From: Richard Foote <>
Date: Fri, 12 Jul 2002 19:40:05 +1000
Message-ID: <cnxX8.33467$>

Just to add a couple of points.

I've asked this question several times in the past and surprisingly (to me anyway) the more common answer is the exp/imp method.

This reason for this appears to be that emp/imp is much like an old friend. Most DBAs are comfortable with it, knows what it does, how to use and so on. The migration utility / manual migration is all a bit "spooky" and so is often not used. Now I'm not saying this is a legitimate reason but it is a common one (the fear of the unknown, comfort factor etc). One advantage of using exp/imp is that the database is reorganised as a result (tablespaces become defragmented, HWMs are reset, indexes rebuilt etc.). This could be of benefit (or it might not).

The migration utility / database convert is much faster as Howard has said and that it's key advantage. You must take a backup mind you (else Clint Eastwood type voices start in your head "Do you feel lucky punk, DO YOU !!") and some other pre work so that needs to be factored in.

Read up on all the possible methods (and posting here is a good start) and pick the one that's right for you.


"Howard J. Rogers" <> wrote in message news:agm1d3$hqh$
> "Jayaraman Ashok" <> wrote in message
> > Hi
> > Under what circumstances do we have to follow the export/import method
> > of upgrading or migrating a database ? When is it advisable to use
> > odma and run the migration utility of oracle or do the
> > upgrade/migration manually? I think the easiest method is
> > export/import for any platform and version. Kindly share your
> > opinion/knowledge in this regard.
> >
> > Cheers,
> > Ashok
> It's certainly *not* the easiest method. For a start, assuming you don't
> trash your old database the moment it's been exported, your machine needs
> the resources to run two databases. Second, export does selects and import
> does inserts -neither of them the fastest operations under the sun. On any
> reasonably-szed database, the export can take hours, and the import more
> Then there's the issue of how you propose to export a 120Gb database...
> dumpfile is going to be pretty big! Yes, there are ways around that on
> (pipes etc), but it's not exactly "easy".
> By contrast the migration utility does an in-place migration of an
> database. It's not especially fast, either, but at least there's only one
> database in existence, you are modifying the database in situ, and you
> have to read/write all the data twice.
> And from that distinction flows the usual recommendations: use whichever
> method is appropriate. If you can't afford downtime on the production
> database, export-import is a good bet, because you can prepare the new
> database whilst the old one is still in use (presumably not permitting
> DML, however). If it's a small database, export-import is also a good bet,
> because the dumpfile sizes aren't a consideration. If it's a large
> or you've not the resources to have two versions of the database, or
> downtime is not a consideration, then the migration utility is the better
> choice.
> Regards
Received on Fri Jul 12 2002 - 04:40:05 CDT

Original text of this message