Re: Reorganizing tables (next extents)

Hi Flame,

Well, It is not necessary to move to separate tablespace and move them back again. If you have enough space then you can move it the same tablespace itself. Anyway it is up to you to decide,which approach you are going to follow.

It does not matter, if you have 100's of extents in uniform size (even for Dictionary Managed Tablespace) then it is OK.

You have to rebuild all your indexes after you move the tables. You may face problem while moving tables with LONG COLUMN.

> next_extents := (user_tables.blocks * db_block_size - initial)
> / no_of_parts;

How will you arrive the figure for initial ?

What do you mean by no_of_parts ? No.of Partitions ?

Rgds,
Dhana

FlameDance <FlameDance_at_gmx.de> wrote in message news:<c6p9ui$eoo$04$1_at_news.t-online.com>...
> Hi everyone,
>
> I have a question, please.
>
> I want to reorganize the tables of a database. They have grown since
> Oracle 7, some have huge inital extents (up to 1.7GB), some consist of
> many parts, some both. (not my fault, don't blame me ;-) )
>
> The database is Oracle 8.1.7 (in 8.1.0 compatibility mode).
>
> My thought is to enforce analyzation of all tables in the important
> tablespace. Then create a temporary tablespace named interim. Then for
> each table issue a
>
> ALTER TABLE <name> MOVE TABLESPACE interim ...
>
> command with new storage parameters. When all tables are moved to the
> temporary tablespace and the original tablespace is empty, I'd move them
> all back.
>
> I'd give a small initial to avoid problems with export/import.
> Then I'd want to determine a good next extents size. It shouldn't be too
> large but I don't want much fragmentation either. (Assuming that there's
> not much free space in the tables storage) my idea is to calculate it
> per table as
>
> next_extents := (user_tables.blocks * db_block_size - initial)
> / no_of_parts;
> or, if that value is small, just as a fixed value of maybe 100KB.
>
> If my idea is ok, I'd only need some ideas of what a good number of
> parts would be.
>
> Thanks for suggestions and any other input,
> Stephan
Received on Thu Apr 29 2004 - 05:28:31 CDT