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: Howard J. Rogers <>
Date: Fri, 12 Jul 2002 17:45:15 +1000
Message-ID: <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 so. Then there's the issue of how you propose to export a 120Gb database... that dumpfile is going to be pretty big! Yes, there are ways around that on Unix (pipes etc), but it's not exactly "easy".

By contrast the migration utility does an in-place migration of an existing 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 don't 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 fresh 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 database, 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.

HJR Received on Fri Jul 12 2002 - 02:45:15 CDT

Original text of this message