Re: How to change object's tablespace ?

Thanx a lot.

Regards Djuro.

"Asaf Shoval" <shovala_at_netvision.net.il> wrote in message news:9oo71n$em4$1_at_news.netvision.net.il...
> Djuro,
>
> If u are planning using exp-imp to do the objects tablespace relocation,
it
> will
> be the best and fastest way of doing that.
> You started a good step by creating a new user with default tablespace as
u
> needed, but the problem is that u gave the user the "resource" role, which
> allow him to write on every tablespace.
> Next time, create the user with connect, create table, create procedure
etc
> and give him a "quota unlimited" on the target tablespace, and "quota 0k"
on
> the
> source tablespace. and then when the import will try create those objects
on
> the
> old tablespace - it will fail cause quota is 0k, and it will create the
> objects on
> the default tablespace.
>
> Asaf.
>
> Djuro D <Djuro.Dretvic_at_Infodom.hr> wrote in message
> news:9omps7$c91$1_at_sunce.iskon.hr...
> > Sorry, next time I'll try to be more percise.
> >
> > Detail's of a problem:
> >
> > 1. My database is running on RDBMS version 8i (8.1.6) for Wndows
NT/2000.
> > I'm planning to upgrade it on version 8.1.7. (Release 3)
> > 2. The operating system is Windows 2000 Advanced Server (Service pack
2).
> >
> > My problem is how to move all user object from one tablespace to
another,
> > not one by one. I have approximately 800 object's.
> > The hardest and longest way is to recreate the object and move their
data
> > (one by one), but is there another(better) way to reallocate them.
> > I tried with EXPORT/IMPORT. (FROMUSER=JOHN TO USER=JOHN). User John has
> > default tablespace "SYSTEM" in source database, but the same user(John)
> has
> > default tablespace "USERS" in targeted database. After the import
> complete,
> > the object's remain in the same tablespace. I have the same situation as
> > before. My goal is that all created John object's are in the USERS
> > tablespace.
> >
> >
> > Regards, Djuro.
> >
> >
> >
> >
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > news:tqrs9d8pi05v3d_at_news.demon.nl...
> > >
> > > "Djuro D" <Djuro.Dretvic_at_Infodom.hr> wrote in message
> > > news:9okpjf$rqa$1_at_sunce.iskon.hr...
> > > > Hi,
> > > >
> > > > What is the best way to move object (tables, indexes etc. ) from one
> > > > tablespace to another tablespace ?
> > > >
> > > > Here is my problem. Our developers put their object's in system
> > > tablespace.
> > > > I have to move them into the useres tablespace.
> > > > What is the easiest solution ?
> > > >
> > > > Thanks in advance.
> > > >
> > > > Regards, Djuro
> > > >
> > > >
> > > >
> > >
> > > Tne answer is partly version specific. Please *always always* include
> > those
> > > 3 digits and two dots. No one in this group has a crystall ball and/or
> > will
> > > assume you are running with the latest version.
> > > From 7.3 onwards indices can be rebuild using alter index rebuild
> > > In 8i a table can be moved with alter table move
> > > Before 8i this would require either a CTAS (Create table as select)
and
> > some
> > > fiddling, or export / import.
> > >
> > > Regards,
> > >
> > > Sybrand Bakker, Senior Oracle DBA
> > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Wed Sep 26 2001 - 06:30:40 CDT