Re: Moving tables/indexes from one tablespace to another
- From: Barbara Baker <barb.baker@xxxxxxxxx>
- To: jdunn@xxxxxxxxx
- Date: Tue, 14 Jun 2005 08:38:02 -0600
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@xxxxxxxxx> 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
> --
> //www.freelists.org/webpage/oracle-l
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
