Thursday, March 14, 2013

Extent, Block & Segment in Oracle

Oracle store data in data blocks also called as logical blocks, oracle blocks .One data block corresponds to specific number of bytes on disk.
Extents: it is a specific number of continuous block allocated for storing special type of information.A segment is set of extents,each of which is allocated for specific data structure and stored in same table space.

Example :
table data is stored in its own data segment.
index's data is stored in its own index segment.

Oracle allocate space to segment in terms of one extent.when existing extents got full, Oracle allocate another extents for the segment.
Because extents are allocated  as needed, so extents of segment may or may not be  contiguous.

How to see all the segment type present in oracle :

SQL> select segment_type ,count(*) from dba_segments group  by segment_type;

SEGMENT_TYPE     COUNT(*)
------------------ ----------
LOBINDEX 1006
INDEX PARTITION  295
TABLE SUBPARTITION   32
TABLE PARTITION  270
NESTED TABLE   40
ROLLBACK    1
LOB PARTITION    1
LOBSEGMENT 1006
INDEX 2183
TABLE 1534
CLUSTER   10
TYPE2 UNDO   20

All segments and its extents stored in one table space  In table space,segment can include extents from more than file means segment can span over multiple data files. But extents contain data from one data files.

To see data files , refer to table DBA_DATA_FILES describes database files.

To see segment and extents info , refer to table DBA_SEGMENTS & DBA_EXTENTS.





1 comment: