I know that the DBA handbook advises that a zero PCTINCREASE on tablespaces. Our System, RBS and Data tablespaces have a PCTINCREASE of 50 - are these the defaults. Should I be concerned? Should/Can I change them?
I believe our consultant set them up that way but I'm not sure why.
PCTINCREASE doesn't really have anything to do with tablespaces extending.
PCTINCREASE deals with the segments inside the tablespace. For example, if you have a table that has a next extent of 10K and PCTINCREASE is set to 50, the next-next extent would be 15K. The next-next-next extent would be 22.5K, etc.
If your PCTINCREASE is set to 0, all extents would be 10K, thus uniform extent sizes.
I like to set the default PCTINCREASE at 1 for the tablespace and then all the objects have a pctincrease of 0. This way, I can tell when my developers sneak a table in without sizing it correctly and don't have to worry about the table extending out really quickly.
A non zero value will ensure that tablespace coalesce happens as and when smon wakes up.But the problem with 0 is that you have to manully coalesce the tablespace.
for temp and rbs keep pctincrease as zero .for all others make it 1.