Re: Upgrade 8.1.6 -> 9.2 via exp/imp

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 !

> 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 !

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.

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.

-- 
Delete the obvious bit from my email address to reply by email.