Reorganizing tables and tablespaces

Folks!,
We have different types of tables and currently all of them are in one tablespace(user).
Broadly the tables can be catogarized in following catagories:



avg_row_len*num_rows*1.2(from dba_tables)
1.high insert and high updates 7mb
2.high inserts and deletes/moderate updates 2mb
3.high inserts/low updates or look up tables 64k

So should I create these three catogores of tables with different inital extents ?

Do I need to have these three catogories in separate tablespaces to avoid any fragmentation?
or I can have them in one tablespace and just create the tables with diff initial extents?

Thanks!

Hi

I think if you want to put in one tablespace alter tablespace´s parameter

minimum extent 128K

this will esure all extents are 128K thus avoiding fragmentation, the deallocated extents will be reused

or

have different inital extents but multiples of smallest extent size and coalesce every once a while, once the extent issue is done just adjust pctfree and pctused parameters for respective table with high inserts/updates/deletes.

thanks pando!
currently all the tables have different storage and block utilization parameters.I wanted to to bring some uniformity (if necessary!)
so for first alternative, if I just change the min extents for tablespace is that sufficient ?

In the second alternative what initial extents are recommended if we want to group separately?
can u elaborate on 'once the extent issue is done....'?

thanks

This is just a modified question:

I have exported the full db to new instance.
the user tablespace containing all tables has min_extent as 128k. all tables now have just one extent.

But the initial and next are not same for many tables.
Initial extents are different for different catagories of table as mentioned earlier.

Should I make initial and next same and pct_inc as zero to avoid table fragmentation in future?

' tables having different initial/next ln same ts '-->is it ok?

thanks

hI

As long as you have

minimum extent 128K clause

(dont confuse with minextents parameter, they are totally different)
all extents will be multiples of 128K no matter the size of initial and next.

I said when extent issue is done, it means after you have set initial next at tablespace level. When those are done the only thing we can change are pctfree and pctused parameters to accomodate updates/inserts/deletes (this is for both cases by the way)