pctincrease for system tablespace

[pthomas]

Should the tablespace SYSTEM be of 50% or O% pctincrease and why?

Thanks in advance.

[marist89]

Since we don't have much control over the objects in the SYSTEM tablespace, I like to keep at PCTINCREASE 50.

[kiruba]

System tablespace should be more than 0 because we will dropping and creating so many objects. There will be lot of space defragmentation. So if pctincrease percentage greater than 1 system will coalese it automatically.



I hope i have clarified

[gsprince]

Another reason could be, most of the system tables are created with a small INITIAL size. PCTINCREASE of 50% will help to keep the number of extents less.

[pando]

well by default it´s 50 anyway and there is not much point changing it afterwards since the segments are already created with that pct increase value, even you change at tablespace level the segments will still have pct increase 50

[rpardip]

Perhaps, you could use the new feature LOCALLY MANAGED TABLESPACE for SYSTEM tablespace as well if you are using Oracle 8.1.5 or greater ?

[pando]

I do not think this is supported

[rpardip]

This is right out of Oracle Documentation:


For the SYSTEM tablespace, you can specify EXTENT MANGEMENT LOCAL in the CREATE DATABASE command. If the SYSTEM tablespace is locally managed, other tablespaces in the database can be dictionary-managed but you must create all rollback segments in locally-managed tablespaces.