Re: Tablespaces

Hi Jan,

If you move a table, it must invalidate all associated indexes and they must all be rebuilt.

Think about it.

All the rows have moved to a new location. Therefore all the rowids in the existing indexes no longer point to the correct location.

Checking dba_indexes is a good idea, to ensure you don't miss any of the buggers.

Cheers

Richard

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:ao16ls$hvmd1$1_at_ID-152732.news.dfncis.de...
> You can always check dba_indexes.status . If it is not 'VALID', then
rebuild
> them.
>
> I do not know if _all_ concerned indexes become invalid (that is:
> 'UNUSUABLE' e.g.),
> but that is why I always check data dictionary, and getting next actions
> from it.
>
> Jan
>
> "s Lehaire" <s.lehaire_at_meilleuregestion.com> schrieb im Newsbeitrag
> news:ao12ku$qcb$1_at_reader1.imaginet.fr...
> > ok that's great thanks!
> >
> > But if my indexes are or an other table sapce is there any problems ?
> > Must I rebuild them ?
> >
> > "Steve Ashmore" <sashmore_at_neonramp.com> a écrit dans le message de news:
> > uq81gcn1mph33c_at_corp.supernews.com...
> > > Assuming you are using oracle 8i or 9i and the table doesn't have
> > > any LONG or LONG Raw fields you can use
> > >
> > > alter table move mytable NEWTABLESPACE;
> > >
> > > Note if the table has any indexes you will need to rebuild them after
> the
> > > move.
> > > (Check the SQL REF guide for the complete syntax)
> > >
> > >
> > > Stephen C. Ashmore
> > > Brainbench MVP for Oracle Administration
> > > http://www.brainbench.com
> > >
> > > Author of: 'So You Want to be an Oracle DBA?'
> > >
> > >
> > >
> > > "s Lehaire" <s.lehaire_at_meilleuregestion.com> wrote in message
> > > news:ao0tb7$p3p$1_at_reader1.imaginet.fr...
> > > > Hi,
> > > > How can I move a table on a new tablespace ?
> > > > I know that I can move a index on an other tablespace with this
> command
> > :
> > > > alter index MYINDX rebuild tablespace MYTABLESPACE.
> > > >
> > > > I have some tables on the tablespace system and I want to move them
on
> > my
> > > > tablespace.
> > > > To day for that I use a borring method:
> > > > Create the same table with a new name
> > > > copy the data from the old to the new, drop the old and rename the
new
> > > with
> > > > the old name (is my indexes are ok after this?).
> > > > So if there's a method like the alter index command it would be
great.
> > > >
> > > > Thx for helping me
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Wed Oct 09 2002 - 08:23:32 CDT