farrokhp, Saying that it'll cause fragmentation ALWAYS, was probably a little quick fire on my behalf. If you NEVER have to recreate objects then you may never have fragmentation. (Very unlikely though) Additionally, having PCTINCREASE > 0 makes administration more difficult, ie. segment sizing because, really you don't know what the object NEXT extent size will be without some serious cacluations on your behalf. If you have large tables, it won't be long before your table is looking for a 2GB NEXT extents when the table stated out at 1 MG and had a 50% increase.

I would make at segments PCTINCREASE = 0 . I haven't come across a situation where I've need to have PCTINCREASE > 0 yet. Although, your situation might be different.

To change this, you'd have to change all the segments that have PCTINCREASE > 0. Just because your TABLESPACE has PCTINCREASE > 0 doesn't mean any segments have been created with this. (Tablespace params only get used when a segment is created in the tablespace WITHOUT the specific storage parms set).

If you said

CREATE TABLE TEST (a number);

Then it would use the TABLESPACE defaults to create the segment.

as opposed to

CREATE TABLE TEST (a number) STORAGE (INITIAL 1m NEXT 1m PCTINCREASE 0);

Which WON'T use tablespace defaults cause you've specifed them yourself.

mrvajrala - The advantages of using LMT far outweigh any adantage of using DMT's.

Cheers