Oracle Data Blocks, Extents and Segments Concepts

Hi DBA-Mates,
Oracle data block

As we know that Oracle allocates logical database space for all data in a database.

Actually, data blocks are also called as logical or oracle blocks or pages. And oracle stores the data in data blocks to a specific number of bytes of physical database space on disk.

In short for understand:

Oracle data blocks are Logical storage space where Oracle Database stores data in data blocks at smallest unit.

We can set the size from DB_BLOCK_SIZE initialization parameter.

Very Important:

Oracle data block-->Extents-->Segments-->Tablespace are LOGICAL STORAGE.

Data files and OS blocks are PHYSICAL STORAGE.

Oracle data block contains below important information:

Common and Variable Header

Table Directory

Row Directory

Free Space

Row Data

Extents:

In easy languages:

Extents are the logical unit of database which is made of contiguous multiple numbers of the oracle data blocks.

For example: oracle data block1+data block2+data block3 = 1 Extent etc.

As we all knowing that an extent is a logical unit of oracle database and space allocation is made up of a number of contiguous data blocks.

Segment:
And as database concepts, one or more extents are called a segment. Whenever the existing space in a segment is completely used or full, oracle allocates a new extent for the segment.

There is Storage parameters which expressed in terms of extents and define every segment.

And one more thing as we know that a Tablespace that manages the extents locally which can be either uniform extent size or variable extent sizes that are determined automatically by the system.

Above is just summery, if required, we will talk about extents later on because these all are very basic concepts about oracle database or we can say it is starting of the oracle database. Here the need is about the sql query for extents, maximum extents and size of all the extents.

We have some sql query which will be very useful getting the details of the extents.

To get the current no. of extents, maximum extents, sizes of all the extents and the sizes of them by inputting a segment name not owned by SYS User:

SQL>col owner for a7
SQL>col SEGMENT_TYPE for a7
SQL>col TABLE_NAME for a25
SQL>col NEXT(KB) for 9,999,990

SQL>SELECT s.owner, s.segment_type, t.table_name,
t.max_extents, t.next_extent/1024 "NEXT(KB)", s.extents
FROM dba_tables t, dba_segments s
WHERE t.table_name=s.segment_name
and t.owner not like 'SYS%'
and s.extents > 100
union all
SELECT s.owner, s.segment_type, i.index_name,
i.max_extents, i.next_extent/1024 "NEXT(KB)", s.extents
FROM dba_indexes i, dba_segments s
WHERE i.index_name=s.segment_name
and i.owner not like 'SYS%'
and s.extents > 100
/

And also a DBA can deallocate the unused extents using the below sql query:

SQL>Alter table table_name deallocate unused;

Hope these details and sql scripts may useful and helpful. For any concerns or suggestion please write to us either in comment box or Contact Us @ https://ora-data.blogspot.in/

Some Useful links:

Important Scripts

Oracle R12.1 Vs R12.2

Career tips

R12.2 Concepts and Architecture

Jobs Tips

Thanks,