Re: imp issue...very confusing!

From: Paul Drake <discgolfdba_at_yahoo.com>
Date: Tue, 13 Jul 2004 09:03:10 -0700 (PDT)
Message-ID: <20040713160310.46148.qmail@web20411.mail.yahoo.com>

Denham Eva <EVAD_at_TFMC.co.za> wrote:
> Hello Gurus,
>
> Here is the situation, I have a Oracle 7.3.4 dump,
> which am importing
> into a Oracle 9.2 server, both Windows platforms.
> I created a new user "schema" for the data, with
> it's own tablespace
> etc.
> No fancy imp parameters other than fromuser, touser.
>
> Here is my problem, the fromuser does already exist
> on the server with
> its data from the 7.3 server. So the import is now
> happily importing the
> data but into the fromuser tablespace and not into
> the touser's
> tablespace as I had wanted it to. The touser is now
> the owner, but
> naturally the tablespace of fromuser has began to
> grow huge.
>
> Many Thanks
> Denham

As one would expect, the dump file contains statements to recreate the objects as they exist in the source database.
If you wish to change attributes, such as tablespace for a table, tablespace for an index, you will have to either pre-create the object or alter the object after import.

<token_unix_comment>
one can run strings against the dump file and see that this is true
</token_unix_comment>

<token_rtmp_comment> (read the man page
imp help=y
</token_rtmp_comment>
(pay particular attention to the INDEXFILE option)

your best bet is to grab a copy of toad, have it create the create table scripts with no indexes, no constraints, no triggers, edit that to the desired tablespace(s) (or create it with no storage parameters) and pre-create the user, assign it the desired default tablespace (and roles, privs) and run the create table script prior to the import. you have now specified the tablespace for the tables, overriding the DDL in the dump file.

This routine is also required if you need to get around ORA-907 errors if you were exporting from 8.1.7.

if you also wish to move the indexes, then do not import the indexes and constraints, and create them afterwards.

hth.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Jul 13 2004 - 10:59:53 CDT