Re: Import Assistance

From: Paul Drake <drak0nian_at_yahoo.com>
Date: 4 Oct 2003 20:52:38 -0700
Message-ID: <1ac7c7b3.0310041952.1f985c32@posting.google.com>

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<3f7f3c0d$0$28898$afc38c87_at_news.optusnet.com.au>...
> Ste wrote:
>
> > Hi,
> >
> > Question about ways of import:
> >
> > When I have exp.dmp from one db and import it into another db with
> > different index tablespace, can I use sed or awk to change the index
> > tablesapce name in the exp.dmp file, then import the data and index with
> > proper index
> > tablespace at the same time? Have anyone done it before?
> >
> > TIA,
> > Ste
>
> You can't muck around with the export file. It's not a text file, and
> editing it will corrupt it.
>
> If you wish to change the tablespace into which objects get imported, there
> are a number of workarounds that Oracle supports.
>
> For indexes, for example, you can run import with rows=n and indexes=n (so
> nothing actually gets imported) but with indexfile=name_of_file.txt ...that
> will cause import to generate a text file containing a script which, if
> run, will create all your indexes for you, from scratch. And since the
> indexfile *is* a text file, you can edit that to your heart's content,
> including changing the tablespace names. Once edited, you invoke it as you
> would any other SQL script -in SQL*Plus, using the "@" command.
>
> Regards
> HJR
a slightly less elegant solution, that would work if you only want to put the indexes in one tablespace would be to:

import the tables and rows, but not the indexes (or constraints).
alter the default tablespace for the user to the intended index tablespace.
alter the quota for the user such that it no longer has any additional space in the tablespace that you do not wish for indexes to be created in.
alter the user's sys_privs so that it does not have "UNLIMITED TABLESPACE" either directly or via a role.
import again, allowing the indexes to be created (and constraints), ignore=y, rows=n.

its not pretty, but it might get you where you want to be.

Howard's solution is much better than this solution, in that you could:
1. specify multiple tablespaces, in the event that some segments are much larger and belong in a tablespace with say 8 MB extents.

2. specify NOLOGGING in the index creation.
3. alter your storage parameters (remove them actually)
4. have your proper DDL re-create script already.

hth.

Pd Received on Sat Oct 04 2003 - 22:52:38 CDT