Scribd
Upload
71 views

Extents in Oracle

An extent is one or more contiguous database blocks allocated for a segment. A segment consists of one or more extents and is a container for database objects like tables and indexes. There are different types of segments including data segments for tables and clusters, index segments, rollback segments, and temporary segments used for operations like sorting. Segments are made up of blocks that contain data as well as some overhead for metadata, and free blocks can be used to store new data.

Uploaded by

Mayur N Malviya
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
71 views

Extents in Oracle

An extent is one or more contiguous database blocks allocated for a segment. A segment consists of one or more extents and is a container for database objects like tables and indexes. There are different types of segments including data segments for tables and clusters, index segments, rollback segments, and temporary segments used for operations like sorting. Segments are made up of blocks that contain data as well as some overhead for metadata, and free blocks can be used to store new data.

Uploaded by

Mayur N Malviya
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

Extents in Oracle

An extent is a part of a segment. It consists of one or more contiguos db blocks.


Space for data on a harddisk is allocated in extents. An extent belongs to a tablespace.
One or more extents make a segment.
sys.fet$ and sys.uet$ maintaint free and used extents.

Thanks
Thanks to Rahul Agarwal who improved this page.
Thanks also to Eliza who notified me of a type on this page.
A segment is a container for objects (such as tables, indexes....). A segment consists of extents.

Segment types
There are 11 types of segments in Oracle 10g:

 table
 table partition
 index
 index partition
 cluster
 rollback
 deferred rollback
 temporary
 cache
 lobsegment
 lobindex
These types can be grouped into four segment classes:

 data segments: table and cluster


 index
 rollback
 temporary data
In order to find out the size of the respective segments, use dba_segments (or user_segments, or all_segments).
A segment can either be created so that it can have an unlimited or limited number of extents.
Oracle keeps track of free blocks for each data segment.

Segment Headers
The segment header is stored in the first block of the first extent. It contains:

 The extents table


 The free lists descriptors
 The high water mark

Temporary segments
The following SQL statements require a temporary segment if they cannot be completed in memory:

 create index .
 select order by
 select distinct
 select group by
 select union
 select intersect
 select minus by
 analyze table
 unindexed joins
 certain correlated subqueries
Actually, most of these statements require a sort.
Temporary segments can be stored in any tablespace. However, it makes more sense to store temporary segments in a temporary tablespace.
SMON frees temporary segments.

Database
Blocks
Free used and unused blocks

Free blocks
Free blocks are blocks below the high water mark whose filling is below the pctused attribute.

If an insert statement is executed, Oracle tries to insert the row in a free block. If it doesn't find a free block, it tries to insert it
in an unused block.

Free blocks of a segment are recorded in the segment's free list.

See also dbms_space.free_blocks

pctfree controls the threshold when a free block becomes a non-free block.

Unused blocks
A block that has never been written to is a unused block. Unused blocks are beyond the high water mark.

The number of unused blocks can be found with dbms_space.unused_space.

Used blocks
A block that has been written to is a used blocks. Used blocks are below the high water mark.

A free block is also a used block; but a used block is not always a free block.

Getting the count of used and free blocks


With free blocks and unused blocks it is possible to get the count of used and free blocks, respectively, within a segment.

Size of db blocks
Since Oracle 10g, it's possible to define multiple block sizes for a database. However, one of the sizes is the standard size. The
size of the (standard) database block is determined by the db_block_size init parameter. This standard block size is the block
size of blocks in the system tablepsace.

Interested transaction list (ITL)


See initrans and maxtrans.

Each ITL uses 24 bytes overhead.

Block header
All blocks have a header that consists of:

 Block type
1=undo segment header, 2=undo segment block, 5=data segment header, 7=temporary table, 11=data file
header, 14=Unlimited rollback segment header, 15=Unlimited deferred rollback segment header, 16=Unlimited
data segment header, 17=Unlimited data segment header with FL groups, 18=Extent map block
 Block format
Is it an oracle 7 or oracle 8, (or 9, or 10???) formatted block?
 Relative database address
 SCN
 SCN sequence number
 Check value
Is used for integrity checking at the block level if db_block_checksum is set.
 Tail
The tail is not at the beginning of a block but at its end: it is used as a block consistency check. That is, the value
must be equal to SCN + block type + SCN sequence number.

RBA
The redo log address (RBA) plays an important role. . To be finished.

Checking integrity of blocks


The integrity of database blocks can be checked with dbverify [dbv] (the database verify tool) or dbms_dbverify.

Overhead
Not all bytes in a datablock actually store inserted (or selectable) data. Some bytes are also used for Oracle to organize the
data so that it can be found again. These (additional) data are called overhead.

The following applies for tables:

The overhead consists of

 fixed header
 transaction header
 table directory
 row directory
 row header
 column size
fixed header + transaction header + table directory + row directory = block header.

Fixed header
The size of the block header is 57 bytes.

Transaction header
The size of the transaction header is dependant on the initrans parameter.

Specifically, its size is at least 23*initrans bytes. It can grow up to 23*maxtrans bytes.

Table directory
The table directory is used to find the start position of each row (or row directory).

The size of the table directory is 4 bytes*number of tables. Number of tables is important for cluster tables. For other
tables, it's 1 (eg 4 bytes).

Row directory
The row directory uses 2 bytes per stored row.

Row header
For non cluster tables, the row header is 3 bytes. Each stored row has one row header. One byte is used to store flags, one
byte to indicate if the row is locked (for example because it's updated but not commited), and one byte for the column count.

Column size
Each column within a row needs at least 1 byte indicating the size of the data in the column. For varchar2's longer than 250
bytes, 3 bytes are used.

PCTFREE
When calculating the amount of storable data in a block, the pctfree must also be considered.

Corrupt blocks
Blocks might become corrupt. Information about corrupt blocks is recorded in the control files.

Dirty blocks
to be finished...

Checking block data integrity in memory


Oracle allows to check the integrity of data within a database block before it is written from memory (that is: buffer cache) to
the harddisk by setting the parameter db_block_checksum to full.

If a data integrity error is detected, an ORA-00227, ORA-00368 or ORA-01578 is raised.

Database block address


Each block in the database is uniquely identified with the database block address (dba).

x$bh
With x$bh, it is possible to query some information about db_blocks in the buffer cache.

You might also like