Re: Moving tables/indexes from one tablespace to another

John:

    alter table <table_name> move tablespace <tspace> is very fast and I highly recommend using it.

However.. Be very careful with your stats. It's kinda a catch-22. After you move the table to the new tablespace you will not have stats on the table.
You'll also need to rebuild your indexes. =20

You can compute stats on your table first, as long as you don't set cascade to true. (Error 20000 index is unusable state)

If you don't have stats on your table and you rebuild the index with compute statistics, the compute on the index will generate some table stats for you. You'll get some stats on your table that might not be what you want. In particular, the avg_row_len will be set to a default of 100.

If you have a very large table that takes a long time to generate stats, you might want to export the stats before you move the table, then import after your move.

If the table is smaller, then this (I believe) works: alter table move tablespace, compute stats with cascade=3Dfalse, alter index rebuild compute stats.

HTH!
Barb

On 6/14/05, John Dunn <jdunn_at_sefas.com> wrote:
> Is there any easy way to move tables and indexes from one tablespace to
> another, other than dropping and recreateing?
>=20
> John
>=20
> John Dunn
> Product Consultant
> Sefas Innovation Limited
> Direct Dial + 44 (0) 117 915 4267
> www.sefas.com
>=20
> PLEASE NOTE SEFAS INNOVATION ARE MOVING THURSDAY 23rd JUNE 2005 TO:
> Sefas Innovation Ltd, CityPoint, Temple Gate, Bristol BS1 6PL, UK.
> Tel: +44(0) 117 373 6114
> Fax: +44 (0) 117 373 6115
> NEW DIRECT DIAL: +44 (0) 117 373 6122
>=20
>=20
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l