Extents Overview

An extent is a contiguous set (side-by-side) of Oracle data blocks allocated to a segment within a tablespace. The size of an extent is controlled by storage parameters used when you CREATE or ALTER the segment (INITIAL, NEXT and PCT_INCREASE) and tablespace parameters. 

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 under the table's segment.
If the data blocks of a segment's initial extent 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.

 Extent Allocation:-

SQL> SELECT tablespace_name, extent_id, blocks, bytes
  2    FROM user_extents
  3   WHERE segment_name = 'EMP';

------------------------------ ---------- ---------- -------------- ----------
USERS                                                  0                     8               65536
USERS                                                  1                     8               65536

When Extents Are Deallocated

In general, the extents of a segment do not return to the tablespace until you drop the schema object whose data is stored in the segment. Exceptions to this include the following:
  • The owner of a table or cluster, or a user with the DELETE ANY privilege, can truncate the table or cluster with a TRUNCATE...DROP STORAGE statement.
  • A database administrator (DBA) can deallocate unused extents using the following SQL syntax:  ALTER TABLE table_name DEALLOCATE UNUSED; 
  • Periodically, Oracle deallocates one or more extents of a rollback segment if it has the OPTIMAL size specified.
When extents are freed, Oracle modifies the bitmap in the datafile (for locally managed tablespaces) or updates the data dictionary (for dictionary managed tablespaces) to reflect the regained extents as available space. Any data in the blocks of freed extents becomes inaccessible.

People who read this post also read :


Post a Comment


Twitter Delicious Facebook Digg Stumbleupon Favorites More