Installation Checklists

When you create an Oracle Database 10g instance, make it a point to create a default permanent tablespace along with it at the database level. Oracle will assign any nonsystem users and objects to this tablespace. Whenever the DBA creates a new user or object, it will use this default permanent tablespace unless the default tablespace is mentioned for the user/object. (Similarly the default temporary tablespace created along with the database will be used as the temporary tablespace for users without an assigned temporary tablespace.) The init.ora parameter compatible>=10.0 should be set to utilize this feature.

This default permanent tablespace as well as default temporary tablespaces can only be locally managed. If the SYSTEM tablespace is created as locally managed, no tablespaces within the database can be dictionary managed, including the default tablespace. Once a default tablespace has been assigned at the database level, it cannot be dropped, but its name can be changed.

Why Should You Prefer Locally Managed Tablespaces?

A locally managed tablespace manages its own extents by maintaining a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks in the data file. Whenever the extents are allocated or released for reuse, Oracle changes the bitmap values to reflect the new status for the blocks. These changes do not update tables in the data dictionary like the default method of dictionary-managed tablespaces. These changes do not generate rollback and reduce the contention on datadictionary tables and fragmentation.