storage advice

[dsd7038]

I am looking into storage sizes for Oracle 816+ databases for both unix and Windows environments

I thought of creating 3 tablespaces that would cater for both unix and windows settings i.e small, medium, large with the following clauses
Small
500M
size 500M
initial 128K
next 1024K
min extents 1
max extents 121

Medium
1000M
size 500M
initial 512K
next 4096K
min extents 1
max extents 121

Large
500M
size 1210M
initial 1 M
next 10 M
min extents 1
max extents 1024

Exact similar settings for Indexes

I now need some mechanism of identifying table sizes on my existing database so that I can create the relevant tables dependant upon size in into either a small, medium,or Large tablespace. Again I need to specify the same for Indexes ie catergorise into small, medium, large.
Does any one have any useful script or way I can do this or recommend any better method. End result is that I need to specify storage clauses for all tables and indexes so rather than do this per table or index I opted to create different tablespace sizes and create each object dependant upon size and growth into the relevant tablespace.

Please help

[davey23uk]

use LMT's with uniform extents, e.g 512K 1M 10M

and you will be fine for all situation

[slimdave]

The usual extent sizes advised are 64kb, 2Mb, and 64Mb, plus dedicated TS's for very large segments.

And forget the max extents stuff. set to unlimited.

[rotem_fo]

Hi,
There is a nice papaer, which oracle published, about tablespace and extent sizes called:
HOW TO STOP DEFRAGMENTING AND START LIVING: THE DEFINITIVE
WORD ON FRAGMENTATION

You can read it at:
http://technet.oracle.com/deploy/ava...pdf/defrag.pdf

Cheers.