Reorganizing tables (next extents)

From: FlameDance <FlameDance_at_gmx.de>
Date: Thu, 29 Apr 2004 00:07:20 +0200
Message-ID: <c6p9ui$eoo$04$1@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 Wed Apr 28 2004 - 17:07:20 CDT