Understanding about Oracle fragmentation and Solutions

The Oracle database consists of three tablespaces: tablespace, systemspace, and tempspace.
A tablespace contains segments, and segments contain multiple ranges (extent) and
Freespace. when data is stored, segments first initialize the storage location range for storage
Store and search, and mark the range of storage locations as 1.

Because when storing data, segments always find the maximum range for themselves, which facilitates storage and searching,
After frequent data storage, there is a redundant range in the narrow area between the range and the range,
When you look for the maximum range for yourself, you need to find the maximum range for yourself.
You can imagine how long it takes to traverse the largest idle range,
Therefore, after frequent data updates, there will be a lot of redundant space fragments in the space.

The following methods can be used to address the storage principles and features:

1. Set the range flag to 1, because SMON periodically merges the positions of the same flag
This reduces redundant fragments and improves data addressing efficiency and storage efficiency.
Change to 1 automatic merge: Alter tablespace users default storage (pctincrease 1)
Manual merge: Alter tablespace users coalesce

2. A simple way to compress data into a range is to use the correct storage parameters to recreate the segment, and then
Import data from the old table to the new table and delete the old table. Use export/import to achieve this goal.
First exp DPS/DPS file = mesdb. dmp compress = y grants = y indexes = y
And then import it. The exported table is the latest table and data after reconstruction, and the range is also the largest.