Extent

From Oracle FAQ
Jump to: navigation, search

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.

[edit] Monitor

Query to look at the extents allocated to a table:

SQL> SELECT tablespace_name, extent_id, blocks, bytes
  2    FROM user_extents
  3   WHERE segment_name = 'EMP'
  4  /
TABLESPACE_NAME                 EXTENT_ID     BLOCKS      BYTES
------------------------------ ---------- ---------- ----------
USERS                                   0          8      65536
USERS                                   1          8      65536

Extents can be mapped to data files within a tablespace (think disk fragmentation chart). To extract the data for such a chart:

SQL> SELECT owner, segment_name, tablespace_name, file_id,
  2         block_id AS "FROM BLOCK", block_id+blocks AS "TO BLOCK"
  3    FROM dba_extents
  4   WHERE segment_name = 'EMP' AND owner = 'SCOTT'
  5  /
OWNER     SEGMENT TABLESPACE  FILE_ID FROM BLOCK   TO BLOCK
--------- ------- ---------- -------- ---------- ----------
SCOTT     EMP     USERS             4         17         25

[edit] Manual allocation of extents

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

SQL> ALTER TABLE emp ALLOCATE EXTENT;
Table altered.

[edit] Extend sizing

Oracle 8 and above versions round off extents to a multiple of 5 blocks when more than 5 blocks are requested. If one requests 16K or 2 blocks (assuming a 8K block size), Oracle doesn't round it up to 5 blocks, but it allocates 2 blocks or 16K as requested. If one asks for 8 blocks, Oracle will round it up to 10 blocks.

Space allocation also depends upon the size of contiguous free space available. If one asks for 8 blocks and Oracle finds a contiguous free space that is exactly 8 blocks, it would give it you. If it is 9 blocks, Oracle would also give it to you. So, clearly Oracle doesn't always round extents to a multiple of 5 blocks.

The exception to this rule is locally managed tablespaces. 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.

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #