Initial Extent sizes on tables

[nabaig]

I have just taken over a 10GB database and on analyzing a few key tables, I find that the intial extent sizes range from 50-70MB. It is an OLTP application and I think that such big extent sizes are not good.

How do I go about fixing this problem? Export table and import it into a new table withh appropriate extent sizes? What happens to the associated indexes/views?

Thanks,
Nizar.

[julian]

Big extents, but are the tables huge? EXP/IMP would do the trick. Rebuild the indexes afterwards. Don't worry about the view, only recompile them after the import of the tables.

[pando]

what version is it?

[sundaju]

Make sure that the privileges assigned on the table to various users are also taken care of while trying to import.

Cheers

[nabaig]

Version 8.1.6

Nizar

[pando]

then you dont need imp/exp, you can use

create a new tablespace aaaa

alter table xxx move tablespace aaaa storage(initial 4M next 4M pctincrease 0)

then mnove back to original tablespace, this is to avoid fragmentate your original tablespace, if it´s LMT then you can dmove the table in same tablespace

[nabaig]

Pando,

Thanks for your reply. I will appreciate your help on these questions also. We are planning to bring this database down for 2-3 days for reorg maintenance.

First:

1. Will changing from dictionary managed to LMT's have any response in performance?

2. And can I do this by creating LMT's and importing all the tables from my current dump?

3. Any watchits or issues to be considered while doing this change?

Thanks,

Nizar

[pando]

lmt is faster yes, the space management has almost zero overhead

you can exp, drop the tbsps and create new tbsps as lmt then import back yes

well i havent had a problem with lmt yet so..

[sundaju]

Are LMTs more advantageous than DD based tablespaces?

If LMTs are used, are Dr.Codd's principles of RDBMS violated?

I understand that in LMT the tablespace parameters are contained in the tablespace itself.... ( ala FAT in DOS )

Any other points to be noted in this regard by me?

[pando]

Originally posted by sundaju
Are LMTs more advantageous than DD based tablespaces?

If LMTs are used, are Dr.Codd's principles of RDBMS violated?

I understand that in LMT the tablespace parameters are contained in the tablespace itself.... ( ala FAT in DOS )

Any other points to be noted in this regard by me?

LMT is faster and better than DDMT, period

There is nothing to do between tablespaces and Codd´s RULE!