lots of chaining

[mallard]

Isn't it true that alter table move only works with index-organized tables?

[marist89]

MOVE TABLE works with all tables EXCEPT IOT's.

[jdorlon]

JDoyle,

If your table is not very large and you are on 8i, I think the 'alter table move' option is better.

Your method is good and it does work. Prior to 8i, there was
no 'alter move' command, so the only options were your suggestion and exp/imp.

The only problem with your suggestion is that it can put a lot of blocks on freelists, which basically means that now your table is somewhat fragmented (which is not as bad as chained rows, I don't think).

But if your table is very big, you might not have the time to do an 'alter move', then rebuild all of the indexes, then re-analyze your tables, so that's when your suggestion can be handy.

-John

[jdorlon]

Oh yeah -

2 more steps you might want to include in JDoyle's method :

Disable any FK constraints and triggers on your table
(if there are any) and then enable them again afterwards!

:-)

-John