Explaining Segment Management in Oracle Database

To improve the operational efficiency of a database, you need to optimize storage management. At the center of that is segment management. This article explains segment management from the basics to practical usage with diagrams so that even beginners can follow along.

1. What is segment management?

In an Oracle Database, a segment is the physical storage unit that holds data for objects such as tables and indexes.

Structure of a segment

A segment consists of the following three elements:

Segment: the overall storage area for a database object.
Extent: a set of contiguous data blocks that make up a segment.
Data block: the smallest unit of storage in the database.

Diagram: hierarchical structure of a segment

[Segment]
   ├─ [Extent 1]
   │      ├─ [Data Block 1]
   │      ├─ [Data Block 2]
   │      └─ ...
   ├─ [Extent 2]
   │      ├─ [Data Block 1]
   │      ├─ [Data Block 2]
   │      └─ ...
   └─ ...

2. Types of segment management

Oracle Database provides two approaches for managing segment space.

Automatic Segment Space Management (ASSM)

Characteristics

Automated space management.
Oracle Database–recommended setting; suitable for most scenarios.

Benefits

No need to set PCTFREE or PCTUSED.
Less contention under concurrent transactions.
Easier performance optimization and maintenance.

Configuration example

Specify SEGMENT SPACE MANAGEMENT AUTO when creating a tablespace.

CREATE TABLESPACE example_tbs
DATAFILE '/u01/app/oracle/oradata/example01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Manual Segment Space Management (MSSM)

Characteristics

Space management can be controlled manually.
Used mainly in legacy systems or when special requirements exist.

Benefits

Detailed control over PCTFREE and PCTUSED.
Effective when advanced customization is necessary.

Configuration example

Specify SEGMENT SPACE MANAGEMENT MANUAL when creating a tablespace.

CREATE TABLESPACE example_tbs_manual
DATAFILE '/u01/app/oracle/oradata/example02.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT MANUAL;

Diagram: comparing ASSM and MSSM

+---------------------------+----------------------------+
|            ASSM           |            MSSM            |
+---------------------------+----------------------------+
| Manages space automatically| Fine-grained manual setup |
| No PCTFREE/PCTUSED needed | Set PCTFREE and PCTUSED   |
| Strong with concurrency   | Advanced tuning possible   |
| Simple and recommended    | For special requirements   |
+---------------------------+----------------------------+

3. PCTFREE and PCTUSED underpinning MSSM

In manual segment space management, the two parameters PCTFREE and PCTUSED are key.

PCTFREE: reserve free space for row updates

Percentage of free space reserved in a data block for updates.
Example: PCTFREE 10 reserves 10% of the data block for future updates.

Diagram: how PCTFREE works

[Data Block]
+----------------------+
| Data (90%)           |
| +------------------+ |
| | Free space (10%) | |
| +------------------+ |
+----------------------+

PCTUSED: threshold to resume inserts

Minimum usage level at which a block becomes eligible for inserts again.
Example: PCTUSED 40 allows new inserts once usage drops below 40%.

Diagram: how PCTUSED works

[Data Block]
+----------------------+
| Used space (50%)     | → New inserts not allowed
|                      |
+----------------------+

(After deletes)
[Data Block]
+----------------------+
| Used space (30%)     | → New inserts allowed
|                      |
+----------------------+

4. Hands-on: try segment management

You can observe MSSM behavior with the following SQL script.

Create a tablespace

CREATE TABLESPACE example_tbs_manual
DATAFILE '/u01/app/oracle/oradata/example02.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT MANUAL;

Create a table

CREATE TABLE test_table (
    id NUMBER,
    data VARCHAR2(100)
)
TABLESPACE example_tbs_manual
PCTFREE 10
PCTUSED 40;

Insert data and verify

BEGIN
    FOR i IN 1..100 LOOP
        INSERT INTO test_table VALUES (i, RPAD('A', 100, 'A'));
    END LOOP;
    COMMIT;
END;

Check tablespace usage

SELECT table_name, pct_free, pct_used, tablespace_name
FROM user_tables
WHERE table_name = 'TEST_TABLE';

5. Selection criteria for real operations

Case                          Choice  Reason
Standard database operations  ASSM    Simple and efficient
High update frequency         ASSM    Automatically reduces contention
Special performance needs     MSSM    Allows fine-grained manual tuning

6. Summary

By properly understanding and applying segment management, you can achieve the following:

Efficiency with ASSM: the recommended setting covers most cases.
Fine-grained control with MSSM: advanced customization for special requirements.
Understanding PCTFREE and PCTUSED: effective tuning of data block management.

This article targets Oracle Database 19c (other versions may differ in UI or defaults).

[Reference]
Oracle Database Database Performance Tuning Guide, 19c