Re: Upgrade 8.1.6 -> 9.2 via exp/imp

Norman Dunbar <Norman_at_RE-MO-VE.BountifulSolutions.co.uk> wrote in message news:<pan.2003.10.31.07.57.44.910428_at_RE-MO-VE.BountifulSolutions.co.uk>...
> On Thu, 30 Oct 2003 16:53:02 -0500, Walt wrote:
>
> Morning Walt,
>
> > Right now I'm running into the problem that I created the database with
> > a locally managed system tablespace and the export dump file wants to
> > create the tablespaces as dictionary managed tablespaces. This is
> > apparently illegal and I get an ORA-12913 error.
>
> This is correct. If you have a LM System tablespace then all others have
> to be LM as well. Not a problem as LM is 'the way to go'.
>
>
> > There seem to be two ways out of this dilemma:
> >
> > 1) recreate the database with the system tablespace dictionary managed
> > or
>
> Don't do this !
>

Agreed.

>
>
> > 2) create the tablespaces as locally managed, run imp with ignore =n.
> >
>
> This is the way to go, but ignore=n is not needed at this point.
>
> I presume from the fact that the import wants to create tablespaces, that
> you are exporting with FULL=Y. Don't !

You might consider FULL=Y, ROWS=N in addition to the data export, just because.

>
> Export only the users you need from the production database and make sure
> that you specify COMPRESS=N - you should always do this especially if you
> are importing into a DMT at some future point.
>
> <ASIDE>
> COMPRESS=Y is default and what it does is sums up all the extents in each
> table or index and creates a script to build them anew with INITIAL and
> NEXT set to the total and 50% of the total respectively. This makes life
> difficult if the objects don't exist in the receiving schema because you
> can get 'unable to create INITIAL extent' or 'unable to create NEXT
> extent' errors on import - even if you do have enough free space.
>
> This is because in DMT any free space has to be in one chunk that is big
> enough to hold the space needed. Even if you have 10 free chunks of space
> together, Oracle cannot use them together unless you coalesce the
> tablespace. LMTs simply take your initial and next requirements and give
> you as many uniform extents as necessary to make up the numbers.
>
> NEVER export without specifying COMPRESS=N.
> </ASIDE>
>
> So, what you need to do is this :
>
> Create appropriate tablespaces (LM) to take the data from the production
> schema(s). Create them as LMT with UNIFORM extent sizes.

Actually, if you export with COMPRESS=Y, the import into LMT does whatever the heck it wants. Aside from the COMPRESS issue, it may be reasonable to let Oracle determine the extent sizes with AUTOALLOCATE if you have thousands of tables and don't have time to sort them by size into appropriate uniformed tablespaces. Worked well for me. The warning about COMPRESS=Y and DMT is well founded, but changing a working methodology may have costs, wrong though the foundations of the methodology may be.

>
> Create appropriate users and passwords. Make sure you specify QUOTA 0 ON
> SYSTEM when you create them - otherwise you *might* end up with user
> objects in your system tablespace under certain conditions.
>
> import from your full dump (if you still have it) but specify
> FROMUSER=a,b,c,d and TOUSER=a,b,c,d - that will stop you importing into
> SYSTEM and other users that you may not need.
>
> When importing or exporting, make sure you specify a BUFFER=value. I tend
> to use 1024000 for a starting point.
>
> This will bring in you user data quite happily.
>
>
> Have fun.
>
>
> Cheers,
> Norm.

jg

--
@home.com is bogus.
Saw on TV the forest in the mountains where I used to play when I was
a kid.  It was burning to the ground.