Re: Moving schemas between tablespaces

I guess I should go and beat myself with birch twigs or something, because my suggestion was so 'obvious' that it doesn't work. Offlining the tablespace is not sufficient, because import still knows that it exists -it just gets upset that it is offline. That'll teach me not to rely on an increasingly frail memory of having performed tests when I clearly haven't.

If you could DROP the original tablespace, that would do it (but I suspect this is not really an option).

So you are reduced to either 'alter table blah move tablepsace newone' if you have 8i or above. (Indexes will need to be re-built afterwards). This is the simplest method.

Or, you could create the tables by hand in whatever tablespace you fancy, and then run import with ignore=y so that it just gets on with populating the tables that already exist. If you investigate some of the other import options (for example indexfile=blah.txt), you can get import to generate a text file containing the sql statements required to create segments -edit that to specify the new tablespace, then run it as a standard sql script. Then, again, run import with ignore=y.

There's always the option to create table newblah as select * from oldblah -that way, you don't need to get involved with import and export at all, not even to change the schema owner. 'create table scott.newblah tablespace newone as select * from mary.oldblah' will do the job in one hit (provided you have 'create any table' privilege). Trouble is, you then have to drop the old table by hand, rename the new copy to be the old name, and then rebuild all indexes, and re-apply all constraints, and re-grant all permissions. Bit of a mess if you've got lots to do.

Still, you've got a number of *real* options now.

Apologies for misleading you before.

Regards
HJR Howard J. Rogers <howardjr_at_www.com> wrote in message news:3a70ad6e_at_news.iprimus.com.au...
>
> <mikeg12321_at_my-deja.com> wrote in message
> news:94q926$9is$1_at_nnrp1.deja.com...
> > Heres what I want to do:
> > I want to create a new user and give him his own
> > tablespace to store data. I want to export a
> > schema from another user and import that schema
> > into the new user's tablespace (so the new schema
> > is owned by the new user). When I try to do this,
> > the import always imports into the original
> > tablespace, no matter that I set the touser=new
> > user, and the new user's default tablespace is
> > the new one I made for him. Any suggestions?
> >
>
>
> Standard import behaviour is to import into the same tablespace as the
> original object was in. And if it can't find an exactly-matched
 tablespace,
> it will use the User doing the import's default tablespace.
>
> So the solution should now be obvious: persuade import that the original
> tablespace doesn't exist (anyone for an 'alter tablespace blah
 offline'???),
> then run import as a User whose default tablespace has been changed to be
> where you are actually after the data going ('alter user blah default
> tablespace xxx'). When all is done, online the original tablespace.
>
> What you have discovered is that there is no relationship between an
> object's schema and it's tablespace (and I wish that all those who keep
> posting as though the two were the same thing would accordingly take
 note!!)
>
> Regards
> HJR
>
>
>
>
> >
> >
> >
> > Sent via Deja.com
> > http://www.deja.com/
>
>
Received on Thu Jan 25 2001 - 17:10:25 CST