Database Technologies‎ > ‎Oracle‎ > ‎

Data Blocks, Extents, Segments

Block :  Its the smallest unit of storage.  For a database the block size is fixed,
however in 10g we could have multiple block sizes, default bloc size is 8K, the setting is
configured in initialization file i.e init.ora
Use the following sqlplus query to get the block size of the database
show parameter block_size
Extents : Grouping of specific number of contiguous data blocks.
Segments : Collection of extents, segment are of specific object types
table segment, index segment, cluster segment etc
Now practically when you create a new object a new segment is going to be
created for it with specified number of extents (default 1), now when the extents
get full oracle creates a new extent for the segment which may not be in contiguous
with the earlier extent
Lets check the dba_segments view
Following are some of the important columns in the view
 Owner  Username of the segment owner
 Segment_name  Name of the segment
 Header_file   Datafile ID
 Header_block  ID of the block containig the header
 Bytes   Size in bytes of the segment
 Extents    Number of extents allocated in the segment
 initial_extent   Size in bytes of the initial extent
 next_extent  Size in bytes of the next extent
 min_extents  Minimum number of extents allowed in segment
 max_extents  Maximum number of extents allowed in segment
 pcs_increase  Size of the next extent to be allocated in percent
 Lets check the dba_extents view, important ones listed below
 Owner  Username of the segment to which the extent belogs
 Segment_name  Name of the segment of which the extent is part
 segment_type  Type of segment whether , data,temp,undo,index etc
 extent_id  Extent ID of the extent in segment
 file_id  Datafile ID to which this extent belongs
 block_id  starting block number of the extent
 bytes  size of extent in bytes    
 blocks  Size of extent in Oracle blocks