Making Segment Management Work for You

Oracle Database manages segment space in two waysautomatic and manual, in a locally managed tablespace. Automatic segment-space management (ASSM), which is the default for all new permanent locally managed tablespaces, is much more efficient than manual segment-space management. The DBA can enable automatic segment-space management in a database using the SEGMENT SPACE MANAGEMENT AUTO clause, and can disable it with the SEGMENT SPACE MANAGEMENT MANUAL clause. The initialization parameter COMPATIBLE should be set to >=10.0 for ASSM to work. For Oracle Database 10g Release 1, you will have to enable ASSM; for Release 2, it is automatically enabled by default.

Using automatic segment-space management for tables and indexes eliminates the need to specify PCTFREE during object creation. Oracle supports setting a value for PCTFREE, but if you specify PCTUSED, it will be ignored.

The segment-space management specified by the DBA at the time of tablespace creation applies to all new segments created in the tablespace. The segment spacemanagement mode of a tablespace cannot be changed, unlike with the database. A locally managed tablespace utilizing automatic management can be created as a single file or a BigFile tablespace.

Automatic segment-space management is far superior to the manual process because it is scalable as the number of users or instances increases. This is true for a RAC environment as well.