How Table Data is Stored in Oracle

How Table Data is Stored in Oracle

In the previous article, we reviewed the Tablespaces and Storage Structures. In this article, we will learn about how table data is stored in Oracle with Examples. This is very important. We have to understand this as this is the main concept.

How Table Data is Stored in Oracle

The tablespace is the logical container for the segment. We have two tables Table a and table B. Table A is a segment and Table B is another segment. The segment is a table or index. So, we have two segments inside the tablespace.

How Table Data is Stored in Oracle

Let’s Take Table B. The table contains rows and columns. All the tables in the relational database contain rows and columns. So, each row in the table is stored in the form of blocks. The blocks are inside the extent. From the above image, we can see the extent consists of two blocks Block1 and Block2. The row1 in table B is stored in block 1 as a row piece. The second row is also stored in block 1 as a row piece. Generally, the block contains one row or two rows.

The last row in table B is stored in block 2. This is because the block has a size of 8kb. When the block the full then the row is automatically stored in the next block.

  • If an entire row can be inserted into a single data block, then oracle stores the row as a one-row piece.
  • If all of the rows’ data cannot be inserted into a single data block or if an update to an existing row causes the row to outgrow its data block, the oracle stores the row using multiple row pieces. We could face a situation where we have the data in one row that may be bigger than 8kb, so the row can store in multiple blocks and multiple row pieces. This situation may occur in two situations either the data is huge or the columns in the row are more than 255 columns.
  • A data block usually contains only one-row piece for each row.
  • When oracle stores a row in more than one-row piece it is changed across multiple blocks.
Data Block Content:

Data Block Content

We will focus only on the block. We are going to focus on block storage. The database block contains three main objects.

  • Block Header
  • Free space
  • Row data

Block Header: The block header is the segment type either table or index. This also contains the block address and row directory. The block header grows downwards from the top. This is just like a block address which determines the location of the block.

Row Data: This contains actual data that is stored in the table. The grows from down to up. When the row data needs any space then it grows to the top.

Free Space: The purpose of free space is to enable both row data and block header to grow and allocate space.

So, this is how the data is stored in the database. In the further articles, we will be learning how to create a tablespace.

In the next article, I am going to discuss How to Create Tablespace in Oracle with Examples. Here, in this article, I try to explain How Table Data is Stored in Oracle. I hope you enjoy this How Table Data is Stored in Oracle article.

Leave a Reply

Your email address will not be published. Required fields are marked *