Re: Importing to a different instance on same server

Slightly different question!

Change the user who's going to be doing the import so that their default tablespace points to where you want the objects to end up.

alter user fred default tablespace X;

Also change that user so that their quota on the tablespace where the objects would naturally or normally end up is zero.

alter user fred quota 0 on Y;

Run import, and import *should* decide to create the objects in X, rather than Y where the quota limits would be breached by doing so (I say "should" only because last time I tested this I had inconsistent results).

Try it and let us know how you got on, huh?

Regards
HJR

--

Oracle Resources : http://www.geocities.com/howardjr2000
========================================


"Roger Jismalm" <Roger.Jismalm_at_removethisword.unisys.com> wrote in message
news:9rlh4d$1r1$1_at_si05.rsvl.unisys.com...


> I thought we were running multiple instances but we are only running one.

>

> So the question is how do I import back into a different tablespace?

>

> The exp/imp has to be done in the same database. We are using only one

> database called OBJ2000 which contains 3 tablespaces called OBJPROD,

> OBJTRAIN and OBJDEV.

>

> Different login/passwords are used for each.

>

>

>

> "Howard J. Rogers" <howardjr_at_www.com> wrote in message

> news:3bdcdf87$0$9826$afc38c87_at_news.optusnet.com.au...

>

> > I'm confused.  What do you mean "it reimported back into the production

> > database".  Do you mean that when you ran import, it tried to load all

the


> > objects back into the production database?  If so, that would be

because,


> > like everything else, when you don't explicity tell Oracle or its

> > applications what to connect to, it uses the instance named by

ORACLE_SID


> as

> > the default connection.

> >

> > So to import into the second database, you either have to re-set your

> > ORACLE_SID, or you have to explicitly tell import what database you want

> it

> > to connect to:

> >

> > imp system/manager_at_TEST full=y etc etc etc.

> >

> > For that to work, of course, you need a tnsnames.ora with the correct

tns


> > alias set in it.

> >

> > Regards

> > HJR

> > --

> >

> > Oracle Resources : http://www.geocities.com/howardjr2000

> > ========================================

> >

> >

> > "Roger Jismalm" <Roger.Jismalm_at_removethisword.unisys.com> wrote in

message


> > news:9rikkn$ihr$1_at_si05.rsvl.unisys.com...

> > > What is the best way to import a production database export to a

> training

> > > instance on the same server?

> > >

> > > When I first tried to import everything seemed OK, but it reimported

> back

> > > into the production database.

> > >

> > > After dropping the production instance the import to the training

> instance

> > > worked OK.

> > >

> > > However this is not a good workaround as production will soon go live!

> > >

> > >

> > >

> > >

> >

> >

>

>