Presentation on Presentation on tablespaces tablespaces

segments extends segments extends and blocksand blocks

Presentation on Presentation on tablespaces tablespaces

segments extends segments extends and blocksand blocks

CONTENT Introduction. what are blocks? what are extends? what are segments? what are tablespaces?

INTRODUCTION Oracle allocates logical database Oracle allocates logical database

space for all data in a database.space for all data in a database. The units of database space The units of database space

allocation are data blocks, extents, allocation are data blocks, extents, and segments.  and segments. 

Oracle Oracle stores data logically in stores data logically in tablespaces and physically in tablespaces and physically in datafiles associated with the datafiles associated with the corresponding tablespace. corresponding tablespace.

BLOCKSBLOCKS Data BlocksData Blocks is smallest logical unit to  is smallest logical unit to

store Oracle Data. store Oracle Data. One data block represent specific One data block represent specific

number of bytes on physical hard disk. number of bytes on physical hard disk. Data Block size is usually multiple of Data Block size is usually multiple of

operating system block size operating system block size   You can have multiple block sizes with You can have multiple block sizes with

in single database (max. five).in single database (max. five).    Block Size is specified by initialization Block Size is specified by initialization

parameter DB_BLOCK_SIZE parameter DB_BLOCK_SIZE Data Block in Oracle is also called as Data Block in Oracle is also called as

logical block logical block

Format of data block Format of data block isis

  HeaderHeader : contains generic information like block : contains generic information like block address and type of segment (index, data.)address and type of segment (index, data.)

Table Directory Table Directory : contains information about : contains information about table having rows in that blocktable having rows in that block

Row Directory Row Directory : contains information about : contains information about actual row contained in that blockactual row contained in that block

Free Space Free Space : available space in data block for : available space in data block for additional row or update of row which require additional row or update of row which require more space.more space.

Row Data Row Data : contains table or index data.First : contains table or index data.First three component of data block (Header, Table & three component of data block (Header, Table & Row directory) collectively known as OverheadRow directory) collectively known as Overhead

EXTENDSEXTENDSExtent is collection of contiguous data

blocks.

One or more extents make up a segment.

When the existing space in a segment is completely used, Oracle allocates a new extent for the segment.

When Extents Are Allocated

When you create a table, Oracle allocates to the When you create a table, Oracle allocates to the table's data segment an initial extent of a table's data segment an initial extent of a specified number of data blocks. Although no specified number of data blocks. Although no rows have been inserted yet, the Oracle data rows have been inserted yet, the Oracle data blocks that correspond to the initial extent are blocks that correspond to the initial extent are reserved for that table's rows.reserved for that table's rows.

If the data blocks of a segment's initial extent If the data blocks of a segment's initial extent become full and more space is required to hold become full and more space is required to hold new data, Oracle automatically allocates new data, Oracle automatically allocates an incremental extent for that segment. An an incremental extent for that segment. An incremental extent is a subsequent extent of the incremental extent is a subsequent extent of the same or greater size than the previously same or greater size than the previously allocated extent in that segment.allocated extent in that segment.

For maintenance purposes, the header block of For maintenance purposes, the header block of each segment contains a directory of the extents each segment contains a directory of the extents in that segment.in that segment.

SEGMENTSSEGMENTSSegmentSegment is set of extents allocated  is set of extents allocated

for specific data structure (like for specific data structure (like table or index).table or index).

Various kind of segments are Various kind of segments are table, index, cluster, rollback, table, index, cluster, rollback, temporary …temporary …

Important views for segments Important views for segments are dba_segments, user_segments,are dba_segments, user_segments,all_segmentsall_segments

In a Segment, first block of first In a Segment, first block of first extent contains segment extent contains segment header information header information

Things to note w.r.t. Segment, Extent &

Datablocks

Segment and its associated extents are stored in one table space.

Extents of a segment may not be contiguous on disk

Segment can span multiple datafiles of a particular tablespace (Information on tablespace & datafiles coming soon) but extent can contain data from only one datafile.

The Relationships Among The Relationships Among Segments, Extents, and Data Segments, Extents, and Data

BlocksBlocks•

TABLESPACESTABLESPACESTablespaces are the bridge between Tablespaces are the bridge between

certain physical and logical certain physical and logical components of the Oracle database. components of the Oracle database. Tablespaces are where you store Tablespaces are where you store Oracle database objects such as Oracle database objects such as tables, indexes and rollback tables, indexes and rollback segments. segments. 

You can think of a tablespace like a You can think of a tablespace like a shared disk drive in Windows. You shared disk drive in Windows. You can store files on your shared drive, can store files on your shared drive, move files around and remove files. move files around and remove files. The same is true with tablespaces.  The same is true with tablespaces. 

PRESENTED BY :PRESENTED BY :

• VINAY UGAVE (SDBCT,INDORE)VINAY UGAVE (SDBCT,INDORE)

SUBMITTED TO:SUBMITTED TO: MISS GARIMA JOSHIMISS GARIMA JOSHI