Re: 9iR2 exp -> 10g imp

From: Noons <>
Date: Thu, 12 Jun 2008 01:59:58 -0700 (PDT)
Message-ID: <>

On Jun 12, 4:17 am, Arch <> wrote:
> >FWIW - I always pre-create the tablespaces and users on the new
> >database, then just import the relevant schemas (from a full=y export
> >from the source db).
> >Which just reminded me, one of the sessions at OOW 07 was about using
> >exp/imp for upgrading, and some pitfalls and things to watch out for
> >when upgrading this way. I forget what they (things to watch for) were,
> >perhaps I can find it on my DVD set..
> Pardon my butting in here, but if you have info on pitfalls in using
> this technique to upgrade, I would be very interested. (I'm about to
> do exactly that)

watch out for grants, procedures and invalid objects.

Assuming a copy through exp/imp being used to land an older db into a new release framework, my progress steps are as follows, with a manual thorough check step in between each:

1- pre-create all storage (tablespaces), adjust controls for new release, whatever they may be.
2- import schema by schema (owner by owner, NEVER sys or system) 3- do the import in stages: create tables only, no data (rows=n, indexes=n, grants=n)
4- then disable all created triggers (if any), and load the rows (rows=y, indexes=n, grants=n)

5- then do all indexing (rows=n, indexes=y, grants=n)
6- then repeat for all other schemas
7- last, do a grants=y,indexes=n, rows=n import to get all the grants.
8- last, manually enable anything I've disabled, including
constraints, triggers and such.

Works fine for most cases. Careful with things like FGAC and AQ/replication, those require some careful thinking and changes to above steps. Received on Thu Jun 12 2008 - 03:59:58 CDT

Original text of this message