One big and important part of the Oracle Database is storage. Data stored in the database, is stored in some kind of structure. Eventually data is stored physically, but a lot of things happen logically before that happens. What happens in the in between? This article focuses on on the basics of storage in the oracle database.

We will cover these main terms when discussing storage in Oracle:

• Tablespace
• Segment
• Extent
• Block

Lets get started

This is what happens from top to botten when working with data. In this article I will assume a big file tablespce scenario. In this case there is a one on one relationship between a tablespace and a data file. Lets work through these terms:

A tablespace holds object in it. Objects can be all things but lets use tables as an example. A tablespace holds table objects in it. Each object is a segement. So you will get table segements (there are exceptions where objects have objects in it). The tablespace stores these table segments.

To grant space to this segment, you assign extents to it. Extents are made out of sets of blocks. These blocks are where data is actually stored. Inside these blocks, rows of data are stored. Each row is given a row number when they’re stored in the block. This row number is also stored in the header of the block (pointer). This way you know where a row starts and ends. A block starts filling up until it reaches its maximum block size. The block will be locked and you wont be able to insert new rows into the block.

Thats it, the basics of storage in Oracle. Lets get into a little more detail.

When rows get bigger and do not fit in the block anymore, they will be placed in another block. The row remains, but a pointer is placed here to the new location of the moved row. Its like migrating a row. Well it is, its call migration.

Another related situation happens when your rows get bigger than the block they’re stored in. In this case the block is filled up with as much of the row as possible and a pointer lets you know where to find the rest of the row. The rest of the row will be stored in another block. So you will get a chain of rows. That’s why its called row chaining.

If you have to access two blocks to get one row, that would be slower than accessing one block for a row. In the case of one row, you will maybe not notice it. But if you access milions of rows that are all chained or migrated, performance will be impacted.

What happens when I request data

When you request data, you get the rowid from the given header. The first part of the rowid tells you which tablespace and data file(s) you need to access for the row. Then you need to lookup which segement in the tablespace you need to access and which blocks and which row numbers. The combination of all this information is the logical address of the row you are accessing. This is always unique. This address is used to access row you need.

You don’t actually read this data from the data files. In Oracle, disk storage is considered slow and reading from memory is fast. So rows you need are placed in the SGA and you read them from there. (read here about the baiscs memory structures, coming soon).

Conclusion

That was it, the basics of storage in the oracle database. Get familiar with these terms and get into your database and try to figure out what happens. Track some data all the way to their block and verify that it actually works the way I wrote it down.