Re: alter table ... move

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 14 Feb 2003 00:02:41 +1000
Message-ID: <6pM2a.46767$jM5.117045@newsfeeds.bigpond.com>

"Stephan Bressler" <stephan.bressler_at_siemens.com> wrote in message news:b2g1r8$a9n$1_at_news.mch.sbs.de...
> Hi there,
>
> I'm about to reorg some of the tables (and indexes) in our database
> (9.0.1.3).
>
> How about using the "alter table ... move" instead of exp/imp?
>
> What I know so far is:
> - affected tables are locked for dml (no problem)
> - indexes become unusable (no problem, they'll be rebuild)
>
> But what about
> - FK constraints (are they still in place after move)
> - object grants
>

Hi Stephen,

These questions are very easily answered by having a go and seeing for yourself.

I'll give you a clue, you should be pleased with the results ;)

> Are there any other implications to take care of?
>

My recommendation is to have a read of the SQL Reference manual where all this is discussed.

Implications I can think off include not being able to move a table with LONGs, a few things to be aware of with regard to LOBS, ensuring you have sufficient free space if moving to the same tablespace ...

As you're only doing the 'one' move, it's an obvious advantage over exp/imp.

Cheers

Richard Received on Thu Feb 13 2003 - 08:02:41 CST