Extent allocation and space management in Oracle

Extent allocation and space management in Oracle

1. Have you ever need to extent allocation for a
table?

In this topic, | describe this ability and review
when a DBA need it:

DATABASE
BOX

Extents

An extent is a logical unit of database storage
space allocation made up of a number of
contiguous data blocks. One or more extents in turn
make up a segment. When the existing space in a
segment is completely used, Oracle allocates a new
extent for the segment.

When Extents Are Allocated

When you create a table, Oracle allocates to the
table's data segment an initial extent of a specified
number of data blocks.

Although no rows have been inserted yet, the Oracle
data blocks that correspond to the initial extent are
reserved for that table's rows.

If the data blocks of a segment's initial extent

2. become full and more space is required to hold new
data, Oracle automatically allocates

an incremental extent for that segment.

An incremental extent is a subsequent extent of the
same or greater size than the previously allocated
extent in that segment.

(The next section explains the factors controlling
the size of incremental extents.)

For maintenance purposes, the header block of
each segment contains a directory of the extents in
that segment.

Rollback segments always have at least two
extents.

Note:

In serial operations, in which one server process
parses and executes a SQL statement.

But extents are allocated somewhat differently in
parallel SQL statements, which entail multiple server
processes.

Determining the Number and Size of Extents
Storage parameters expressed in terms of extents

3. define every segment. Storage parameters apply to
all types of segments. They control how Oracle
allocates free database space for a given segment.

For example, you can determine how much space is
initially reserved for a table's data segment or you
can limit the number of extents the table can
allocate by specifying the storage parameters of a
table in the STORAGE clause of the CREATE TABLE
statement.

How Extents Are Allocated
Oracle controls the allocation of incremental
extents for a given segment as follows:

e Oracle searches through the free space (in the
tablespace that contains the segment) for the
first free, contiguous set of data blocks of an
incremental extent's size or larger, using the
following algorithm:

© Oracle searches for a contiguous set of data
blocks that matches the size of new extent plus
one block to reduce internal fragmentation.
(The size is rounded up to the size of the
minimal extent for that tablespace, if
necessary.) For example, if a new extent

4. requires 19 data blocks, Oracle searches for
exactly 20 contiguous data blocks. If the new
extent is 5 or fewer blocks, Oracle does not add
an extra block to the request.

e If an exact match is not found, Oracle then
searches for a set of contiguous data blocks
greater than the amount needed. If Oracle finds
a group of contiguous blocks that is at least 5
blocks greater than the size of the extent
needed, it splits the group of blocks into
separate extents, one of which is the size it
needs. If Oracle finds a group of blocks that is
larger than the size it needs, but less than 5
blocks larger, it allocates all the contiguous
blocks to the new extent.

In the current example, if Oracle does not find a set
of exactly 20 contiguous data blocks, Oracle
searches for a set of contiguous data blocks greater
than 20. If the first set it finds contains 25 or more
blocks, it breaks the blocks up and allocates 20 of
them to the new extent and leaves the remaining 5
or more blocks as free space. Otherwise, it allocates
all of the blocks (between 21 and 24) to the new
extent.

5. If Oracle does not find an equal or larger set of
contiguous data blocks, it coalesces any free,
adjacent data blocks in the corresponding
tablespace to form larger sets of contiguous
data blocks. (The SMON background process
also periodically coalesces adjacent free
space.)

If an extent cannot be allocated after the
second search, Oracle tries to resize the files by
autoextension. If Oracle cannot resize the files,
it returns an error.

Once Oracle finds and allocates the necessary
free space in the tablespace, it allocates a
portion of the free space that corresponds to
the size of the incremental extent. If Oracle
found a larger amount of free space than was
required for the extent, Oracle leaves the
remainder as free space (no smaller than 5
contiguous blocks).

Oracle updates the segment header and data
dictionary to show that a new extent has been
allocated and that the allocated space is no
longer free.

6. DATABASE
Box

The blocks of a newly allocated extent, although
they were free, may not be empty of old data.
Usually, Oracle formats the blocks of a newly
allocated extent when it starts using the extent, but
only as needed (starting with the blocks on the
segment free list).

In a few cases, however, such as when a database
administrator forces allocation of an incremental
extent with the ALLOCATE EXTENT option of an
ALTER TABLE or ALTER CLUSTER statement, Oracle
formats the extent's blocks when it allocates the
extent.

When Extents Are Deallocated
In general, the extents of a segment do not return to
the tablespace until you drop the object whose data
is stored in the segment (using a DROP TABLE or
DROP CLUSTER statement). Exceptions to this
include the following:

+ The owner of a table or cluster, or a user with

the DELETE ANY privilege, can truncate the

7. table or cluster with a TRUNCATE...DROP
STORAGE statement.

+ Periodically, Oracle may deallocate one or more
extents of a rollback segment if it has the
OPTIMAL size specified.

e A database administrator (DBA) can deallocate
unused extents using the following SQL
syntax:ALTER TABLE table_name DEALLOCATE
UNUSED;

When extents are freed, Oracle updates the data
dictionary to reflect the regained extents as
available space. Any data in the blocks of freed
extents becomes inaccessible, and Oracle clears
the data when the blocks are subsequently reused
for other extents.

If a tablespace is created with local extent
management and the extent size is 64K, then Oracle
allocates 64K or 8 blocks assuming 8K block size.
Oracle doesn't round it up to the multiple of 5 when
a tablespace is locally managed.

8. Manual allocation of extents:

Oracle will automatically allocate extents to
segments when needed. To manually force extent
allocation:

SQL> ALTER TABLE emp ALLOCATE EXTENT;

alter table Table_Name allocate extent [size KIM]
datafile ‘filename’ instance N;

The following statement allocates an extent of 5
kilobytes for the EMP table and makes it available to
instance 4 of RAC:

ALTER TABLE emp ALLOCATE EXTENT (SIZE 5K
INSTANCE 4);

Because this command omits the DATAFILE
parameter, Oracle allocates the extent in one of the
datafiles belonging to the tablespace containing the
table.

To apply changes on specific datafile set mentioned
datafile as:

SQL> alter table ts allocate extent (datafile ‘/data/
oradata/pop/test_01.dbf’)

9. What ALTER TABLE ... ALLOCATE EXTENT Does??

The statement:

ALTER TABLE test ALLOCATE EXTENT;

Forces Oracle to allocate a new extent immediately
for the segment (table test), even if existing extents
still have free space.

You can also specify details:

ALTER TABLE test ALLOCATE EXTENT (SIZE 1M
DATAFILE '/u01/oradata/ts01.dbf');

e SIZE: defines extent size (optional; if omitted,
Oracle uses tablespace default).

e DATAFILE: specifies in which datafile the extent
should be allocated.

+ Instance : define this change apply into which
instance of RAC.

10. Why a DBA might use ALLOCATE EXTENT?

It's not common in day-to-day operations, but there
are specific advanced or legacy use cases:
a. Pre-allocating space
+ To reserve disk space in advance for a large
table (e.g., before a big data load).
e Prevents fragmentation or auto-extent
allocation during a heavy insert.
b. Performance tuning (legacy)
+ Before ASSM and LMT, DBAs sometimes
allocated extents manually to:
+ Control where data goes (specific datafiles).
e Reduce contention for space management.
e Avoid frequent extent allocations.
c. In DMT (Dictionary Managed Tablespaces)
+ Manual extent allocation was common because
extent management was dictionary-based, and
DBAs needed to control growth carefully.

In LMT + ASSM, is ALLOCATE EXTENT useful?
In Locally Managed Tablespaces (LMT)

e Extents are tracked via bitmaps, and space
allocation is automatic.

11. e Oracle decides when and where to allocate
extents.

¢ You can still use ALLOCATE EXTENT, but it's
rarely necessary — Oracle will handle it
efficiently.

In Automatic Segment Space Management (ASSM)
e ASSM deals with block-level space (free space
within extents), not extent allocation.
+ So ALLOCATE EXTENT doesn't affect ASSM's
behavior — it just forces another extent to be
allocated.

Conclusion:
On ASSM + LMT tablespaces, ALLOCATE EXTENT is
technically valid, but functionally redundant. Oracle

manages extents automatically and more efficiently
than manual allocation.

12. When it might still make sense (edge cases)

+ Preallocating for very large objects (LOBs,
partitions)

Example:

ALTER TABLE orders PARTITION p2025 ALLOCATE
EXTENT (SIZE 16);

> Useful when you know a specific partition will get
massive inserts and you want to reserve contiguous
space early.

© Datafile-level control If you want extents in a
specific datafile:
ALTER TABLE sales ALLOCATE EXTENT (DATAFILE '/
u01/oradata/sales02.dbf');

> Helps when manually balancing tablespace
usage across datafiles.

+ Temporary workaround for space errors
Occasionally, DBAs use it to force extent
allocation after resolving a space issue, without
triggering automatic allocation errors

13. (ORA-1653: unable to extend table).
Practical summary:

DMT (Dictionary Managed): Often used in Manual
format and extent control important

LMT (Manual Segment Space Management) Use
sometimes and Possible for preallocation

LMT + ASSM (default in modern Oracle):Use

Rarely and Oracle manages extents automatically.
Partitioned / LOB Tables:Sometimes used, For
preallocation of large partitions or LOB segments

Recommended Practice:

You should not need to use ALLOCATE EXTENT
manually unless you are:
e Preallocating space for a specific operational
reason,
+ Managing special storage configurations,
+ Or performing precise physical space tuning on
very large, high-performance systems.