Re: moving tables from to new tablespace.

Glen,

alter table move is several times faster than a import. You can also do them in parallel to make this take less time. You do not have to worry about disabling constraints or broken triggers. The big side effect is indexes. Indexes must be rebuilt after a alter table move (alter index rebuild ) . This is what takes time. I still say this will be much faster than a import. Now here is where you get both theory and practice. In theory indexes can be reorged in parallel in practice it has issues. I have seen some strange issues come up with the amount of space allocated it sometimes much higher than what you start with. Also if you are looking at attempting to reallocate storage of tables/indexes and save some space while you do this. This is possible but index rebuild takes causes the index to take up more space (in some cases) than the original. You may want to consider moving to LMT if you can while you do this. It makes this process much faster.

HTH
Frank P.

"Glen A Stromquist" <glen_stromquist_at_nospam.yahoo.com> wrote in message news:Njf%9.71834$c41.2292908_at_news2.telusplanet.net...
> I am replacing a machine with a small database on it, so far I have
created
> a "blank" database on the new machine with just the tablespaces matching
> the one I'm going to replicate. The twist to this is that on the original
> database the tables somehow wound up in the index tablespace when it was
> built by the people who put the application in place.
>
> Whats the best way for me to get this rectified? I was thinking just
import
> it then issue a alter table move tablespace for all the affected tables,
> but I've never had to use this before, I am assuming that once this was
> done I'd have to either rebuild or recreate all the indexes in the schema
> as well?
>
> Or should I create all of the tables in the new schema first, then import?
> I actually did a quick test run of this, but got dozens of constraint
error
> messages, so I'd also have to write a script to disable all the
constraints
> before doing the import.
>
> Time is of the essence for this, as this is supposed to be a 24-7 db and
> although I picked a "quiet" time to do this, I want to keep it as short as
> possible, which means taking an export from the one in place now just
> before I take it offline and importing it into the new one.
>
> anyone have any better ideas?
>
> TIA
>
>
Received on Tue Feb 04 2003 - 04:19:56 CST