Tuesday 29 November 2016

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:
Thanks,

4 comments:

  1. Hello There. I discovered your weblog the usage of msn. That is a
    very well written article. I will be sure to bookmark it
    and come back to read extra of your helpful info. Thank
    you for the post. I will definitely comeback.

    ReplyDelete
    Replies
    1. Dear User,

      Thank you for your feedback...
      Please check this below, hope this may also useful:
      Oracle error -29548: ORA-29548 Solution (FNDCPASS Error)

      Regards,
      ora-data Team

      Delete
  2. Ꮐooɗ post. I learn ѕomethіng neww and challenging on sites I stumbleupon on a daily baѕiѕ.
    It will alwaуs be ᥙuseful to read through content from other authors and usse a little something from their websites.

    ReplyDelete
  3. alpways i used to read smaller content that also clear theirr motive, and that is also happening
    with this paragraph which I am reading at this time.

    ReplyDelete

Thank you for your comments and suggestions. It's good to talk you.