Re: Reorganizing tables (next extents)

"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

Convert to Locally managed tablespaces with auto. No need to muck with the minimum extent myth.
Jim Received on Wed Apr 28 2004 - 21:01:22 CDT