Overview of Extents

Extents is made up of a number of contiguous data blocks. One or more extents make a segment. When in a segment there is no space i.e segment is full, then to allow more space oracle allocates a new extent in that segment.

When Extents Are Allocated
When you create a table or index then oracle automatically creates an initial extent with a number of specified data blocks (with how many data blocks will be discussed later on) under the table’s/ index’s data segment.

In this case though no rows are inserted into the table or index but yet extents are allocated and reserve for the table’s or index’s rows.

Now, if the data blocks of a segment’s initial extent become full and more space is required to hold new data, then oracle automatically allocates an incremental extent for that segment.

In this way, subsequent increment extents are allocated whenever data blocks of a segment’s extent full.

When Extents Are Deallocated
With the Segment Advisor oracle automatically advice on which schema objects or on which tablespaces has space available for reclamation based on the level of space fragmentation within the object.

To access Segment Advisor from enterprise manager home go to Related Links> Select Advisor Central>Select Segment Advisor.

You can reclaim space manually of LB table by

alter table “LB” enable row movement;
alter table “LB” shrink space;

In general the extents of a segment do not return to the tablespace until you drop the schema objects using DROP TABLE or DROP CLUSTER statement.

The exception is following,
•TRUNCATE…DROP STORAGE statement.
•ALTER TABLE table_name DEALLOCATE UNUSED;
•Periodically, Oracle deallocates one or more extents of a rollback segment if it has the OPTIMAL size specified.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s