Is there ever an advantage to setting pctincrease to 0 on a tablespace? Does it indeed prevent auto-coalesce on a tablespace? Thoughts on the subject?
Thanks in Advance,
I'm building the aforementioned tablespaces in a warehouse environment.
I believe it does stop SMON from autocoalescing. Set it to 1 instead.
the advantage of set pctincrease to 0 is that the tablespace will not fragmented. This means if you delete some rows and syou will insert new rows. This coun't take this space where the older (deleted) rows were.
So the advantage is that the tablespace will not fragmented and so the db must not search space were new rows will insert. So all new rows will insert into spaces of the tablespace where old rows are deleted.
The changes in pctincrease will not take effect until you shutdown and startup again.
That is, going from 0 to 1 without a reboot won't result in an automatic coalesce.
OCP DBA 8i, 9i, 10g
When u set pctincrease to 0 at tablespace level Oracle(SMON) will not coalesce the tablespace automatically.
Oracle recommends a setting of 0 as a way to minimize fragmentation and avoid the possiblity of very large temporary segments during processing.
In case of any help please be free to ask me at email@example.com
Rohit nirkhe,Oracle DBA,OCP 8i
Click Here to Expand Forum to Full Width