Re: Questions on moving big tables

Linda Lee <goforticket_at_yahoo.com> wrote:
>I have a few tables that each have several thousand extents with
>several hundred MB data. I plan to move the big tables into a new and
>separate tablespace.
>
>1. When I use following code to create the new LMT tablesapce, should
>I use AUTOEXTEND or not? what's the difference? What are the advantage
>and disadvantage in using AUTOEXTEND?
>
>create tablespace prod2
>datafile 'O:\ORACLE81\OWXE2\ORADATA\ORAJDE\proddtat02.dbf' size
>1536064K EXTENT management local uniform size 5120K;

Autoextend is generally considered a recipe for trouble. You should monitor your database and manually modify it when objects grow too large (as you're doing now). Autoextend will cause the datafile to grow automatically, which causes unpredictable usage of disk.

>2. When I move big table into the new LMT tablespace, should I have a
>storage clause with it?
>
>alter TABLE bigTable move tablespace prod2 storage(?);

With uniform extents, you can't modify the extent options of an individual segment in a LMT. You may want a storage clause for other reasons (such as freelists).

>3. The old tablespace that currently has bigTable in it is a
>Dictionary tablspace. If its size is 4508 MB and the size of bigTable
>is 1200 MB, how much disk space can I reduce the old tablespace after
>the move? (1200MB ?) Should I use "alter database datafile ... resize
>3308M" to reduce the old tablespace size? Or are there other better
>methods?

Look at how much space is left in the tablespace after the move, then decide.

>4. Is it a good practice to put big tables into a dedicated
>tablespace?

Put tables in a tablespace suitable for their size, so large tables go in tablespaces with large extent sizes and small tables in tablespaces with small extent sizes.

You're planning to have your large table take 240 extents, while not a major problem in itself, it's not that good. Given you're reorganising anyway, possibly you should consider creating another tablespace with larger extents.

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/