Blocks , Extents, Segments in Oracle Database : Back to Basics

Data Blocks Extents Segments in Database

On popular demand from our newbie DBA readers, I am starting Oracle Database Back to Basics series where I am going to cover basic concepts and Architecture of Oracle Database (9i, 10g, 11g)

Today’s post is dedicated to logical storage component of Oracle Database i.e. Data Blocks, Extents and Segment

A. Data Blocks is smallest logical unit to store Oracle Data.
ii) One data block represent specific number of bytes on physical hard disk.
iii) Data Block in Oracle is also called as logical block
iv) Data Block size is usually multiple of operating system block size
v) You can have multiple block sizes with in single database (max. five)
vi) Block Size is specified by initialization parameter DB_BLOCK_SIZE
vii) Format of Data Block is

data blocks in oracle database
a) Header : contains generic information like block address and type of segment (index, data..)
b) Table Directory : contains information about table having rows in that block
c) Row Directory : contains information about actual row contained in that block
d) Free Space : available space in data block for additional row or update of row which require more space.
e) Row Data : contains table or index data.First three component of data block (Header, Table & Row directory) collectively known as Overhead

B. Extent is collection of contiguous data blocks.
ii) One or more extents make up a segment.

C. Segment is set of extents allocated for specific data structure (like table or index).
ii) Various kind of segments are table, index, cluster, rollback, temporary …
iii) Important views for segments are dba_segments, user_segments, all_segments
iv) In a Segment, first block of first extent contains segment header information

Things to note w.r.t. Segment, Extent & Datablocks
i) Segment and its associated extents are stored in one table space.
ii) Extents of a segment may not be contiguous on disk
iii) Segment can span multiple datafiles of a particular tablespace (Information on tablespace & datafiles coming soon) but extent can contain data from only one datafile.

About the Author Atul Kumar

Oracle ACE, Author, Speaker and Founder of K21 Technologies & K21 Academy : Specialising in Design, Implement, and Trainings.

follow me on:

Leave a Comment:

11 comments
prashanth says January 31, 2008

Nice you have started on DB side. I am daily user of onlineappsdba.com

Regards
Prashanth

Reply
Reddy says April 18, 2008

Hi,

Can any body tell , how to rectify data block.

Thanks,
Reddy

Reply
Amit says April 18, 2008

Hi Reddy,

You can follow these steps

1.) Restore a backup from before this corruption occurs and recover.

STEPS TO BE DONE
—————-

shutdown the database
restore the datafile
startup mount
recover database
auto
alter database open
analyze table sys.source$ validate structure;

2.) If 1 is not an option (but 1 is the best solution) then we can try this
option.

a. select obj#, rowid from sys.source$

— When this stops/fails the last line should give you the obj# of the object
affected by the corruption.

b. select OWNER#, NAME, TYPE#, STATUS from sys.obj$ where OBJ# =

— This should give you the name of the package/procedure hit be the
corruption.

c. exec DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(‘SYS’,’source$’);

— This will skip the corrupt block, and might allow you to re-create the
object hit by the corruption.

IF you are not allowed to re-create the object – then after DBMS_REPAIR.
SKIP_CORRUPT_BLOCKS you should be able to perform an full Export of the
database minus the
procedure/package hit by the corruption.

d. Use the Export to re-create the database and afterwards manually re-create
the object that was skipped by the Export.

BUT, this is only a last resort if 1 (restore/recover) really isn’t an option,
and it is with no guarantee for success.
The only way to guarantee that you will not run into further problems with this
block is to recreate your database or go to a backup where the problem does not
exist.

Hope this helps

Amit

Reply
ziad says July 20, 2009

hi,
what is the difference between Segment and Page in DB?
thx.

Reply
ziad says July 20, 2009

hi,
what is the difference between Segment and Page in DB?
thx.

Reply
murali says August 25, 2010

what is the difference between segment and page in DB?

Reply
frankh2 says August 24, 2011

No such thing as ALL_SEGMENTS view,

Reply
mahamad says April 16, 2012

when address space of the process is devided into different-differnr size of blocks,then every block is known as segement.

when address space of the process is devided into equal size of blocks,then every block is known as page.

Reply
Uday says August 1, 2012

Hi,
What is the use of segments, why tablespace itself cannot directly deal with extents. Instead we have segment as intermediate for tablespace and extents.

Reply
SDASD says August 5, 2015

You have just copied from ORACLE DOCS.Nothing new.Plagiarism at its best

Reply
    Atul Kumar says August 5, 2015

    Thanks @SDASD for your comment, We’ll try to add content with our own opinion/comments behind that in future .

    Reply
Add Your Reply

Not found