Re: How to change object's tablespace ?

Not quite.

As Sybrand pointed out, you can just do the moves. After that, you have to rebuild the indexes because they go invalid by the move. With the rebuilds you can allocate them in the correct tablespaces.

Remember to move the partitioned segments partition by partition.

Martin

Sybrand Bakker wrote:
>
> If you have 8.1.6 and the compatible init.ora parameter is set to an
> appropiate value
> (guys today I discovered I can't use any 8i feature in a 8.1.7 database
> because some smart ass has set the compatible parameter to 8.*0*.5!!!!!, and
> there's really no need for that)
> you can simply issue a series of alter table move.
> In that case you can simply
>
> for t in (select table_name from user_tables where tablespace_name =
> 'SYSTEM') loop
> execute immediate 'alter table '||t.table_name||' move '||<target
> tablespace>;
> end loop;
> end;
> /
>
> And that's it!
>
> Hth,
>
> Sybrand Bakker, Senior Oracle DBA
>
> "Djuro D" <Djuro.Dretvic_at_Infodom.hr> wrote in message
> news:9omunv$gb2$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
> > >
> > > >
> > >
> > >
> >
> >
> > "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 Sat Oct 13 2001 - 02:11:38 CDT