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.
Originally posted by mrvajrala This question is to Julian
My database is running on 8.1.7 version, And i did see all i\of the tablespaces are DMT and not LMT,, so what all teh adavanrages i am going to get by converting the DMT to LMT..
would take some time for me,
I suggest that after using 8.1.7, you convert to LMTs. There are several advantages of LMTs to DMTs, make a search in this forum with keywords like LMT, advantage, etc. You will find a lot of information!
when pctincrease is equal to zero oracle will not do a coalescing when fragmentation occurs. we have to coalesce explicitly by ourselves. but if the pctincrease is greater than zero oracle(SMON) will take care of coalescing automatically.
but managing the extent size becomes a real problem.so it is better to have the pctincreae equal to zero or a value closer to zero(say 1% or 2%).