Re: imp-exp tools

In our last gripping episode carl_bruneau_at_my-deja.com wrote:
> Hello Everyone,
>
> In our business - software developper - we have to deal with data from
> all of our clients. Consequently we decided long ago to work with many
> tablespaces on the same server and many users by tablespace. So every
> tablespace logically represent a set of client.
>
> Recently, we upgrade our Oracle server from version 8.0.3 to version
> 8.1.6.
>
> Since then, we have a problem using the imp-exp tools.
>
> Before the upgrade, we were able to copy all the objects from a users
> schema residing on one tablespace to another user residing on another
> tablespace on the same server. Here is an exemple:
>
> exp.exe system/manager owner=user_a file=x.dmp log=y.log direct=y
> consistent=y
>
> imp.exe system/manager fromuser=user_a touser=user_b file=x.dmp
> log=z.log commit=y
>
> We always did that with exp-imp tools using the fromuser and touser
> parametrers and it always worked.
>
> Since the upgrade, we ancounter the imp-00015 error when we are doing
> that kind of move. Oracle says to us that the default behavior of
 those
> tools was to try to copy the data to the same tablespace as it was
> extract with exp and that those tools are not intended for this kind
 of
> move. They almost didn't believe us that we were able to do that on
 our
> old server (but, of course, they didn't want to try to reproduce it).
>
> So, I would like to know if you have to deal with this kind of
 situation
> and how you proceed to do that?
>
> Any suggestions?
>
> Thank's a lot,
>
> Carl
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

You can extract the table/index information from dump file with imp, using the indexfile parameter, edit that file to place the objects into the desired user's tablespaces then run that script through SQL*Plus as the desired user. Then import the data using ignore=y to ignore the create errors generated. This will load the tables/indexes with the desired data.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.