In Oracle style databases, what does coalescing a tablespace do and why might an administrator perform this operation?

Introduction / Context:
In older Oracle style databases that use dictionary managed tablespaces, space within the tablespace is divided into extents that can be allocated and freed as objects grow and shrink. Over time, free space can become fragmented into many small, non contiguous extents. Coalescing a tablespace is an operation that helps reduce this fragmentation by merging adjacent free extents into larger regions of free space. Although locally managed tablespaces handle this automatically, understanding coalescing remains important for maintaining legacy systems and for interview questions on storage management.

Given Data / Assumptions:

• We are working with a dictionary managed tablespace where free space is tracked in the data dictionary.
• Extents of free space can become fragmented as segments are allocated and deallocated.
• Adjacent free extents can be merged into larger free extents to simplify future allocations.
• The coalesce operation focuses on free space management and does not move allocated data.

Concept / Approach:
Coalescing a tablespace scans the free space information and looks for neighbouring free extents that share a boundary. When such adjacent extents are found, the database merges them into a single larger free extent. This reduces the number of individual free space entries and creates bigger contiguous areas of free space, which can be useful for allocating larger extents in the future. The operation does not compress data or drop indexes; it simply restructures the metadata describing free space within the tablespace.

Step-by-Step Solution:
Step 1: Recognise that fragmentation occurs when free space is broken into many small extents scattered across the tablespace.Step 2: Understand that when a segment is dropped or shrinks, its extents return to the free space list, which may or may not be adjacent to other free extents.Step 3: During coalescing, the database inspects the free space list and identifies extents that are physically adjacent.Step 4: For each pair or group of adjacent free extents, the operation merges them into a single larger free extent entry.Step 5: After coalescing, the tablespace has fewer, larger free extents, making it easier to satisfy requests for large extent allocations.

Verification / Alternative check:
Administrators can verify the effect of coalescing by querying space usage views before and after the operation. Before coalescing, free space may appear as many small extents, some of which are adjacent. After coalescing, the number of free extents decreases, and their average size increases, while the total amount of free space remains the same. This confirms that coalescing reorganises free space metadata without affecting existing data blocks or dropping objects.

Why Other Options Are Wrong:
Option B incorrectly claims that coalescing compresses all data blocks into a single file and deletes indexes, which is not the purpose of this operation and would be highly disruptive. Option C suggests converting a read only tablespace into a temporary tablespace, which requires different commands and is unrelated to coalescing. Option D says that coalescing backs up and drops the tablespace, which describes backup and drop operations, not free space consolidation. These options do not match the documented behaviour of the coalesce operation for tablespaces.

Common Pitfalls:
A common pitfall is overestimating the importance of coalescing in modern databases where locally managed tablespaces automatically handle extent management and fragmentation more efficiently. Another mistake is running coalesce operations unnecessarily during peak hours, which can consume resources without delivering real benefit. DBAs should understand that coalescing is mainly relevant for dictionary managed tablespaces and should consider migrating to locally managed tablespaces where possible. When used appropriately, coalescing can help maintain manageable free space structures in legacy environments.

Final Answer:
Correct answer: It merges adjacent free extents in a dictionary managed tablespace into larger continuous free space to reduce fragmentation